Course Overview
The Introduction to SQL Server Integration Services (SSIS) training teaches attendees how to use SSIS to build high-performance ETL (Extract, Transform, and Load) and workflow packages and projects.
We also offer Developing and Administering SQL Server Integration Services (SSIS), a 5-day class that adds coverage of administrative topics, such as project deployment, managing packages in an SSIS catalog, securing the SSIS catalog, and more.
Note: This class is appropriate for SQL Server 2012 through 2019.
Key Learning Areas
- 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
- Incrementally load data into a data warehouse
- Deploy and execute packages
- Successfully troubleshoot packages
- Configure package reliability
Course Outline
SSIS Overview
- SSIS Overview
- Import/Export Wizard
- Exporting Data with the Wizard
- Common Import Concerns
- Quality Checking Imported/Exported Data
Working with Solutions and Projects
- Working with SQL Server Data Tools
- Understanding Solutions and Projects
- Working with the Visual Studio Interface
Basic Control Flow
- Introduction to the Control Flow
- Working with Tasks
- Understanding Precedence Constraints
- Annotating Packages
- Grouping Tasks
- Package and Task Properties
- Connection Managers
- Favorite Tasks
Common Tasks
- SSIS Tasks in the Common Section
- Analysis Services Processing
- Bulk Insert Task
- Data Profiling Task
- Execute Package Task
- Execute Process Task
- Expression Task
- File System Task
- FTP Task
- Hadoop Tasks
- Script Task Introduction
- Send Mail Task
- Web Service Task
- XML Task
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
- Chapter 5 Lab
- Answers to Exercises
Data Flow Transformations
- Transformations Overview
- Transformations
- Configuring Transformations
Making Packages Dynamic
- Features for Making Packages Dynamic
- Package Parameters
- Project Parameters
- Variables
- SQL Parameters
- Expressions in Tasks
- Expressions in Connection Managers
- After Deployment
- How it all fits together
Containers
- Sequence Containers
- For Loop Containers
- Foreach Loop Containers
Troubleshooting and Package Reliability
- Understanding MaximumErrorCount
- Breakpoints
- Redirecting Error Rows
- Logging
- Event Handlers
- Using Checkpoints
- Transactions
Deploying to the SSIS Catalog
- SSIS Deployment Overview
- The SSIS Catalog
- Deploying Projects
- Working with Environments
- Executing Packages in SSMS
- Executing packages from the command line
- Deployment Model Differences
Prerequisites
Students should be comfortable with SQL Server Management Studio and writing basic queries.