This course is intended for IT professionals who need to learn how to use SSIS to build, deploy, maintain, and secure Integration Services projects and packages, and to use SSIS to extract, transform, and load data to and from SQL Server. This course is similar to the retired Course 20767-C: Implementing a SQL Data Warehouse but focuses more on building packages, rather than the entire data warehouse design and implementation.


* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

After completing this course, you will be able to:
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
Successfully troubleshoot packages
Create and Manage the SSIS Catalog
Deploy, configure, and schedule packages
Secure the SSIS Catalog

  • Module 1: SSIS Overview

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

  • Module 2: Working with Solutions and Projects

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

  • Module 3: Basic Control Flow

  • Working with Tasks
    Understanding Precedence Constraints
    Annotating Packages
    Grouping Tasks
    Package and Task Properties
    Connection Managers
    Favorite Tasks

  • Module 4: Common Tasks

  • Analysis Services Processing
    Data Profiling Task
    Execute Package Task
    Execute Process Task
    Expression Task
    File System Task
    FTP Task
    Hadoop Task
    Script Task Introduction
    Send Mail Task
    Web Service Task
    XML Task

  • Module 5: 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

  • Module 6: Data Flow Transformations

  • Transformations
    Configuring Transformations

  • Module 7: Making Packages Dynamic

  • Features for Making Packages Dynamic
    Package Parameters
    Project Parameters
    SQL Parameters
    Expressions in Tasks
    Expressions in Connection Managers
    After Deployment
    How It All Fits Together

  • Module 8: Containers

  • Sequence Containers
    For Loop Containers
    Foreach Loop Containers

  • Module 9: Troubleshooting and Package Reliability

  • Understanding MaximumErrorCount
    Redirecting Error Rows
    Event Handlers
    Using Checkpoints

  • Module 10: Deploying to the SSIS Catalog

  • The SSIS Catalog
    Deploying Projects
    Working with Environments
    Executing Packages in SSMS
    Executing Packages from the Command Line
    Deployment Model Differences

  • Module 11: Installing and Administering SSIS

  • Installing SSIS
    Upgrading SSIS
    Managing the SSIS Catalog
    Viewing Built-in SSIS Reports
    Managing SSIS Logging and Operation Histories
    Automating Package Execution

  • Module 12: Securing the SSIS Catalog

  • Principals
    Grantable Permissions
    Granting Permissions
    Configuring Proxy Accounts


The primary audience for this course is database professionals who need to fulfil a Business Intelligence Developer role. They will need to focus on hands-on work creating BI solutions including Data Warehouse implementation, ETL, and data cleansing.




Working knowledge of T-SQL and SQL Server Agent jobs is helpful, but not required. Basic knowledge of the Microsoft Windows operating system and its core functionality. Working knowledge of relational databases. Some experience with database design.


Length: 5.0 days (40 hours)


Not Your Location? Change

Course Schedule:

To request a custom delivery, please chat with an expert.