Basic SQL Server Integration Services (SSIS): Developing a SQL Data Warehouse Overview

This introductory course provides the knowledge and skills needed to develop a SQL Server data warehouse using SQL Server Integration Services (SSIS). Participants learn data warehouse fundamentals and how to build extract, transform, and load (ETL) processes that integrate data from SQL Server databases and flat files. The course covers SSIS package development, data transformation, dimension and fact table loading, error handling, deployment, and scheduled execution.

 

Duration 5 Days – 35 hrs.

 

Objectives

• Explain data warehouse concepts and the role of SSIS in ETL processing.
• Identify fact tables, dimension tables, keys, and data grain.
• Create SSIS projects, packages, and database connections.
• Build control flows and data flows to manage data integration tasks.
• Extract, cleanse, transform, and load data from common sources.
• Load dimension and fact tables in a SQL Server data warehouse.
• Implement basic incremental loading and slowly changing dimension patterns.
• Configure package parameters, logging, and error handling.
• Deploy, execute, monitor, and schedule SSIS packages.

 

Target Audience 

• Database developers beginning to work with SSIS.
• SQL developers responsible for data integration.
• Business intelligence developers and analysts with SQL knowledge.
• Junior data engineers and ETL developers.
• Database administrators supporting data warehouse processes.
• IT professionals transitioning into data warehousing.

 

Prerequisites

• Basic knowledge of relational databases, tables, primary keys, and foreign keys.
• Ability to write SQL queries using SELECT, JOIN, WHERE, and GROUP BY.
• Familiarity with INSERT, UPDATE, and DELETE statements.
• Basic experience using SQL Server Management Studio (SSMS).
• General understanding of business data and reporting requirements.
• No previous SSIS or data warehouse development experience is required.

Course Outline 

Day 1: Data Warehouse and SSIS Fundamentals

Module 1: Introduction to Data Warehousing

• Purpose and business uses of a data warehouse
• Differences between transactional and analytical databases
• ETL concepts and the data integration lifecycle
• Source systems, staging areas, and warehouse layers
• Introduction to data quality and historical data

 Module 2: Dimensional Modeling Basics

• Star schemas and an introduction to snowflake schemas
• Fact tables, dimension tables, and table grain
• Business keys and surrogate keys
• Measures and dimension attributes
• Date dimensions and relationships between tables

 Module 3: Getting Started with SSIS

• SSIS architecture and development tools
• Creating an Integration Services project
• Navigating SSIS Designer
• Packages, connection managers, control flow, and data flow
• Building and executing a basic data import package

 

Day 2: Building SSIS Data Integration Packages

Module 4: Managing Package Control Flow

• Tasks, containers, and precedence constraints
• Execute SQL Task and Data Flow Task
• Sequence Container and Foreach Loop Container
• Variables, parameters, and expressions
• Processing multiple source files

 Module 5: Extracting and Loading Data

• Connecting to SQL Server and flat files
• Configuring sources and destinations
• Understanding metadata and data types
• Mapping source columns to destination columns
• Loading data into staging tables

 Module 6: Transforming and Cleansing Data

• Data Conversion and Derived Column transformations
• Conditional Split and Lookup transformations
• Aggregate and Union All transformations
• Handling null values and inconsistent formats
• Identifying duplicates and invalid records

 

 Day 3: Loading the SQL Data Warehouse

Module 7: Preparing the Warehouse Loading Process

• Creating staging, dimension, and fact tables
• Defining source-to-target mappings
• Establishing the order of warehouse loads
• Preparing and clearing staging data
• Tracking load batches and record counts

 Module 8: Loading Dimension Tables

• Initial dimension loading
• Matching records using business keys
• Generating and retrieving surrogate keys
• Type 1 changes: overwriting attribute values
• Type 2 changes: preserving attribute history
• Handling unknown dimension members

 Module 9: Loading Fact Tables

• Confirming fact table grain
• Looking up dimension surrogate keys
• Mapping measures and date keys
• Handling missing dimension references
• Preventing duplicate fact records
• Reconciling source and warehouse totals

 

Day 4: Incremental Loads and Package Reliability

Module 10: Implementing Basic Incremental Loading

• Full loads compared with incremental loads
• Identifying new and changed records
• Using modification timestamps and load watermarks
• Separating inserts from updates
• Updating watermarks after successful loads
• Recognizing source deletion handling requirements

 Module 11: Error Handling and Troubleshooting

• Common connection, metadata, and conversion errors
• Configuring error outputs
• Redirecting rejected records
• Using breakpoints and data viewers
• Introducing event handlers and package logging
• Diagnosing failed package executions

 Module 12: Building Reliable ETL Processes

• Organizing packages and task dependencies
• Using a parent package to coordinate warehouse loads
• Designing packages for safe reruns
• Recording load status and failure details
• Understanding restart and recovery considerations
• Applying basic performance practices

 

Day 5: Deployment and Operation

Module 13: Deploying SSIS Projects

• Introduction to the project deployment model
• Understanding the SSISDB catalog
• Building and deploying an SSIS project
• Configuring project and package parameters
• Using environments and environment references
• Managing sensitive connection information

 Module 14: Executing, Scheduling, and Monitoring

• Executing packages from SSMS
• Scheduling package execution with SQL Server Agent
• Understanding execution accounts and permissions
• Reviewing execution reports and log messages
• Investigating scheduled execution failures
• Maintaining operational documentation

 Module 15: Integrating the Complete Warehouse Solution

• Connecting source extraction, staging, and warehouse loads
• Coordinating dimension and fact processing
• Applying incremental loading across the solution
• Incorporating error handling and execution logging
• Deploying the integrated warehouse workflow
• Reviewing data consistency and operational readiness