About the Authors 9
Acknowledgments 10
Contents 11
Preface 21
Who This Book Is For 21
What This Book Covers 21
How This Book Is Structured 22
What This Book Covers 22
Instructional Videos on DVD 23
Conventions 23
Supporting Packages and Code 23
Errata 24
p2p.wrox.com 24
Welcome to SSIS 27
Import and Export Wizard 27
The Business Intelligence Development Studio 28
Architecture 28
Editions of SQL Server 2008 33
Summary 34
Section I: Installation and Getting Started 35
Lesson 1: Moving Data with the Import and Export Wizard 37
Try It 39
Lesson 2: Installing SQL Server Integration Services 43
Lesson 3: Installing the Sample Databases 47
Try It 48
Lesson 4: Creating a Solution and Project 51
Try It 52
Lesson 5: Exploring Business Intelligence Development Studio 55
The Solution Explorer 55
The Properties Window 57
The Toolbox 57
The BIDS Design Environment 58
Lesson 6: Creating Your First Package 61
Creating and Using Connection Managers 62
Using and Configuring Tasks 63
Exploring Package Encryption 64
Executing Packages 65
Try It 65
Section II: Tasks 67
Lesson 7: Connecting Control Flow Tasks with Precedence Constraints 69
Try It 72
Lesson 8: Manipulating Files with the File System Task 75
Try It 80
Lesson 9: Coding Custom Script Tasks 83
Try It 87
Lesson 10: Using the Execute SQL Task 91
Try It 96
Lesson 11: Using the Send Mail Task 99
Try It 103
Lesson 12: Using the FTP Task 107
Try It 110
Lesson 13: Creating a Data Flow 113
Try It 115
Section III: Data Flow 119
Lesson 14: Extracting Data from Sources 121
OLE DB Source 121
Excel Source 127
Flat File Source 130
Lesson 15: Loading Data to a Destination 135
OLE DB Destination 136
Flat File Destination 140
Excel Destination 142
Lesson 16: Changing Data Types with the Data Conversion Transform 145
Try It 147
Lesson 17: Creating and Replacing Columns with the Derived Column Transform 151
Try It 155
Lesson 18: Rolling Up Data with The Aggregate Transform 159
Try It 160
Lesson 19: Ordering Data with the Sort Transform 165
Try It 167
Lesson 20: Joining Data with the Lookup Transform 171
Cache Modes 171
The Cache Connection Manager and Transform 176
Lesson 21: Auditing Data with the Row Count Transform 181
Try It 181
Lesson 22: Combining Multiple Inputs with the Union All Transform 185
Try It 186
Lesson 23: Cleansing Data with the Script Component 189
Try It 191
Lesson 24: Separating Data with the Conditional Split Transform 195
Try It 197
Lesson 25: Altering Rows with the OLE DB Command Transform 201
Try It 203
Lesson 26: Handling Bad Data with the Fuzzy Lookup Transform 211
Try It 214
Lesson 27: Removing Duplicates with the Fuzzy Grouping Transform 221
Try It 223
Section IV: Variables and Expressions 229
Lesson 28: Making a Package Dynamic with Variables 231
Try It 233
Lesson 29: Making a Connection Dynamic with Expressions 237
Try It 238
Lesson 30: Making a Task Dynamic with Expressions 243
Try It 245
Section V: Containers 249
Lesson 31: Using Sequence Containers to Organize a Package 251
Try It 252
Lesson 32: Using For Loop Containers to Repeat Control Flow Tasks 255
Try It 256
Lesson 33: Using the Foreach Loop Container to Loop Through a Collection of Objects 261
Try It 262
Section VI: Configuration 267
Lesson 34: Easing Deployment with Configuration Tables 269
Try It 273
Lesson 35: Easing Deployment with Configuration Files 275
Try It 279
Lesson 36: Configuring Child Packages 281
Configuring an Execute Package Task 281
Configuring a Child Package 284
Try It 284
Section VII: Troubleshooting and Logging 287
Lesson 37: Logging Package Data 289
Try It 292
Lesson 38: Using Event Handlers 295
Creating Event Handlers 296
Common Uses for Event Handlers 297
Try It 298
Lesson 39: Troubleshooting Errors 301
Working in the Progress Tab 302
Troubleshooting Steps 303
Try It 304
Lesson 40: Using Data Viewers 307
Try It 311
Lesson 41: Using Breakpoints 313
Try It 315
Section VIII: Deployment and Administration 319
Lesson 42: Deploying Packages 321
Deploying Packages Using a Deployment Utility 321
Other Methods of Deploying Packages 324
Try It 324
Lesson 43: Configuring the SSIS Service 327
Try It 329
Lesson 44: Securing SSIS Packages 331
Try It 333
Lesson 45: Running SSIS Packages 335
Try It 339
Lesson 46: Scheduling Packages 341
Using Proxy Accounts 342
Try It 343
Section IX: Data Warehousing 345
Lesson 47: Loading a Dimension Table 347
Try It 349
Lesson 48: Loading a Fact Table 353
Try It 353
Section X: Wrap Up and Review 357
Lesson 49: Bringing It All Together 359
Lesson Requirements 359
Hints 360
Step-by-Step 360
Appendix A: SSIS Component Crib Notes 365
When to Use Control Flow Tasks 365
When to Use Data Flow Transforms 367
Appendix B: Problem and Solution Crib Notes 369
Appendix C: What\u2019s on the DVD? 373
System Requirements 373
Using the DVD 373
What\u2019s on the DVD 374
Troubleshooting 374
Customer Care 374
Index 375