SQL Server Integration Services (W8-20767)

This course provides an in-depth exploration of SQL Server Integration Services (SSIS), a powerful platform for building enterprise-level data integration and transformation solutions. Designed for data professionals, this course covers essential SSIS concepts and practical skills, from basic package creation to advanced data flow management, deployment, and security.

The course begins with an SSIS Overview, introducing the Import/Export Wizard for quick data transfers, discussing common import concerns, and teaching how to ensure data quality during import/export operations. This section sets the foundation for understanding the basic functionalities of SSIS.

Next, in the Working with Solutions and Projects module, you’ll work with SQL Server Data Tools (SSDT) and learn about solutions, projects, and the Visual Studio interface. This section helps you navigate and utilize the development environment effectively, preparing you to build and manage SSIS projects.

The Basic Control Flow section focuses on the fundamentals of working with tasks, understanding precedence constraints, and annotating packages. You’ll also learn how to group tasks, configure package and task properties, and set up connection managers, enabling you to design efficient and well-organized SSIS packages.

In the Common Tasks module, you’ll explore various tasks, including the Analysis Services Processing Task, Execute Package Task, FTP Task, and more. This section provides hands-on experience with commonly used tasks, enhancing your ability to handle different data integration scenarios with SSIS.

The Data Flow Sources and Destinations section introduces the Data Flow Task and SSIS Toolbox. You’ll learn to work with data sources and destinations, configuring them to handle data extraction, transformation, and loading processes, which are crucial skills for building effective ETL solutions.

Moving on to Data Flow Transformations, you’ll learn about different transformations available in SSIS and how to configure them. This section covers essential transformations, helping you to manipulate and process data as it flows through your packages.

The Making Packages Dynamic module covers techniques for making SSIS packages dynamic, including the use of package and project parameters, variables, SQL parameters, and expressions in tasks and connection managers. You’ll learn how to adapt your packages to changing data environments, enhancing their flexibility and robustness.

In the Containers section, you’ll explore the use of Sequence Containers, For Loop Containers, and Foreach Loop Containers. These containers help control task execution flow, enabling you to build complex and dynamic control structures within your SSIS packages.

The Troubleshooting and Package Reliability module teaches you how to handle errors and improve package reliability. You’ll learn about MaximumErrorCount, breakpoints, error row redirection, logging, event handlers, checkpoints, and transactions, equipping you with the skills to debug and optimize your SSIS packages effectively.

Next, in Deploying to the SSIS Catalog, you’ll learn about the SSIS Catalog, deploying projects, working with environments, and executing packages via SQL Server Management Studio (SSMS) and command line. This module covers deployment models and helps you understand the differences between various deployment options.

The Installing and Administering SSIS section provides guidance on installing and upgrading SSIS, managing the SSIS Catalog, viewing built-in SSIS reports, and automating package execution. You’ll gain skills in maintaining and administering SSIS environments, ensuring smooth and efficient operation of your data integration solutions.

The course concludes with Securing the SSIS Catalog, focusing on security best practices. You’ll learn about principals, securables, grantable permissions, configuring proxy accounts, and managing permissions to protect your SSIS environment from unauthorized access.

By the end of this course, you will have developed a comprehensive skill set in SSIS, enabling you to design, implement, troubleshoot, and deploy robust data integration solutions. You’ll be well-equipped to handle various data integration challenges and optimize data workflows in your organization.