SQL Server Integration Services (W8-20767)
Learn how to build, optimize, deploy, troubleshoot, and secure SQL Server Integration Services packages for reliable ETL and business intelligence workflows.
Register or Request Training
- Private class for your team
- Live expert instructor
- Online or on‑location
- Customizable agenda
- Proposal responses same day as request
Course Overview
Learn to design, configure, and secure SQL Server Integration Services (SSIS) packages for efficient extract, transform, and load (ETL) workflows. You will connect to multiple data sources, transform and cleanse data, and load results into SQL Server and other destinations.
The course covers control flows, precedence constraints, dynamic package configuration, troubleshooting, deployment, automation, and administration of the SSIS Catalog. It is designed for database professionals and BI developers who build data integration solutions, support data warehouses, or manage large-scale Microsoft SQL Server projects.
Note: This course is similar to retired Course 20767-C: Implementing a SQL Data Warehouse, but it focuses more specifically on building SSIS packages than on complete data warehouse design and implementation.
Course Benefits
- Create SSIS packages that extract, transform, and load data.
- Use containers to control repetitive tasks and transactions efficiently.
- Configure packages to adapt dynamically to environment changes.
- Cleanse data using Data Quality Services to improve business intelligence accuracy.
- Troubleshoot and debug packages for reliable execution.
- Create, manage, and secure the SSIS Catalog.
- Deploy, configure, and schedule packages for automated execution.
Delivery Methods
Delivered for your team at your site or online.
Course Outline
- SSIS Overview
- Import/Export Wizard and Exporting Data
- Common Import Concerns and Quality Checking Data
- Working with Solutions and Projects
- SQL Server Data Tools Overview
- Understanding Solutions, Projects, and the Visual Studio Interface
- Basic Control Flow
- Working with Tasks, Precedence Constraints, and Connection Managers
- Annotating, Grouping, and Managing Package and Task Properties
- Commonly Used Tasks
- Common Tasks
- Analysis Services Processing and Data Profiling
- Execute Package, Execute Process, and Expression Tasks
- File System, FTP, and Hadoop Tasks
- Script Task Basics, Send Mail, Web Service, and XML Tasks
- Data Flow Sources, Destinations, and Transformations
- Data Flow Task and SSIS Toolbox Overview
- Working with Data Sources and Destinations, Including Databases, Flat Files, and Excel
- Configuring Transformations to Cleanse and Shape Data
- Making Packages Dynamic
- Package, Project, and SQL Parameters and Variables
- Using Expressions in Tasks and Connection Managers
- Post-Deployment Dynamic Configurations
- Containers
- Sequence Containers, For Loops, and Foreach Loops
- Troubleshooting and Package Reliability
- Error Handling with Redirected Rows, Logging, and Event Handlers
- Using Breakpoints, Checkpoints, and Transactions
- Managing MaximumErrorCount for Stability
- Deploying to the SSIS Catalog
- Understanding the SSIS Catalog and Deployment Models
- Deploying Projects, Managing Environments, and Executing Packages through SSMS and the Command Line
- Installing and Administering SSIS
- Installing and Upgrading SSIS
- Managing the Catalog, Built-in Reports, Logging, and Operation Histories
- Automating Package Execution
- Securing the SSIS Catalog
- Principals, Securables, and Permissions Management
- Granting Permissions and Configuring Proxy Accounts
Class Materials
Each student receives a comprehensive set of materials, including course notes and all class examples.
Class Prerequisites
Experience in the following is required for this Microsoft SQL Server class:
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
Experience in the following would be useful for this Microsoft SQL Server class:
- Working knowledge of relational databases.
- Some experience with database design.
- Working knowledge of T-SQL and SQL Server Agent jobs is helpful but not required.
Prerequisite Courses
Courses that can help you meet these prerequisites:
Have questions about this course?
We can help with curriculum details, delivery options, pricing, or anything else. Reach out and we’ll point you in the right direction.