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

Inquire now

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.

 

Inquire now

Best selling courses

Duration: 5 days – 35 hrs   Overview The “SOC Network and Threat Detection and Analysis” training course is designed to equip Security Operations Center (SOC) analysts and IT security professionals with the skills and knowledge required to detect, analyze, and respond to network threats effectively. This comprehensive course covers essential topics such as threat...

Duration 1 day – 7 hrs   Overview   This 1-day training builds upon basic warehouse operations knowledge and introduces key logistics concepts involved in the movement and coordination of goods—especially wet and dry food items—within and outside the warehouse. Participants will explore transport logistics, inbound and outbound coordination, documentation practices, and cold chain considerations,...

Duration 2 days – 14 hrs   Overview   This hands-on course provides an introduction to Splunk, a powerful platform for searching, monitoring, and analyzing machine-generated data. The training focuses on how developers and QA professionals can leverage Splunk to gain insights from logs and metrics, improve application observability, detect anomalies, and support test validation....

Duration 3 days – 21 hrs   Overview.   This course is designed for fresh graduates aspiring to build a career in Data Science. It introduces the fundamentals of data science, focusing on data analysis, visualization, and basic machine learning concepts using Python. The course provides hands-on practice with real-world datasets, equipping participants with the...

Duration 5 days – 35 hrs   Overview   The Microsoft Certified: Azure Administrator Associate (AZ-104) Training Course is designed for IT professionals who want to develop expertise in managing Microsoft Azure environments. This course provides hands-on training on deploying, managing, and monitoring Azure services, including virtual machines, networking, storage, identity, and security.   Objectives...

ARTIFICIAL INTELLIGENCE / MACHINE LEARNING / DEEP LEARNING

Capstone Workshop: Build Your Own GenAI Toolkit TECH AI

GenAI Toolkit Training is a practical, hands-on capstone workshop designed to help participants bring their generative AI knowledge together into a personalized toolkit of AI tools, prompts, workflows, and practical solutions. Rather than simply learning individual AI platforms, participants will learn how to select and combine the right tools to address real-world technical and professional...

In this course, you will gain practical foundation level training that enables immediate and effective participation in big data and other analytics projects. Learn ways of storing data that allow for efficient processing and analysis, and gain the skills you need to store, manage, process, and analyze massive amounts of unstructured data to create an appropriate data lake.

Advanced Laravel Engineering Scalable Architecture, Performance, Testing, Queues & DevOps equips developers with the skills to design scalable Laravel applications, optimize performance, implement automated testing, manage queues, and integrate DevOps practices for reliable, production-ready deployments.   Duration 5 Days – 35 hrs   Overview This advanced-level bootcamp is designed for teams building large-scale or mission-critical...

// BG EARTH WHEN NOT PLAYING

We use cookies on our website to personalize your experience by storing your preferences and recognizing repeat visits. By clicking “Accept”, you agree to the use of all cookies. You can also select “Cookie Settings” to adjust your preferences and provide more specific consent. Cookie Policy