Synopses & Reviews
Dive into the 2012 release of SQL Server Integration Services with this book-and-video package
This book-and-video package teaches you the essential elements of Microsoft SQL Server Integration Services (SSIS) from the ground upno previous knowledge of SSIS is required. Each lesson is streamlined to teach a specific component of SSIS, helping you to learn just what you need to succeed. The lessons include three major components: a description of how each SSIS feature or process works, a tutorial that walks you through the process or technique, and an accompanying video lesson, all of which present you with a thorough introduction to Microsoft business intelligence tools and SSIS.
Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer:
- Furnishes you with all the information you need to confidently start your first SSIS project
- Integrates instructional videos with each of the hands-on lessons found in the book
- Provides expert instruction from leading SQL Server authorities
- Covers the latest changes and updates in the SQL Server 2012 release
The 24-Hour Trainer is a unique book-and-video package that delivers step-by-step lessons for handling real-world scenarios. Each lesson in the book is accompanied by an instructional video that reinforces the content while providing additional explanations and tips.
On the DVD
Don't just learndo!
The lessons within the book are each accompanied by an instructional demonstration on the DVD.
You'll learn to:
- Create your first package
- Upgrade packages to SQL Server 2012
- Use a variety of tasks
- Use Data Quality Services to cleanse data
- Incrementally load data
- Load a data warehouse
- Roll up, order, join, audit, cleanse, and separate data
- Deploy, configure, and schedule packages
Please see the DVD appendix for details and complete system requirements.
This book and video package is geared at introducing DBAs and developers who are just starting to get a handle on Microsoft Business Intelligence tools, and SQL Server Integration Services (SSIS) in particular. (It is therefore intended to be the first book you read---and the first video you watch--before moving on to Knight's Professional SQL Server 2012 Integration Services.) The book and accompanying video give the novice reader just enough experience with the product to start their first SSIS project.
The book tightly integrates instructional videos--provided on the book's accompanying DVD--with each of the hands-on lessons found in the book. Each lesson comprises three major components:
• a lesson which describes how a certain component or process works.
• a tutorial to walk a user through a micro-process
• a video where a user can walk through the lesson with the author.
Book-and-video package gets novices up to speed on Microsoft SQL Server 2012
If you need a practical, hands-on introduction, especially to SQL Server Integration Services (SSIS), this book-and-video package from authority Brian Knight is the perfect solution. Each lesson includes three major components: a description of how each SSIS feature or process works, a tutorial that walks you through the process or technique, and an accompanying video lesson. It's a complete learning package that will give you the confidence you need to start your first SSIS project.
- Guides novice database administrators and developers who are learning Microsoft SQL Server 2012 and SQL Server Integration Services (SSIS)
- Provides expert instruction from leading SQL Server authority and author, Brian Knight
- Includes a book and a video, complete instruction that includes lessons, hands-on tutorials, and video demonstrations by the author
- Covers the very latest changes and updates in the SQL Server 2012 release
Microsoft SQL Server 2012 Integration Services 24-Hour Trainer makes SQL Server 2012 and SSIS much less intimidating.
Note: CD-ROM/DVD and other supplementary materials are not included as part of the e-book file, but are available for download after purchase.
About the Author
, SQL Server MVP, is the founder of Pragmatic Works. He is the author of more than a dozen books.
Devin Knight, MCTS, MCITP, is an author, consultant, and trainer with Pragmatic Works.
Mike Davis, MCTS, MCITP, is an author, developer, consultant, and trainer with Pragmatic Works.
Wayne Snyder, SQL Server MVP, is a Distinguished Architect at Mariner focused on business intelligence.
Table of Contents
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