Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition

Name: Hands-On Microsoft SQL Server 2008 Integration Services, Second Edition
Brand: McGraw-Hill Education
Price: 96.89 EUR
Availability: OnlineOnly

Ashwani Nanda(Autor*in)

McGraw-Hill Education (Verlag)

1. Auflage

Erschienen am 2. September 2010

758 Seiten

E-Book

ePUB mit Adobe-DRM

Systemvoraussetzungen

978-0-07-173641-1 (ISBN)

96,89 €inkl. 7% MwSt.

Systemvoraussetzungen

für ePUB mit Adobe-DRM

E-Book Einzellizenz

Als Download verfügbar

Beschreibung

Weitere Details

Weitere Ausgaben

Inhalt

Cover Page
Hands-On Microsoft® SQL Server® 2008 Integration Services
Copyright Page
Contents
Acknowledgments
Introduction
Chapter 1 Introducing SQL Server Integration Services
Integration Services: Features and Uses
Integration Services Architecture
Integration Services Designer and Management Tools
Data Warehousing Loading
Standardizing and Enhancing Data Quality Features
Converting Data into Meaningful Information
Data Consolidation
Package Security Features
Service-Oriented Architecture
SSIS Package as a Data Source
Programmability
Scripting
Easy Management of SSIS Packages
Automating Administrative Tasks
Easy Deployment Features
Legacy Support Features
What's New in Integration Services 2008
Better Lookup
Improved ADO NET Components
Powerful Scripting
Extended Import and Export Wizard
Ability to Profile Your Data
Optimized Thread Allocation
SSIS Package Upgrade Wizard
Taking Advantage of Change Data Capture
Benefiting from T-SQL Merge Statement
Enhanced Debugging
Inclusion of New Date and Time Data Types
Where Is DTS in SQL Server 2008?
Integration Services in SQL Server 2008 Editions
32-Bit Editions vs. 64-Bit Editions
Integration Services Architecture
Integration Services Service
Integration Services Object Model
Integration Services Run Time
Integration Services Data Flow
Installing Integration Services
Installing Integration Services on a Clean System
Hands-On: Installing SQL Server 2008 Integration Services
Installing Integration Services from the Command Prompt
Installing Side by Side
Upgrading to SQL Server 2008 Integration Services
Business Intelligence Development Studio
Hands-On: Creating a Blank Integration Services Project
SQL Server Management Studio
Hands-On: Connecting to Integration Services Service
Summary
Chapter 2 Getting Started with Wizards
Starting SQL Server Import and Export Wizard
Hands-On: Importing a Flat File into SQL Server 2008
Using Business Intelligence Development Studio
Hands-On: Exploring an SQL Server Import and Export Wizard Package Using BIDS
Integration Services Connections Project Wizard
Analyzing Data Quality with the Data Profiling Task
Single-Column Profiles
Multiple-Column Profiles
Hands-On: Using Data Profiling Task
Summary
Chapter 3 Nuts and Bolts of the SSIS Workflow
Integration Services Objects
Solutions and Projects
File Formats
Fixed Width
Delimited
Ragged Right
Connection Managers
ADO Connection Manager
ADO.NET Connection Manager
Cache Connection Manager
Excel Connection Manager
File Connection Manager
Flat File Connection Manager
FTP Connection Manager
HTTP Connection Manager
MSMQ Connection Manager
Analysis Services Connection Manager
Multiple Files Connection Manager
Multiple Flat Files Connection Manager
ODBC Connection Manager
OLE DB Connection Manager
SMO Connection Manager
SMTP Connection Manager
SQL Server Compact Edition Connection Manager
WMI Connection Manager
Microsoft Connector 1.0 for SAP BI
Microsoft Connector for Oracle by Attunity
Microsoft Connector for Teradata by Attunity
Data Sources and Data Source Views
Data Sources
Data Source View
SSIS Variables
System Variables
Hands-On: Using System Variables to Create Custom Logs
User-Defined Variables
Hands-On: Creating a Directory with User-Defined Variables
Precedence Constraints
Constraint Options
Multiple Constraints
Integration Services Expressions
Hands-On: Using Expressions to Update Properties at Run Time
Summary
Chapter 4 Integration Services Control Flow Containers
Integration Services Package
Foreach Loop Container
Hands-On: Contacting Opportunities
For Loop Container
Hands-On: Deleting Data Month by Month After Archiving
Sequence Container
Task Host Container
Summary
Chapter 5 Integration Services Control Flow Tasks
Categories of Control Flow Tasks
Data Flow Task
Data Preparation Tasks
Workflow Tasks
SQL Server Tasks
Scripting Tasks
Analysis Services Tasks
Transfer Tasks
Maintenance Tasks
Backward Compatibility Tasks
Custom Tasks
Control Flow Tasks in Detail
FTP Task
Preparations for the Hands-On Exercises in This Chapter
Hands-On: Downloading Zipped Files
Execute Process Task
Hands-On: Expanding Downloaded Files
File System Task
Hands-On: Archiving Downloaded Files
Web Service Task
XML Task
Input Section
Second Operand Section
Output Section
Operation Options Section
Execute SQL Task
General Page
Parameter Mapping Page
Result Set Page
Expressions Page
Bulk Insert Task
Message Queue Task
Hands-On: Importing Expanded Files
Execute Package Task
Hands-On: Consolidating Workflow Packages
Send Mail Task
WMI Data Reader Task
Hands-On: Reading the Application Log
WMI Event Watcher Task
Transfer Database Task
Transfer Error Messages Task
Transfer Jobs Task
Transfer Logins Task
Transfer Master Stored Procedures Task
Transfer SQL Server Objects Task
Back Up Database Task
Check Database Integrity Task
Execute SQL Server Agent Job Task
Execute T-SQL Statement Task
History Cleanup Task
Maintenance Cleanup Task
Notify Operator Task
Rebuild Index Task
Reorganize Index Task
Shrink Database Task
Update Statistics Task
Summary
Chapter 6 Administering Integration Services
Connecting to Integration Services Service
Managing Packages with Default Settings
Managing Packages Saved on a Remote Server
Managing Packages on an Instance of an SQL Server
Connecting to Integration Services on a Remote Server
Managing SSIS Packages
Hands-On: Working with Integration Services Storage Folders
dtutil Utility
Hands-On: Using dtutil
Running SSIS Packages
SQL Server Import and Export Wizard
BIDS
Execute Package Utility (DTExecUI)
Hands-On: Running an SSIS Package Using the Execute Package Utility
DTExec Utility
SQL Server Agent
Hands-On: Automating Running an SSIS Package with SQL Server Agent
Executing SSIS Packages Programmatically
Summary
Chapter 7 Securing Integration Services Packages
Digitally Signing the Package
Excluding Sensitive Information from the Package
Encrypting Sensitive Information in the Package
Encrypting All the Information in the Package
Hands-On: Working with Package Protection Levels
Using Integration Services Fixed Database-Level Roles
Fixed Database-Level Roles and Their Permissions
Hands-On: Control Access to a Package with User-Defined Roles
Considerations for Different Storage Areas
Considerations for Saving to SQL Server
Considerations for Saving to the File System
Summary
Chapter 8 Advanced Features of Integration Services
Logging and Log Providers in SSIS
Hands-On: Configuring Logging in a Package
Transactions in Integration Services Packages
Hands-On: Maintaining Data Integrity with Transactions
Restarting Packages with Checkpoints
Hands-On: Restarting a Failed Package Using Checkpoints
Expressions and Variables
Hands-On: Extending the Contacting Opportunities Package with Property Expressions
Handling Events at Package Run Time
Hands-On: Creating Event Handlers in an SSIS Package
As a Data Source for Reporting Services Report
Enable SSIS as a Data Source
Using SSIS as a Data Source
Summary
Chapter 9 Data Flow Components
From Control Flow to Data Flow
Data Flow Component Interfaces
External Metadata
Inputs
Outputs
Error Outputs
Considerations for Bringing Data into Data Flow
Data Flow Sources
ADO NET Source
Excel Source
Flat File Source
OLE DB Source
Raw File Source
Script Component Source
XML Source
Data Flow Transformations
Business Intelligence Transformations
Row Transformations
Rowset Transformations
Split and Join Transformations
Auditing Transformations
Data Flow Destinations
ADO NET Destination
Data Mining Model Training Destination
DataReader Destination
Dimension Processing Destination
Excel Destination
Flat File Destination
OLE DB Destination
Partition Processing Destination
Raw File Destination
Recordset Destination
Script Component Destination
SQL Server Compact Destination
SQL Server Destination
Data Flow Paths
Hands-On: An Introduction to the Data Flow Task
Summary
Chapter 10 Data Flow Transformations
Row Transformations
Copy Column Transformation
Character Map Transformation
Data Conversion Transformation
Derived Column Transformation
Export Column Transformation
Import Column Transformation
Script Component
OLE DB Command Transformation
Split and Join Transformations
Conditional Split Transformation
Multicast Transformation
Union All Transformation
Merge Transformation
Merge Join Transformation
Cache Transform
Lookup Transformation
Hands-On: Updating PersonContact Data
Rowset Transformations
Sort Transformation
Percentage Sampling Transformation
Row Sampling Transformation
Pivot Transformation
Hands-On: Pivoting Sales Order Records in an Excel Worksheet
Unpivot Transformation
Aggregate Transformation
Hands-On: Aggregating SalesOrders
Audit Transformations
Audit Transformation
Row Count Transformation
Business Intelligence Transformations
Slowly Changing Dimension Transformation
Hands-On: Loading a Slowly Changing Dimension
Data Mining Query Transformation
Term Lookup Transformation
Term Extraction Transformation
Fuzzy Grouping Transformation
Fuzzy Lookup Transformation
Hands-On: Removing Duplicates from Owners Data
Summary
Chapter 11 Programming Integration Services
The Two Engines of Integration Services
Programming Options
Scripting
Developing Custom Objects from Scratch
Building Packages Programmatically
Extending Packages with Scripting
The Legacy Scripting Task: ActiveX Script Task
Script Task
Hands-On: Scripting the Handshake Functionality
Script Component
Script Task vs. Script Component
Hands-On: Extending Data Flow with the Script Component
Script Component as a Data Source
Script Component as a Transformation
Script Component as a Destination
Debugging Techniques for Script Component
Summary
Chapter 12 Data Warehousing and SQL Server 2008 Enhancements
The Need for a Data Warehouse
Data Warehouse Design Approaches
Top-Down Design Approach
Bottom-Up Design Approach
Data Warehouse Architecture
Centralized EDW with Dependent Data Marts
Distributed Independent Data Marts
Data Warehouse Data Models
Entity-Relationship Model
Dimensional Model
Dimension Types
Loading a Dimension Using a Slowly Changing Dimension
Data Model Schema
Star Schema
Snowflake Model
Building a Star Schema
SQL Server 2008 R2 Features and Enhancements
SQL Server 2008 R2 Data Warehouse Editions
SQL Server 2008 R2 Datacenter
SQL Server 2008 R2 Parallel Data Warehouse
SQL Server 2008 R2 Data Warehouse Solutions
Fast Track Data Warehouse
Parallel Data Warehouse
SQL Server 2008 R2 Data Warehouse Enhancements
Backup Compression
MERGE Statement
GROUP BY Extensions
Star Join Query Processing Enhancement
Change Data Capture
Partitioned Table Parallelism
Summary
Chapter 13 Deploying Integration Services Packages
Package Configurations
Types of Package Configurations
Hands-On: Applying Configurations to Contacting Opportunities
Direct and Indirect Configurations
Hands-On: Using Indirect Configurations
Deployment Utility
Deploying Integration Services Projects
Hands-On: Deploying an Integration Services Project
Custom Deployment
Summary
Chapter 14 Migrating to Integration Services 2008
Upgrade Advisor
Hands-On: Analyzing DTS 2000 Packages with SQL Server 2008 Upgrade Advisor
Migrating Data Transformation Services Packages
Migration Options
Installing DTS 2000 Support Components
Running DTS 2000 Packages As-Is with Run-Time Support
Hands-On: Executing a DTS 2000 Package
Embedding DTS 2000 Packages in Integration Services Packages
Execute DTS 2000 Package Task
Hands-On: Executing Importing Contacts Using the Execute DTS 2000 Package Task
Migrating DTS 2000 Packages to Integration Services
Package Migration Wizard
Hands-On: Migrating Importing Contacts to Integration Services
Upgrading Integration Services 2005
Same-Server Installation
Different Server Installation
Upgrading SSIS 2005 Packages
Summary
Chapter 15 Troubleshooting and Performance Enhancements
Troubleshooting Integration Services Packages
Debugging Features Available by Default
Debugging Tools Requiring Configuration
Hands-On: Setting Breakpoints to See Variables Values
Performance Enhancements
It's All About Memory
Architecture of the Data Flow
Synchronous and Asynchronous Transformations
Classifying Data Flow Transformations
Optimization Techniques
Performance Monitoring Tools
Performance Counters
SQL Server Profiler
Logging
Execution Trees
Hands-On: Monitoring Log Events in a Pipeline
Using Parallel Processing
Running Parallel Tasks in the Control Flow
Creating Multiple Data Flows
Enhancing EngineThreads
Summary
Appendix How to Use the Provided Software
Downloaded Software
Attaching Campaign Database
Index

Systemvoraussetzungen

Als PDF speichern Als Link merken