Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

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