x Hands-On Microsoft SQL Server 2008 Integration Services
Chapter 4 Integration Services Control Flow Containers . . . . . . . . . . . . . . . . . . . . 109
Integration Services Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Foreach Loop Container . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Hands-On: Contacting Opportunities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
For Loop Container . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Hands-On: Deleting Data Month by Month After Archiving . . . . . . . . . . . . . . . . . . 124
Sequence Container . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Task Host Container . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Chapter 5 Integration Services Control Flow Tasks . . . . . . . . . . . . . . . . . . . . . . . 135
Categories of Control Flow Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Data Flow Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Data Preparation Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Workow Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
SQL Server Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
Scripting Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Analysis Services Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Transfer Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Maintenance Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Backward Compatibility Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Custom Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Control Flow Tasks in Detail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
FTP Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
Preparations for the Hands-On Exercises in This Chapter . . . . . . . . . . . . . . . . . . . 143
Hands-On: Downloading Zipped Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Execute Process Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Hands-On: Expanding Downloaded Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
File System Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Hands-On: Archiving Downloaded Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Web Service Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160

History Cleanup Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Maintenance Cleanup Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Notify Operator Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Rebuild Index Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Reorganize Index Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Shrink Database Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Update Statistics Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224
Chapter 6 Administering Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Connecting to Integration Services Service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Managing Packages with Default Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Managing Packages Saved on a Remote Server . . . . . . . . . . . . . . . . . . . . . . . . . 227
Managing Packages on an Instance of an SQL Server . . . . . . . . . . . . . . . . . . . . . . 228
Connecting to Integration Services on a Remote Server . . . . . . . . . . . . . . . . . . . . 228
xii Hands-On Microsoft SQL Server 2008 Integration Services
Managing SSIS Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Hands-On: Working with Integration Services Storage Folders . . . . . . . . . . . . . . . . 229
dtutil Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Hands-On: Using dtutil . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Running SSIS Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
SQL Server Import and Export Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
BIDS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 244
Execute Package Utility (DTExecUI) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Hands-On: Running an SSIS Package Using the Execute Package Utility . . . . . . . . . . . 246
DTExec Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
SQL Server Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
Hands-On: Automating Running an SSIS Package with SQL Server Agent . . . . . . . . . . 257
Executing SSIS Packages Programmatically . . . . . . . . . . . . . . . . . . . . . . . . . . . 266
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267
Chapter 7 Securing Integration Services Packages . . . . . . . . . . . . . . . . . . . . . . . 269

From Control Flow to Data Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334
Data Flow Component Interfaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
External Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Inputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Outputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
Error Outputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
Considerations for Bringing Data into Data Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
Data Flow Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
ADO NET Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
Excel Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
Flat File Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
OLE DB Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Raw File Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Script Component Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
XML Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Data Flow Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351
Business Intelligence Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Row Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Rowset Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354
Split and Join Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 354
Auditing Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
Data Flow Destinations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355
ADO NET Destination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
Data Mining Model Training Destination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
DataReader Destination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
xiv Hands-On Microsoft SQL Server 2008 Integration Services
Dimension Processing Destination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 358
Excel Destination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Flat File Destination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
OLE DB Destination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361

Percentage Sampling Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
Row Sampling Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
Contents xv
Pivot Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Hands-On: Pivoting Sales Order Records in an Excel Worksheet . . . . . . . . . . . . . . . 420
Unpivot Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
Aggregate Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
Hands-On: Aggregating SalesOrders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
Audit Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Audit Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Row Count Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
Business Intelligence Transformations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Slowly Changing Dimension Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Hands-On: Loading a Slowly Changing Dimension . . . . . . . . . . . . . . . . . . . . . . . 443
Data Mining Query Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Term Lookup Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456
Term Extraction Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457
Fuzzy Grouping Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460
Fuzzy Lookup Transformation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463
Hands-On: Removing Duplicates from Owners Data . . . . . . . . . . . . . . . . . . . . . . 468
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 480
Chapter 11 Programming Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . 481
The Two Engines of Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 482
Programming Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 484
Developing Custom Objects from Scratch . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
Building Packages Programmatically . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486
Extending Packages with Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486
The Legacy Scripting Task: ActiveX Script Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486
Script Task . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488

SQL Server 2008 R2 Parallel Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . 552
SQL Server 2008 R2 Data Warehouse Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552
Fast Track Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553
Parallel Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554
SQL Server 2008 R2 Data Warehouse Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . 557
Backup Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557
MERGE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 559
GROUP BY Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
Star Join Query Processing Enhancement . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562
Change Data Capture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 562
Partitioned Table Parallelism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 564
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 566
Chapter 13 Deploying Integration Services Packages . . . . . . . . . . . . . . . . . . . . . . 567
Package Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568
Types of Package Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569
Hands-On: Applying Configurations to Contacting Opportunities . . . . . . . . . . . . . . 572
Contents xvii
Direct and Indirect Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577
Hands-On: Using Indirect Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578
Deployment Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587
Deploying Integration Services Projects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589
Hands-On: Deploying an Integration Services Project . . . . . . . . . . . . . . . . . . . . . 589
Custom Deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594
Chapter 14 Migrating to Integration Services 2008 . . . . . . . . . . . . . . . . . . . . . . . 595
Upgrade Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596
Hands-On: Analyzing DTS 2000 Packages with SQL Server 2008 Upgrade Advisor . . . . . 597
Migrating Data Transformation Services Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600
Migration Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600
Installing DTS 2000 Support Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601

Hands-On: Monitoring Log Events in a Pipeline . . . . . . . . . . . . . . . . . . . . . . . . . 663
Using Parallel Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668
Running Parallel Tasks in the Control Flow . . . . . . . . . . . . . . . . . . . . . . . . . . . 669
Creating Multiple Data Flows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670
Enhancing EngineThreads . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 670
Appendix How to Use the Provided Software . . . . . . . . . . . . . . . . . . . . . . . . . . 673
Downloaded Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 674
Attaching Campaign Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 677
xix
Acknowledgments
I
would like to thank my wife Sarita for being supportive and doing the major work
of maintaining the household while I was busy with the book revision work. My
kids Toozy, Ritzy, and Himnish also deserve acknowledgments here for being
co-operative and patient with me. Special thanks go to the readers of the first edition—
especially the ones who have taken time to give their valuable feedback. The feedback
coming in through various channels has actually inspired me to revise the first edition
to this new edition in line with the release of SQL Server 2008 R2. In the process of
writing and editing the book, technical editor Allan Mitchell has contributed his ideas
and constructive comments that not only have helped to remove some of the mistakes
in the compilation but also have greatly improved the content. I would like to thank
all my colleagues at Avis-Europe, where I learn most while working with them and for
giving their valuable inputs as well. Finally, special thanks to the team at McGraw-Hill
for their hard work and support during this project.