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

Inquire now

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

 

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