Pragmatic Master SSIS (Online)

During this 4 day online course, you will learn how to use SSIS in your enterprise to solve common extract, transform and load (ETL) challenges. This class assumes you know the basics of SSIS and takes you from the novice level to a more effective, advanced ETL developer. Much of this class focuses on implementing common real-world SSIS patterns taught from the experts of SSIS. You'll also learn how to performance tune SSIS. Be prepared for a very in-depth day of SSIS that gets advanced in many areas.

IMPORTANT: When you purchase a seat in this class you also receive 2 hours of our BI Virtual Mentor service for assistance with YOUR project work. This remote service lets you work ONE on ONE with our BI mentors to accelerate YOUR project and further improve YOUR skills. Your Virtual Mentor hours can be used during business hours anytime during the 12 months following your class. To set up a session, download this simple questionnaire, fill it out, and send it as an attachment to mentor@pragmaticworks.com. This unique Pragmatic Works offering insures that your training will translate into real world success for YOUR projects.

All virtual training students are provided a previously recorded version of the class that are available for 7 days after the end of the class.

Outline

    Day 1

  1. Loading a Dimension Table
    • Data Warehouse Design Basics
    • Loading a Dimension with the SCD Wizard
    • Using Alternate Patterns for Loading a Dimension
  2. Loading a Fact Table
    • Fact Table Load Pattern
    • Understanding Dimension Lookup Pattern
    • Dealing with Unknown Values
  3. Creating an Incremental Load Pattern
    • Loading Data More Efficiently
    • Designing an Incremental Load Pattern
    • Using Change Data Capture (CDC) in SSIS 2008 or 2012

    Day 2

  4. Performance Tuning SSIS
    • Addressing Data Source Performance Problems
    • Using the Right Tool for the Job: Transform Performance Tuning
    • Destination Performance Enhancements
  5. Scaling Out SSIS
    • Loading Large Datasets in Parallel
    • Creating and Using the Data Load Queue Pattern
  6. Restartability Patterns in SSIS
    • Using SQL Server Transactions in SSIS
    • Package Restart Strategies with SSIS Transactions
    • Enabling SSIS Checkpoints for Restartability

    Day 3

  7. Scripting in SSIS
    • Common Script Task Patterns
    • Scripting Patterns in the Data Flow with the Script Component
  8. Data De-Duplication
    • Understanding Fuzzy Logic
    • Getting Rid of Duplicate Data with the Fuzzy Grouping
    • Keeping your Data Clean with the Fuzzy Lookup
  9. Advanced Patterns
    • Using the Term Extraction Transform
    • Using the Unpivot Transform
    • Using the Data Mining Transform
  10. Upgrading to SSIS 2012/2014 and the Project Deployment Model
    • What's new in SSIS?
    • Upgrading to the Project Deployment Model
    • Deploying Packages to the Project Deployment Model
    • Configuring and Administering Packages

    Day 4

  11. Integrating Data Quality Services in SSIS
    • Using DQS to Cleanse Data
    • Integrate the DQS Pattern into SSIS
  12. Creating a Master Package Pattern
    • Identifying Opportunities to Run your Packages in Parallel
    • Creating a Framework to Simplify your Package Execution
    • Designing a Logging Framework
  13. SSAS Integration
    • Processing a Cube with SSIS
    • Automating SSAS Partition Creation in SSIS
    • Incrementally Processing Data into your Cube

Prerequisites

The target audience of this class are developers, architects or IT professionals who have basic SSIS skills. This class assumes you know the basics of SSIS and takes you from the novice level to a more effective, advanced ETL developer. We recommend our Pragmatic SSIS class for those new to SSIS. Having a basic understanding of VB will help but is not required for this course.

SystemRequirements

  • Microsoft Windows XP Professional, Microsoft Windows Server 2003, Microsoft Vista, Windows 7 or Windows 8
  • 1 GHz or faster CPU
  • 1 GB of available RAM (recommended 2 GB)
  • 20 GB of free hard drive space
  • SQL Server Developer, Standard or Enterprise Edition 2008/2008R2/2012/2014 (Many features discussed are Enterprise/Developer edition only)
    • SQL Server Database Engine
    • SQL Server Integration Services
    • SQL Server Analysis Services (Optional)
    • SQL Server Data Tools (SQL 2012/2014) or Business Intelligence Development Studio (SQL 2008/2008R2)
    • SQL Server Management Studio
    • Data Quality Services (SQL 2012/2014only)
    • Data Quality Client (SQL 2012/2014 only)
    • Follow this installation guide to ensure all items are installed correctly
  • AdventureWorks2008R2 or AdventureWorks2012 databases installed on the instance.
  • AdventureWorksDW2008R2 or AdventureWorksDW2012 databases installed on the instance.
  • HigherEd and HigherEdDW databases should be restored on the instance. The HigherEd database backup files can be found in the class download in C:\SSISMaster\databases

Some of our clients