Pro SQL Server 2005 Integration Services (eBook)

James Wightman is an average guy. A programmer, problem solver, artist, and artisan, he loves his work and his family, and he's looking for that one chance to change the world. Born in 1975, James spent his formative years reading Marvel comics and books by Tolkien before discovering computers in 1980. Teaching himself to program computers at the age of 6, he wrote himself a teaching aid to learn pure mathematics and then translated the program into Z80 machine language. From there, he learned C and then became adept with 68000 assembly language and eventually C++, writing two games one released commercially for the Commodore Amiga. James is an innovator in many ways and in many areas. At age 11, he invented a way for aircraft to land with increased safety, and at age 12, he invented a VCR that could record four simultaneous broadcast streams onto a standard VHS tape. That you don't own a VCR that records four channels at once and that aircraft still make a squealing sound when their wheels touch tarmac indicate how well the designs of a preteen are received by companies such as Sony and Boeing. His next invention will rock the world to its very foundations. Or at least cause a ripple. Maybe. So he hopes. Having worked on the largest of Microsoft technology led projects for some of the biggest companies worldwide for the past 15 years, James is honored to have worked on his second book, Pro SQL Server 2005 Integration Services, and hopes that writing will remain a part of his ongoing commitment to programming and the programming community. Currently, his time is taken up learning the Mandarin, Japanese, and Russian languages as well as spending time on the MSDN forums trying to help his peers. In his spare time, he works on his next books for Apress. He was also recently voted in as a school governor. In his work life, James is technical lead on a project in cooperation with Microsoft which could, quite literally, save the planet.

Contents at a Glance 5
Contents 6
Foreword 15
About the Author 16
About the Technical Reviewer 17
Acknowledgments 18
Introduction 19
Introducing SQL Server 2005 Integration Services 22
A Little History 23
What Can SSIS Do? 24
The First Time Ever I Saw SSIS 27
I Have Such Delights to Show You 27
An Interview with Donald Farmer 28
Become a Knowledge Broker 31
Summary 33
The Architecture of SSIS 34
A Brief SQL Server Architecture Overview 34
SSIS and SQL Server Analysis Services 35
Common Language Runtime Integration 36
SSIS Architecture Overview 36
The Power of the Data Flow Architecture 42
Summary 49
From DTS to SSIS 50
What’s New? 50
Preparing to Migrate Legacy DTS Packages 51
Using the Package Migration Wizard 60
Summary 64
The IDE and Extending Visual Studio 65
A First Look at BIDS 65
Projects and Solutions 66
Properties Explorer 71
Compilation, Running, and Debugging 73
The Toolbox 80
Class Explorer 81
Project Templates 82
The SSIS Menu 83
Package Explorer 89
Execution Results 90
Summary 91
Package Hierarchy 93
Vocabulary 94
Containers 94
Packages 95
Checkpoints 95
Variables 96
Variable Scope 97
Event Handlers 98
Adding Components 102
Using Executable Designers 102
Precedence Constraints 102
Flow Between Executables 104
Directing Errors 108
Summary 110
Control Flow 111
Where Does the Control Flow Fit? 112
For Loop Container 113
Foreach Loop Container 115
Sequence 132
ActiveX Script Task 133
Analysis Services Execute DDL Task 133
DDL Configuration 134
Analysis Services Processing Task 135
Bulk Insert Task 136
Data Mining Query Task 140
Execute DTS 2000 Package Task 141
Execute Package Task 142
Execute Process Task 143
Execute SQL Task 146
File System Task 148
FTP Task 149
Message Queue Task 150
Script Task 151
Send Mail Task 153
Transfer Database Task 154
Transfer Error Messages Task 154
Transfer Jobs Task 155
Transfer Logins Task 156
Transfer Master Stored Procedures Task 156
Transfer SQL Server Objects Task 157
Web Service Task 158
WMI Data Reader Task 166
WMI Event Watcher Task 166
XML Task 166
Summary 175
Maintenance Plans 176
Back Up Database 176
Check Database Integrity 179
Execute SQL Server Agent Job 180
Execute T- SQL Statement 181
History Cleanup 181
Maintenance Cleanup 183
Notify Operator 184
Rebuild Index 185
Reorganize Index 186
Shrink Database 187
Update Statistics 187
Summary 188
Data Flow Data Sources 189
DataReader Source 189
Excel Source 196
Flat File Source 200
OLEDB Source 209
Raw File Source 212
XML Source 213
Summary 216
Basic Data Flow Transformations 217
SSIS Data Types 217
Data Viewers 218
Audit 222
Character Map 222
Conditional Split 223
Copy Column 230
Data Conversion 232
Derived Column 233
Export Column 236
Import Column 238
Lookup 239
Merge 243
Merge Join 250
Multicast 252
OLE DB Command 253
Percentage Sampling 254
Row Count 255
Row Sampling 256
Sort 256
Union All 257
Summary 258
Advanced Data Flow Transformations 259
Aggregate 260
Data Mining Query 263
Fuzzy Grouping 263
Fuzzy Lookup 266
Pivot 272
Script Component 276
Slowly Changing Dimension 281
Term Extraction 286
Term Lookup 291
Unpivot 294
Summary 298
Data Flow Destinations 299
Data Mining Model Training 299
DataReader Destination 301
Dimension Processing 302
Excel Destination 305
Flat File Destination 309
OLE DB Destination 310
Partition Processing 314
Raw File Destination 318
Recordset Destination 320
SQL Server Destination 321
SQL Server Mobile Destination 324
Summary 326
Optimization for SSIS 327
Decisions: The Basics 327
Identifying Performance Issues 331
SQL Server Tweaks 348
SSIS- Specific Optimizations 349
Summary 353
SSIS Security 355
SSIS Security Options 356
Custom Security Options 369
Summary 371
Scripting, .NET Style 372
Development Environments 372
OOP 373
Design Patterns 379
. NET Concepts 380
Classes in the Script Component 382
Script Component Crash Course 386
Summary 402
Custom Components 403
What Flavor Would You Like? 404
What Makes a Custom Component? 405
Deriving from and Overriding SSIS Classes 408
Putting It All Together 415
Designers 468
Summary 472
Interview with Donald Farmer 474
The Package Object 478
Package Properties 478
Package Methods 488
The Control Flow Object 492
Control Flow Common Properties 492
Control Flow Task Properties 495
Maintenance Plans Reference 524
Maintenance- Plan Tasks Common Properties 524
Maintenance- Plan Tasks 528
Data Flow Object Reference 534
Index 538