IESSIS1: Immersion Event on Learning SQL Server Integration Services

Overview

This 1-300-level, 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 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 2014. SSIS will need to be installed and functioning. You can obtain a copy of the Evaluation Edition of SQL Server 2014 here. We do not provide a classroom computer for you.

Instructor: Andy Leonard

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Ready to register? Please see our Immersion Events Schedule for class dates and our comprehensive Immersion Events F.A.Q. for class costs and other frequently asked questions.


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

Ready to register? Please see our Immersion Events Schedule for class dates and our comprehensive Immersion Events F.A.Q. for class costs and other frequently asked questions.


Questions?

If you have any questions not answered by our F.A.Q., please contact us.