Learn SSIS (SQL Server Integration Services) from Andy Leonard; author, enterprise data architect, and experienced corporate team leader.
A recording of From Zero to SSIS is immediately available with an SSIS Premium or Premium Level subscription. Click here for details.
Synopsis
This 100-200-300-level, 4-5-day, SQL Server Integration Services (SSIS) training class was created to train technology professionals in the fine art of using SSIS to build data integration and Extract-Transform-Load (ETL) solutions. Developed by author, international speaker, and experienced enterprise data architect Andy Leonard, the training is focused around labs and emphasizes a hands-on approach. Most technologists learn SSIS by doing so this training is designed to maximize the time attendees spend working with SSIS.
At the conclusion of the training, attendees will have been exposed to:
- Using SSIS to develop data integration solutions
- Using SSIS to load a data warehouse dimension
- Troubleshooting real-world SSIS Data Flow Task errors
- Deploying SSIS Solutions
- Managing, monitoring, and administering SSIS in the enterprise
Target audience: Database professionals, application developers, and business intelligence practitioners interested in acquiring or expanding their existing SSIS skill set.
Prerequisites: No experience with SQL Server or SQL Server Integration Services is required before attending this training. It is helpful (but not required) that students possess some knowledge of and experience with relational databases. SQL Server knowledge / experience will be more helpful than experience and knowledge with other technologies.
Bring your own laptop: You will need to bring your laptop and you will need a version of SQL Server 2017. SSIS will need to be installed and functioning. You can obtain a free copy of the Developer Edition of SQL Server 2017 here. We do not provide a classroom computer for you.
Curriculum
Module 1: Introduction
In the introduction we share information about the instructor, course flow, and content, and gather information about attendee expectations for the course. Topics covered include:
- Training overview
- Expectations
- Introductions
Module 2: Creating Your First SSIS package
We believe many learn SSIS best by doing, so we get attendee’s hands on SSIS as soon as possible. In this module, we demonstrate how to build an SSIS package and invite attendees to walk through building a basic SSIS package with us. Topics covered include:
- Introduction to SQL Server Data Tools – Business Intelligence (SSDT-BI), a version of Microsoft Visual Studio that serves as the SSIS integration development environment (IDE)
- Visual Studio tips and tricks
- Exercise: Create an SSIS package
Module 3: Introduction to the Data Flow Task
SSIS was designed to move data. The SSIS Data Flow Task is a complex data integration engine built to support generic data load use cases. In this module, we introduce SSIS Data Flow concepts. Topics covered include:
- Introduction to the Data Flow Task
- Connection Manager and Data Flow Task basics
- Source and destination adapters
- Exercise: Create a Real Data Flow, Part 1
Module 4: Data Flow Task 201
Once the basics of Data Flow development are understood, we introduce a basic version of the SSIS design pattern called the incremental load. Building idempotent (safely re-executable) SSIS packages is one key to successful data integration in the enterprise. Topics covered include:
- Intermediate Data Flow concepts
- Building re-executable data loaders
- Exercise: Create a Real Data Flow, Part 2
Module 5: Data Flow Task 202
Change detection is one of the most important components of Extract, Transform, and Load (ETL) projects. In this module, we expand the basic change detection introduced in the previous module and begin to examine SSIS design patterns that improve SSIS Data Flow performance. Topics covered include:
- Advanced Data Flow concepts
- Building and tuning robust incremental loader
- Exercise: Create a Real Data Flow, Parts 3 and 4
Module 6: The Control Flow
The SSIS Control Flow is the work flow manager for an SSIS package. In this module, we demonstrate work flow containment and management in the SSIS Control Flow, including advanced Precedence Constraint configuration and package execution control. Topics covered include:
- Containers, precedence, and work flow
- Transactions, restartability, and locking
Module 7: Loop Containers
Loop containers provide execution work flow isolation and iterative functionality. In this module, we examine traditional and non-traditional configurations for the For Loop and ForEach Loop containers. Topics covered include:
- Using For Loop and Foreach Loop Containers
Module 8: Data Flow Task 301
After turning our attention to the SSIS Control Flow, we return to the SSIS Data Flow for an extended period of hands-on labs. In this module, attendees are tasked with the first of three labs that build a robust data integration loader using SSIS, with an instructor available to assist with questions. This lab focuses on transforming the data by applying strong-typing rules and managing data that violates those rules. Topics covered include:
- Data typing basics
- Building an incremental loader for real-world data
- Exercise: Build a Robust Incremental Staging Loader, Part 1
Module 9: Data Flow Task 302
In this module, attendees are tasked with the second of three labs that build a robust data integration loader using SSIS, with an instructor available to assist with questions. This lab focuses on using built-in SSIS Adapter functionality to manage and execute DDL operations during data integration staging development. Topics covered include:
- Intermediate data staging
- Managing real-world changes to data sources
- Exercise: Build a Robust Incremental Staging Loader, Part 2
Module 10: Data Flow Task 303
In this module, attendees are tasked with the third of three labs that build a robust data integration loader using SSIS, with an instructor available to assist with questions. This lab reinforces writing idempotent SSIS Data Flow packages and introduces data quality concepts via data cleansing in SSIS. Topics covered include:
- Basic data cleansing
- Deriving values
- Exercise: Build a Robust Incremental Cleansing Loader
Module 11: Event Handlers, Logging, and Configurations
An important component of any solution is messaging and configuration. In this module, we demonstrate SSIS’s messaging model in the context of event handlers, logging, and managing externalized parameter and variable values. Topics covered include:
- A survey of SSIS event handlers focusing on OnError and OnInformation events
- Using SSIS’s built-in logging facility to capture package execution details and statistics
- Using built-in package configurations to externalize variable values
Module 12: Security, Deployment, and Execution
SSIS facilitates secure connection and parameter value management. In this module, we build on our understanding of messaging, logging, and externalization by examining project, package, and connection security. We then turn our attention to deployment and execution patterns, and discuss the role of security in each. Topics covered include:
- SSIS Package deployment options
- Security implications
- SSIS Package execution
Module 13: ETL Design Patterns
SSIS is a powerful enterprise data integration engine. In this module, we discuss experiences using SSIS to solve complex and “edgy” use cases, and some patterns we’ve realized from those engagements. Topics covered include:
- Leveraging less-documented features of SSIS
- Using Parent-Child design pattern to achieve “hands-free” custom logging and creative custom configuration
- ETL Instrumentation
Module 14: Enterprise Execution Patterns
Data integration in a large enterprise is different from data integration in a smaller shop. In this module, we discuss patterns of SSIS development and management that lower the total cost of owning a data integration solution built using SSIS. Topics covered include:
- Leveraging the Parent-Child design pattern and much of what we’ve learned over the past four days to build a metadata-driven SSIS execution framework engine