Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services, Volume 1

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