IESSIS2: Immersion Event on Advanced Integration Services

Overview

This 400-level, 5-day, advanced SSIS training class was created to help technology professionals take their SQL Server Integration Services (SSIS) skills to the next level. Developed by Tim Mitchell and Andy Leonard, 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 catalog customization, we will 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.

Instructors: Andy Leonard, Tim Mitchell

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: 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 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.