Course Overview

The Introduction to SQL Server Integration Services (SSIS) training teaches attendees how to use SSIS to build high-performance ETL (Extract, Transform, and Load) and workflow packages and projects.

We also offer Developing and Administering SQL Server Integration Services (SSIS), a 5-day class that adds coverage of administrative topics, such as project deployment, managing packages in an SSIS catalog, securing the SSIS catalog, and more.

Note: This class is appropriate for SQL Server 2012 through 2019.

Key Learning Areas

  • Create sophisticated SSIS packages for extracting, transforming, and loading data
  • Use containers to efficiently control repetitive tasks and transactions
  • Configure packages to dynamically adapt to environment changes
  • Use Data Quality Services to cleanse data
  • Incrementally load data into a data warehouse
  • Deploy and execute packages
  • Successfully troubleshoot packages
  • Configure package reliability

Course Outline

SSIS Overview

  • SSIS Overview
  • Import/Export Wizard
  • Exporting Data with the Wizard
  • Common Import Concerns
  • Quality Checking Imported/Exported Data

Working with Solutions and Projects

  • Working with SQL Server Data Tools
  • Understanding Solutions and Projects
  • Working with the Visual Studio Interface

Basic Control Flow

  • Introduction to the Control Flow
  • Working with Tasks
  • Understanding Precedence Constraints
  • Annotating Packages
  • Grouping Tasks
  • Package and Task Properties
  • Connection Managers
  • Favorite Tasks

Common Tasks

  • SSIS Tasks in the Common Section
  • Analysis Services Processing
  • Bulk Insert Task
  • Data Profiling Task
  • Execute Package Task
  • Execute Process Task
  • Expression Task
  • File System Task
  • FTP Task
  • Hadoop Tasks
  • Script Task Introduction
  • Send Mail Task
  • Web Service Task
  • XML Task

Data Flow Sources and Destinations

  • The Data Flow Task
  • The Data Flow SSIS Toolbox
  • Working with Data Sources
  • SSIS Data Sources
  • Working with Data Destinations
  • SSIS Data Destinations
  • Chapter 5 Lab
  • Answers to Exercises

Data Flow Transformations

  • Transformations Overview
  • Transformations
  • Configuring Transformations

Making Packages Dynamic

  • Features for Making Packages Dynamic
  • Package Parameters
  • Project Parameters
  • Variables
  • SQL Parameters
  • Expressions in Tasks
  • Expressions in Connection Managers
  • After Deployment
  • How it all fits together

Containers

  • Sequence Containers
  • For Loop Containers
  • Foreach Loop Containers

Troubleshooting and Package Reliability

  • Understanding MaximumErrorCount
  • Breakpoints
  • Redirecting Error Rows
  • Logging
  • Event Handlers
  • Using Checkpoints
  • Transactions

Deploying to the SSIS Catalog

  • SSIS Deployment Overview
  • The SSIS Catalog
  • Deploying Projects
  • Working with Environments
  • Executing Packages in SSMS
  • Executing packages from the command line
  • Deployment Model Differences

Prerequisites

Students should be comfortable with SQL Server Management Studio and writing basic queries.