SQL Server Integration Services (SSIS) – An Introduction Course Syllabus

This beginner-friendly course provides a comprehensive introduction to SQL Server Integration Services (SSIS), guiding you through the essential skills needed to build, debug, and deploy ETL solutions. With approximately 5 hours of hands-on learning, you'll progress from environment setup to deploying real-world data integration workflows. Each module combines clear demonstrations with practical labs, ensuring you gain confidence in using SSIS for data extraction, transformation, and loading tasks.

Module 1: Introduction to SSIS & Environment Setup

Estimated time: 0.5 hours

• Install SQL Server Data Tools (SSDT) and SSIS extensions
• Create your first SSIS project and explore the Solution Explorer
• Understand SSIS package structure and deployment models

Module 2: Control Flow Fundamentals

Estimated time: 0.75 hours

• Add and configure tasks such as Execute SQL, File System, and Script Task
• Sequence tasks using precedence constraints and containers
• Leverage Sequence and ForEach Loop containers for iterative processing

Module 3: Data Flow Basics

Estimated time: 1 hour

• Design Data Flow tasks with sources, transformations, and destinations
• Use common transformations: Lookup, Derived Column, and Data Conversion
• Optimize buffer settings and transformation order for performance

Module 4: Connection Managers & Data Sources

Estimated time: 0.75 hours

• Configure OLE DB, ADO.NET, Flat File, and Excel connection managers
• Set up parameters for dynamic connection strings
• Secure sensitive connection information via project parameters and environments

Module 5: Variables, Parameters & Expressions

Estimated time: 0.75 hours

• Define and scope variables at package, container, and task levels
• Create parameters for project-wide configuration
• Use expressions to build dynamic SQL statements and file paths

Module 6: Event Handlers, Logging & Debugging

Estimated time: 0.75 hours

• Implement OnError, OnWarning, and OnPostExecute event handlers
• Configure SSIS logging to capture runtime details to text files or SQL tables
• Debug packages using breakpoints, data viewers, and progress windows

Module 7: Deployment, Scheduling & Management

Estimated time: 0.5 hours

• Deploy packages to SSIS Catalog and configure environments
• Schedule SSIS jobs via SQL Server Agent
• Monitor execution reports, handle versioning, and roll back deployments

Module 8: Hands-On Lab & Real-World Scenarios

Estimated time: 1 hour

• Build an end-to-end ETL pipeline importing sales data from flat files to SQL tables
• Implement error redirection, audit logging, and package parameterization
• Review best practices for maintainability and performance tuning

Prerequisites

• Familiarity with basic database concepts and SQL
• Basic understanding of data workflows and ETL processes
• Access to a Windows machine for installing SSDT and SQL Server (free editions acceptable)

What You'll Be Able to Do After

• Understand the role of SQL Server Integration Services (SSIS) in data integration workflows
• Install and configure SSIS projects and packages within Visual Studio
• Design and implement Control Flow tasks for workflow orchestration
• Build Data Flow pipelines to extract, transform, and load (ETL) data
• Configure Connection Managers, Variables, Parameters, and Expressions for dynamic, maintainable SSIS packages