Pragmatic SSIS (Online)

A four half day class designed to help you understand all aspects of SQL Server Integration Services (SSIS), the ETL tool of choice for SQL Server users. We will use a variety of lab exercises and small projects to ensure you gain familiarity in how the design and execution environments work and be prepared to build your own real world applications using this SQL Server application. Students will learn how to use SSIS in the real-world. With the hands-on labs, you will learn how to create a SSIS package to load a database. You’ll also learn how to make the package dynamic to where it will work and self-heal in the event of a failure. This course gives detailed and extensive demos on how to use SSIS in your environment.

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 - Integration Services Features

  1. SQL Server Integration Services Overview
    • Why use SSIS?
    • Common SSIS use Cases
    • Who Should use SSIS?
  2. Intro to the Development Environment and the Control Flow
    • Introduction to SQL Server Data Tools (SSDT)
    • Connection Managers
    • Precedence Constraints
    • Common Control Flow Tasks
  3. Loading Data with the Data Flow
    • Common Sources
    • Common Transforms
    • Common Destinations

Day 2 - Making your Package Dynamic

  1. Using Variables, Parameters and Expressions
    • Using Variables, Parameters, and Expressions to Make a Package Dynamic
    • Writing the SSIS Expression Language
    • Parameterized SSIS Objects (Tasks, Connections, Variables, etc…)
  2. Troubleshooting SSIS
    • Diagnosing a Package Error
    • Viewing Data in Flight
    • Creating Breakpoints
  3. Using Containers
    • Grouping
    • Sequence Container
    • For Loop Container
    • Foreach Loop Container

Day 3 - Loading a Data Warehousing

  1. Loading a Dimension
    • Data Warehouse Modeling
    • Loading a Dimension
  2. Loading a Fact Table
    • Loading a Fact Table
    • Dealing with Unknown Values
  3. Deploying SSIS Packages
    • Package vs. Project Deployment
    • SSIS Catalog
    • SSISDB

Day 4 - Administration

  1. Configuring SSIS Packages for Portability
    • Using Configuration Files to Externalize Configurations
    • Using Tables to Externalize Configurations
    • Setting up Environments
  2. SSIS Server Executions
    • Using the SSIS Catalog GUI
    • Using T-SQL with SSIS
    • Scheduling a Package
  3. Setting Up Auditing and Notification
    • Setting up Package Notifications with Event Handlers
    • Creating Package Auditing with SSIS Logging
    • Utilizing SSIS Reports

Common Questions

  • Will I be able to ask the instructor a question? Of course! You are encouraged to participate in the class by asking questions and sharing your own case studies.
  • How large is each class? Each class has a maximum capacity of 20-24 seats each month, they do fill up quickly.
  • How would I receive individual attention? During the class, the conference line opens 30 minutes prior to the class to answer individual questions. If you still need more help, individual sessions can be scheduled during the week of the class for class-related questions.
  • How much time is required of me to take this class? Each class is 4 hours a day so you’ll still have time to answer your emails or go to a meeting or two. To get the most out of the class, it’s extremely important to do the class labs so you can ask questions if you have problems.
  • What if I miss a class? Each class is recorded and posted 24 hours after the class. The recording can be downloaded for up to 30 days after the class.
  • Is the number a toll free number? GoToMeeting supports Voice of IP (VOIP) or a toll number. It is recommended that you have a speaker phone if you dial in, or use a head set for your comfort if you use VOIP. It is also recommended that if you can, have a second monitor so you can participate in a lab on one screen while the web meeting is happening on another.

Prerequisites

The target audiences of this class are developers, architects or IT professionals who have basic SQL skills. This class assumes that you have no previous knowledge of SSIS. In some of the scripting chapters, it is useful (but not mandatory) that the student has some basic VB experience. You will need to know how to run a query inside of SQL Server.

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
    • SQL Server Database Engine
    • SQL Server Integration Services
    • SQL Server Data Tools (SQL 2012/2014) or Business Intelligence Development Studio (SQL 2008/2008R2)
    • SQL Server Management Studio
    • 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.

Some of our clients