Advanced SSIS Training Course

Level-up your SSIS skills with training from Andy Leonard; author, enterprise data architect, and experienced corporate team leader.

Course Stats

Level: 300, 400
Length: 3 days
Location: On-site or remote
Delivery: Live and in-person
Instructor: Andy Leonard
Author: Andy Leonard

Overview

This 300-400-level, 3-day, advanced SSIS training class was created to help technology professionals take their SQL Server Integration Services (SSIS) skills to the next level. The training is focused around lectures and emphasizes a demonstration approach.

If you are looking to take your SQL Server Integration Services (SSIS) skills to the next level, this course is for you. From data flow internals to error handling patterns, and auditing/logging to SSIS Catalog customization, I illustrate and demonstrate SSIS Design Patterns to help bring you to the next level in your SSIS skill set.

Target audience: Database professionals, application developers, and business intelligence practitioners interested in expanding their existing SSIS skill set.

Prerequisites: One year experience with SQL Server and SQL Server Integration Services is required before attending this training.

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.

Ready to register? Please visit Enterprise Data & Analytics Training for public training delivery dates.

Interested in private training for your team? Please contact Enterprise Data & Analytics for information about scheduling private training for your enterprise.

Interested in other levels of SSIS training or Azure Data Factory?
Visit Andy’s SSIS Training page!
From Zero to SSIS
Expert SSIS
Fundamentals of Azure Data Factory 
Mastering the SSIS Catalog
Developing SSIS Data Flows with Labs 
Advanced SSIS

 

Quotes From Past Attendees

Listed below are some verbatim quotes from recent attendees of this class:

  • Extremely excited going into this class, and I was not disappointed.”
  • “I thoroughly enjoyed the class. All my questions I came with were answered and I now have a head full of new ideas.”
  • “Really liked the course, especially the interactivity between the Instructors and the students.”
  • “Great content, loved that there was a large class and we got to discuss issues that attendees were experiencing which allowed us to get into some of the topics in more depth.”
  • “Very good – very knowledgeable, friendly, entertaining. Covered a lot and willing to do whatever it takes to answer, demonstrate.”
  • “This was the best training class I had in a long time.”
  • “Absolutely great. Quality and quantity of material was excellent, exceeds expectations.”

Curriculum

Module 1: SSIS Catalog

The SSIS Catalog is a centralized data integration repository for SQL Server Integration Services (SSIS) projects and packages. SSIS Catalog functionality includes package and project storage, package and project configuration, package and project validation, package execution, and package execution monitoring. The Catalog is not required for SSIS package execution, one can still use the Package Deployment Model (the pre-2012 methods for storing and executing SSIS packages). Topics covered include:

  • Create an SSIS Catalog
  • Deploy an SSIS Project to the Catalog
  • Execute an SSIS Package
  • Monitor SSIS Package execution via Catalog Reports
  • Parameterization and environments

Module 2: SSIS Security

SSIS provides package- and project-level security options. In this module we describe the available Protection Level options and best practices. Topics covered include:

  • Protection Level Property options
  • Data Security
  • Secure Connection Managers
  • Secure Parameterization
  • SSIS Admin role

Module 3: Load Performance

SSIS is not the only way to accomplish data integration. In this module we compare SSIS to other data integration options. Topics covered include:

  • Data Flow vs. T-SQL statements
  • BCP / bulk insert

Module 4: Data Flow Internals

SSIS is built to read data from a source and load the data to a destination. SSIS package performance can vary widely depending on options and load patterns. In this module, we discuss various performance patterns and demonstrate how to test SSIS performance for optimal package performance. Topics covered include:

  • Buffer architecture
  • Incremental loads

Module 5: Testing Patterns

Although SQL Server Integration Services (SSIS) contains “SQL Server” in the name, SSIS development is actually software development. Software development best practices apply, and one very important software development best practice is software must be tested. In this module we discuss SSIS package testing techniques and practices. Topics covered include:

  • Unit testing patterns
  • ETL Instrumentation
  • Performance testing patterns
  • Binary Large Objects (BLObs) performance testing

Module 6: SSIS Scripting

SSIS provides the Script Task and Script Component. These extremely powerful objects allow data integration developers to extend the functionality of an SSIS package using .Net scripting. Topics covered include:

  • Using the Script Component as a Data Flow Source, Transformation, and Destination
  • Using the Script Task to raise messages

Module 7: Data Quality and SSIS

Data quality and master data management (MDM) are vital components of any data warehouse project. SSIS provides some facility to address data quality and MDM concerns. Topics covered include:

  • Data Quality Services (DQS)

Module 8: Hacking the SSIS Catalog

The SSIS Catalog performs some functions in an enterprise data integration lifecycle; other functions are incomplete. In this module, we discuss the things the Catalog does well and not-so-well. Topics covered include:

  • Catalog Package execution via T-SQL
  • Catalog customization

Module 9: Advanced Logging

The SSIS Catalog includes built-in and configurable logging, but also supports legacy logging options. In this module, we examine logging levels and their benefits. Topics covered include:

  • SSIS Catalog logging levels
  • Legacy logging patterns
  • Custom logging patterns
  • Data taps

Module 10: Advanced SSIS Load Patterns

SSIS supports loading data from many sources. In this module, we examine pitfalls and patterns for loading data from disparate data sources. Topics covered include:

  • XML
  • JSON
  • OData
  • Incremental example – SCD wizard vs. data flow vs. MERGE

Module 11: Error Handling

SSIS provides built-in fault tolerance, error flows, and execution error handling. In this module, we examine the vital topic of data integration error handling in SSIS. Topics covered include:

  • Event handlers
  • Alternate data paths
  • Data validation
  • Error Messaging

Module 12: Data Edge Cases

In this Tales-from-the-Dark-Side session, Tim and Andy share some of the odd data load requirements they’ve encountered and how they used SSIS to provide robust solutions. Topics covered include:

  • Type II data containing new and updated records in the same load
  • Fun with SSIS Variable locking

Module 13: Business Intelligence Markup Language (Biml)

Business Intelligence Markup Language (Biml) is revolutionizing data integration development. In this session, we discuss and examine meta-design-patterns with Biml. Topics covered include:

  • Introduction to Biml and BimlScript
  • Using Biml as an SSIS design patterns engine

Ready to register? Please visit Enterprise Data & Analytics Training for public training delivery dates.

Interested in private training for your team? Please contact Enterprise Data & Analytics for information about scheduling private training for your enterprise.

Interested in other levels of SSIS training or Azure Data Factory?
Visit Andy’s SSIS Training page!
From Zero to SSIS
Expert SSIS
Getting Started with Azure Data Factory