| |
| |
| Acknowledgments | |
| |
| |
| |
| 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 | |
| |
| |
| Deployment Features | |
| |
| |
| Legacy Support Features | |
| |
| |
| Where Is DTS in SQL Server 2005? | |
| |
| |
| Integration Services in SQL Server 2005 Editions | |
| |
| |
| 32-bit Editions Versus 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 2005 Integration Services | |
| |
| |
| Installing Integration Services from the Command Prompt | |
| |
| |
| Upgrading SQL Server 2000 Data Transformation 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 | |
| |
| |
| |
| SQL Server Import and Export Wizard | |
| |
| |
| Starting SSIS Import and Export Wizard | |
| |
| |
| Hands-On: Importing a Flat File into SQL Server 2005 | |
| |
| |
| Hands-On: Exporting an SQL Server Table to an Excel File | |
| |
| |
| Using Business Intelligence Development Studio | |
| |
| |
| Hands-On: Exploring an SQL Server Import and Export Wizard Package Using BIDS | |
| |
| |
| Hands-On: Importing Data from a Microsoft Access Table | |
| |
| |
| Summary | |
| |
| |
| |
| 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 | |
| |
| |
| Microsoft .NET Data Provider for mySAP Business Suite | |
| |
| |
| 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 Mobile Connection Manager | |
| |
| |
| WMI Connection Manager | |
| |
| |
| 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 | |
| |
| |
| Integration Services Expressions | |
| |
| |
| Hands-On: Using Expressions to Update Properties at Run-time | |
| |
| |
| Summary | |
| |
| |
| |
| Integration Services Control Flow Containers | |
| |
| |
| Integration Services Package | |
| |
| |
| Foreach Loop Container | |
| |
| |
| Hands-On: Contacting Opportunities | |
| |
| |
| For Loop Container | |
| |
| |
| Hands-On: Removing Duplicate E-mail Addresses | |
| |
| |
| Sequence Container | |
| |
| |
| Task Host Container | |
| |
| |
| Summary | |
| |
| |
| |
| 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 | |
| |
| |
| Custom Tasks | |
| |
| |
| Control Flow Tasks in Detail | |
| |
| |
| FTP Task | |
| |
| |
| Hands-On: Downloading Zipped Files | |
| |
| |
| Execute Process Task | |
| |
| |
| Hands-On: Expanding Downloaded Files | |
| |
| |
| Execute DTS 2000 Package Task | |
| |
| |
| 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 | |
| |
| |
| ActiveX Script Task | |
| |
| |
| Script Task | |
| |
| |
| Transfer Database Task | |
| |
| |
| Transfer Error Messages Task | |
| |
| |
| Transfer Jobs Task | |
| |
| |
| Transfer Logins Task | |
| |
| |
| Transfer SQL Server Objects Task | |
| |
| |
| Transfer Master Stored Procedures 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 | |
| |
| |
| |
| Administrating Integration Services | |
| |
| |
| Introduction | |
| |
| |
| Hands-On: Enabling the SSIS Service | |
| |
| |
| Working with Integration Services Folders and Packages | |
| |
| |
| Hands-On: Managing Storage of Integration Services Package | |
| |
| |
| Dtutil Utility | |
| |
| |
| Hands-On: Using dtutil | |
| |
| |
| Running Packages | |
| |
| |
| BIDS | |
| |
| |
| SQL Server Import and Export Wizard | |
| |
| |
| Package Execution Utility | |
| |
| |
| Hands-On: Running an SSIS Package Using the Package Execution Utility | |
| |
| |
| DTExec Utility | |
| |
| |
| SQL Server Agent Service | |
| |
| |
| Hands-On: Automating Running an SSIS Package with SQL Server Agent Service | |
| |
| |
| Summary | |
| |
| |
| |
| 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: Understanding Package Protection Levels | |
| |
| |
| Using Integration Services Fixed Database Level Roles | |
| |
| |
| Fixed Database Level Roles and Their Permissions | |
| |
| |
| Hands-On: Control Access to a Package Using Database-level Roles | |
| |
| |
| Protecting Packages and Metadata at the Storage Level | |
| |
| |
| Considerations for Saving to SQL Server | |
| |
| |
| Considerations for Saving to the File System | |
| |
| |
| Summary | |
| |
| |
| |
| Advanced Features of Integration Services | |
| |
| |
| Logging and Log Providers in SSIS | |
| |
| |
| Hands-On: Configure Logging in a Package | |
| |
| |
| Transactions in Integration Services Packages | |
| |
| |
| 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 a SSIS Package | |
| |
| |
| Summary | |
| |
| |
| |
| Data Flow Components | |
| |
| |
| Separating Data Flow from Control Flow | |
| |
| |
| Data Flow Component Interfaces | |
| |
| |
| Inputs and Outputs | |
| |
| |
| Considerations when Bringing Data into Data Flow | |
| |
| |
| Data Flow Sources | |
| |
| |
| DataReader 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 | |
| |
| |
| Other Transformations | |
| |
| |
| Data Flow Destinations | |
| |
| |
| 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 Mobile Destination | |
| |
| |
| SQL Server Destination | |
| |
| |
| Data Flow Paths | |
| |
| |
| Hands-On: An Introduction to Data Flow Task | |
| |
| |
| Summary | |
| |
| |
| |
| Data Flow Transformations | |
| |
| |
| Row Transformations | |
| |
| |
| Copy Column Transformation | |
| |
| |
| Character Map Transformation | |
| |
| |
| Data Conversion Transformation | |
| |
| |
| Derived 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 | |
| |
| |
| 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 | |
| |
| |
| Other Transformations | |
| |
| |
| Export Column Transformation | |
| |
| |
| Import Column Transformation | |
| |
| |
| Audit Transformation | |
| |
| |
| Row Count Transformation | |
| |
| |
| Slowly Changing Dimension Transformation | |
| |
| |
| Hands-On: Loading a Slowly Changing Dimension | |
| |
| |
| Business Intelligence Transformations | |
| |
| |
| Data Mining Query Transformation | |
| |
| |
| Term Lookup Transformation | |
| |
| |
| Term Extraction Transformation | |
| |
| |
| Fuzzy Grouping Transformation | |
| |
| |
| Fuzzy Lookup Transformation | |
| |
| |
| Hands-On: Removing Duplicates from Owners Data | |
| |
| |
| Summary | |
| |
| |
| |
| 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 | |
| |
| |
| Summary | |
| |
| |
| |
| Migrating to Integration Services | |
| |
| |
| Upgrade Advisor | |
| |
| |
| Hands-On: Analyzing DTS 2000 Packages Before Upgrading to SSIS | |
| |
| |
| Migration Options | |
| |
| |
| Running DTS 2000 Packages 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 | |
| |
| |
| Summary | |
| |
| |
| |
| 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 | |
| |
| |
| Discuss and Optimize | |
| |
| |
| Test, Measure, and Record | |
| |
| |
| Performance Monitoring Tools | |
| |
| |
| Performance Counters | |
| |
| |
| SQL Server Profiler | |
| |
| |
| Logging | |
| |
| |
| Execution Trees | |
| |
| |
| Hands-On: Monitoring Execution Trees in a Pipeline | |
| |
| |
| Using Parallel Processing | |
| |
| |
| Running Parallel Tasks in the Control Flow | |
| |
| |
| Creating Multiple Data Flows | |
| |
| |
| Enhancing EngineThreads | |
| |
| |
| Balancing Synchronicity | |
| |
| |
| Summary | |
| |
| |
| |
| How to Use the Provided Software | |
| |
| |
| Index | |