PREFACE xxv WELCOME TO SSIS 1 SECTION 1: INSTALLATION AND GETTING STARTED LESSON 1: MOVING DATA WITH THE IMPORT AND EXPORT WIZARD 11 LESSON 2: INSTALLING SQL SERVER INTEGRATION SERVICES 17 LESSON 3: INSTALLING THE SAMPLE DATABASES 21 LESSON 4: CREATING A SOLUTION AND PROJECT 25 LESSON 5: EXPLORING SQL SERVER DATA TOOLS 29 LESSON 6: CREATING YOUR FIRST PACKAGE 35 LESSON 7: UPGRADING PACKAGES TO SQL SERVER 2012 41 LESSON 8: UPGRADING TO THE PROJECT DEPLOYMENT MODEL 47 SECTION 2: CONTROL FLOW LESSON 9: USING PRECEDENCE CONSTRAINTS 59 LESSON 10: MANIPULATING FILES WITH THE FILE SYSTEM TASK 63 LESSON 11: CODING CUSTOM SCRIPT TASKS 71 LESSON 12: USING THE EXECUTE SQL TASK 79 LESSON 13: USING THE EXECUTE PROCESS TASK 87 LESSON 14: USING THE EXPRESSION TASK 93 LESSON 15: USING THE SEND MAIL TASK 99 LESSON 16: USING THE FTP TASK 107 LESSON 17: CREATING A DATA FLOW 113 SECTION 3: DATA FLOW LESSON 18: EXTRACTING DATA FROM SOURCES 121 LESSON 19: LOADING DATA TO A DESTINATION 139 LESSON 20: CHANGING DATA TYPES WITH THE DATA CONVERSION TRANSFORM 151 LESSON 21: CREATING AND REPLACING COLUMNS WITH THE DERIVED COLUMN TRANSFORM 159 LESSON 22: ROLLING UP DATA WITH THE AGGREGATE TRANSFORM 167 LESSON 23: ORDERING DATA WITH THE SORT TRANSFORM 173 LESSON 24: JOINING DATA WITH THE LOOKUP TRANSFORM 179 LESSON 25: AUDITING DATA WITH THE ROW COUNT TRANSFORM 189 LESSON 26: COMBINING MULTIPLE INPUTS WITH THE UNION ALL TRANSFORM 193 LESSON 27: CLEANSING DATA WITH THE SCRIPT COMPONENT 197 LESSON 28: SEPARATING DATA WITH THE CONDITIONAL SPLIT TRANSFORM 203 LESSON 29: ALTERING ROWS WITH THE OLE DB COMMAND TRANSFORM 211 LESSON 30: HANDLING BAD DATA WITH THE FUZZY LOOKUP 221 LESSON 31: REMOVING DUPLICATES WITH THE FUZZY GROUPING TRANSFORM 231 SECTION 4: MAKING PACKAGES DYNAMIC LESSON 32: MAKING A PACKAGE DYNAMIC WITH VARIABLES 241 LESSON 33: MAKING A PACKAGE DYNAMIC WITH PARAMETERS 249 LESSON 34: MAKING A CONNECTION DYNAMIC WITH EXPRESSIONS 255 LESSON 35: MAKING A TASK DYNAMIC WITH EXPRESSIONS 261 SECTION 5: COMMON ETL SCENARIOS LESSON 36: LOADING DATA INCREMENTALLY 269 LESSON 37: USING THE CDC COMPONENTS IN SSIS 281 LESSON 38: USING DATA QUALITY SERVICES 295 LESSON 39: USING THE DQS CLEANSING TRANSFORM 309 LESSON 40: CREATING A MASTER PACKAGE 317 SECTION 6: CONTAINERS LESSON 41: USING SEQUENCE CONTAINERS TO ORGANIZE A PACKAGE 327 LESSON 42: USING FOR LOOP CONTAINERS TO REPEAT CONTROL FLOW TASKS 331 LESSON 43: USING THE FOREACH LOOP CONTAINER TO LOOP THROUGH A COLLECTION OF OBJECTS 337 SECTION 7: CONFIGURING PACKAGES LESSON 44: EASING DEPLOYMENT WITH CONFIGURATION TABLES 347 LESSON 45: EASING DEPLOYMENT WITH CONFIGURATION FILES 357 LESSON 46: CONFIGURING CHILD PACKAGES 365 SECTION 8: TROUBLESHOOTING SSIS LESSON 47: LOGGING PACKAGE DATA 375 LESSON 48: USING EVENT HANDLERS 381 LESSON 49: TROUBLESHOOTING ERRORS 387 LESSON 50: USING DATA VIEWERS 393 LESSON 51: USING BREAKPOINTS 399 SECTION 9: ADMINISTERING SSIS LESSON 52: CREATING AND CONFIGURING THE SSIS CATALOG 407 LESSON 53: DEPLOYING PACKAGES TO THE PACKAGE CATALOG 411 LESSON 54: CONFIGURING THE PACKAGES 415 LESSON 55: CONFIGURING THE SERVICE 421 LESSON 56: SECURING SSIS PACKAGES 425 LESSON 57: RUNNING SSIS PACKAGES 431 LESSON 58: RUNNING PACKAGES IN T-SQL AND DEBUGGING PACKAGES 437 LESSON 59: SCHEDULING PACKAGES 443 SECTION 10: LOADING A WAREHOUSE LESSON 60: DIMENSION LOAD 451 LESSON 61: FACT TABLE LOAD 459 SECTION 11: WRAP UP AND REVIEW LESSON 62: BRINGING IT ALL TOGETHER 465 APPENDIX A: SSIS COMPONENT CRIB NOTES 473 APPENDIX B: PROBLEM AND SOLUTION CRIB NOTES 477 APPENDIX C: WHAT S ON THE DVD? 481 INDEX 483