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

This course covers the design, development, and operation of a SQL Server data warehouse using SQL Server Integration Services (SSIS). Participants learn to design dimensional data models, integrate data from multiple sources, and develop extract, transform, and load (ETL) processes that populate fact and dimension tables.

The course progresses from data warehousing fundamentals to incremental loading, data quality controls, error handling, deployment, monitoring, and performance optimization. It focuses on SQL Server relational data warehouses and SSIS package development.

Duration 5 Days – 35 hrs.

Objectives

• Explain data warehouse architecture and the role of SSIS in data integration.
• Design fact tables, dimension tables, and staging structures.
• Create SSIS projects, packages, connection managers, and control flows.
• Develop data flows to extract, cleanse, transform, and load data.
• Implement full and incremental loading strategies.
• Manage historical dimension changes using slowly changing dimension patterns.
• Apply data validation, error handling, logging, and recovery controls.
• Deploy and configure SSIS projects using the SSISDB catalog.
• Schedule and monitor package execution.
• Identify and address common ETL performance bottlenecks.

 

Target Audience 

• SQL developers and database developers.
• ETL developers and data integration specialists.
• Business intelligence developers.
• Data engineers developing SQL Server data warehouse solutions.
• Database administrators supporting data integration workloads.
• Data analysts with SQL experience who are moving into data warehouse development.

 

Prerequisites 

• Working knowledge of relational database concepts, including tables, keys, relationships, and constraints.
• Ability to write T-SQL queries using joins, aggregations, subqueries, and data modification statements.
• Familiarity with SQL Server Management Studio.
• Basic understanding of views and stored procedures.
• General familiarity with business reporting requirements is helpful.
• No previous SSIS development experience is required.

 

Course Outline 

Day 1: Data Warehouse Foundations and SSIS Fundamentals

Module 1: Introduction to SQL Server Data Warehousing

• Data warehouse purpose and business requirements.
• Operational databases versus analytical databases.
• ETL and ELT concepts.
• Source systems, staging areas, data warehouses, and data marts.
• Data integration requirements and source-to-target mappings.

 Module 2: Dimensional Modeling

• Star and snowflake schemas.
• Defining the grain of a fact table.
• Fact tables, measures, and dimension tables.
• Natural keys and surrogate keys.
• Conformed dimensions and date dimensions.
• Translating reporting requirements into a dimensional model.

 Module 3: SSIS Architecture and Development Environment

• SSIS components and package execution concepts.
• Visual Studio with the SQL Server Integration Services Projects extension.
• Creating solutions, projects, and packages.
• Connection managers and data source connectivity.
• Control flow and data flow fundamentals.
• Package properties and target server compatibility.

  

Day 2: Developing SSIS Control Flows and Data Flows

Module 4: Building Control Flows

• Tasks, containers, and precedence constraints.
• Execute SQL Task and Data Flow Task.
• Sequence, For Loop, and Foreach Loop containers.
• File processing and workflow dependencies.
• Variables, parameters, and expressions.
• Organizing reusable package workflows.

 Module 5: Extracting and Transforming Data

• Relational database and flat file sources.
• Data types, metadata, and conversions.
• Derived Column and Conditional Split transformations.
• Lookup transformations and reference data matching.
• Aggregate, Sort, Merge Join, and Union All transformations.
• Handling null values and inconsistent source formats.

 Module 6: Loading Staging Tables

• Designing staging tables.
• Loading data from multiple sources.
• Configuring destinations and column mappings.
• Fast-load options and batch settings.
• Recording batch identifiers and source metadata.
• Reconciling source and staging row counts.

  

Day 3: Loading Dimensions and Fact Tables

Module 7: Loading Dimension Tables

• Initial dimension loading.
• Generating and resolving surrogate keys.
• Detecting new and changed records.
• Slowly changing dimension Types 1 and 2.
• Managing effective dates and current-record indicators.
• Handling unknown and inferred dimension members.

 Module 8: Loading Fact Tables

• Maintaining consistent fact table grain.
• Resolving dimension keys through lookups.
• Loading transaction and snapshot fact tables.
• Handling late-arriving facts and dimensions.
• Preventing duplicate fact records.
• Validating measures and referential integrity.

 Module 9: Incremental Loading Strategies

• Full refresh versus incremental loading.
• Watermark-based extraction.
• Introduction to SQL Server Change Tracking and Change Data Capture.
• Processing inserts, updates, and deletions.
• Managing load dependencies and control tables.
• Designing restartable loads that avoid duplicate processing.

  

Day 4: Reliability, Data Quality, and Performance

Module 10: Data Quality and Error Handling

• Defining validation and data quality rules.
• Detecting missing, invalid, and duplicate data.
• Configuring error outputs and redirecting rejected rows.
• Capturing error details and source record identifiers.
• Using event handlers.
• Reprocessing corrected records.

 Module 11: Logging, Auditing, and Recovery

• SSIS logging and execution diagnostics.
• Batch audit tables and load status tracking.
• Recording row counts and reconciliation totals.
• Transactions and checkpoints.
• Failure recovery and restart considerations.
• Troubleshooting validation and runtime errors.

 Module 12: ETL Performance Optimization

• Identifying source, transformation, and destination bottlenecks.
• Reducing unnecessary data movement.
• Understanding blocking and nonblocking transformations.
• Configuring lookup caching.
• Managing buffers and execution parallelism.
• Optimizing bulk loads, indexes, and staging queries.

  

Day 5: Deployment and Operations

Module 13: Deploying and Configuring SSIS Projects

• Project deployment model.
• Creating and organizing the SSISDB catalog.
• Building and deploying project deployment files.
• Configuring project and package parameters.
• Managing environments and environment references.
• Protecting sensitive values and configuring access permissions.

 Module 14: Scheduling and Monitoring ETL Workloads

• Scheduling package execution with SQL Server Agent.
• Execution accounts, credentials, and proxies.
• Configuring job steps and workflow dependencies.
• Reviewing catalog reports and execution messages.
• Configuring failure notifications.
• Managing execution log retention.

 Module 15: Integrating the Data Warehouse Solution

• Connecting staging, dimension, and fact loading workflows.
• Coordinating package execution through a master package.
• Applying environment-specific configurations.
• Verifying initial and incremental load behavior.
• Documenting mappings, dependencies, and operational procedures.
• Preparing the solution for production support.