Accelerated SQL Server Integration Services

In this course you are going to learn how to use Microsoft SQL Server 2025 Integration Services to automate a variety of data management operations, such as data extraction, transformation and loading, file management, migrating data between data systems, etc. You are also going to gain first-hand experience in Integration Services solution development, deployment, configuration, administration, execution, and monitoring.

This 4-day instructor-led training course, developed in-house by ESQLENCE (formerly known as Lucient) focuses on developing and managing automated data management processes in an enterprise. The course is centered around a typical data warehouse management solution implementing the ETL (Extract-Transform-Load) data management concept, where data is extracted from one or more sources, transformed, cleansed, and validated, to finally be loaded into one or more destinations of a data warehouse (or a data mart) for use in Business Intelligence operations.

In the hands-on labs at the end of each module, you are going to learn first-hand how to automate data management operations using Integration Services, as well as how to develop efficient and scalable data warehouse management solutions – either on your own, or as part of a development team.

Prerequisites

It is recommended that the students are familiar with the Microsoft Windows operating system, its components and services, and the Windows security model.

Basic experience with Microsoft SQL Server, working with databases and data objects, is recommended but not required.

The students should have at least an understanding of business intelligence and data integration at conceptual level.

No prior experience with SQL Server Integration Services is required.

Target audience

This course is intended for database professionals in charge of existing ETL or data warehousing solutions, as well as those who are just starting with ETL or data warehousing. The course helps junior developers gain their first experience working with Integration Services, provides seasoned developers with new insights and opportunities to improve their knowledge, and offers development team leads new perspectives on the development of efficient and scalable data management solutions.

The target audience for this session are IT professionals, database administrators, and developers, interested in data integration, data warehouse management, and the delivery of Integration Services solutions from development to production.

Section A

Overview of Data Warehousing and SQL Server Integration Services

Module 01 – SSIS Overview and Core Features

• Introduction to business intelligence
• Microsoft tools for BI
• Introduction to data integration
• SSIS features overview

Lab 01 – SSIS Overview and Core Features

Exercise 1 – Using the Import and Export Wizard

Exercise 2 – Creating a New SSIS Project

Exercise 3 – Modifying and Executing an SSIS Package in Debug Mode

Module 02 – Data Warehousing

• Dimensional modeling
• Optimizing a dimensional database
• Data preparation for advanced analytics

Lab 02 – Preparing a Data Warehouse

Exercise 1 – Creating Schemas and Dimensions

Exercise 2 – Creating the Fact Table

Exercise 3 – Populating the Date Dimension

Module 03 – SSIS Control Flow Objects and Features

• What is Control Flow
• Control Flow Concepts
• Control Flow Objects – Tasks, Constraints, Containers
• Control Flow Features – Parameters and Variables, SSIS Expression Language

Lab 03 – Using the Control Flow to Orchestrate SSIS Execution

Exercise 1 – Create an SSIS Package to Automate a Simple Data Movement

Exercise 2 – Determining Sequences and Parallelism in SSIS Package Design

Module 04 – Extracting, Transforming, and Loading Data using SSIS Data Flows

• The SSIS Data Flow Task
• Data Flows and Data Paths
• Data Connections and Connection Managers
• Data Flow Components

Lab 04 – Using Data Flows to Move Data in SSIS

Exercise 1 – Merging Multiple Sources into a Single Destination

Exercise 2 – Managing New and Modified Rows in a Data Flow

Section B

Application of SSIS to Automate Common Operations

Module 05 – Working with Files, Importing and Exporting File Data

• Using SSIS to Automate File System Maintenance
• Extracting Data from Files
• Loading Data into Files
• Excel Considerations

Lab 05 – Working with Files in SSIS

Exercise 1 – Extracting Data from Flat Files

Exercise 2 – Using the Foreach Loop Container to Process Multiple Files

Module 06 – Optimizing Data Extraction and Data Loading

• Data Extraction Optimization Essentials
• Determining the “Delta”
• Change Tracking
• Data Loading Optimization Essentials

Lab 06 – Using Change Tracking in SSIS Data Flows

Exercise 1 – Preparing the Source and the Staging Database

Exercise 2 – Creating the Data Warehouse Maintenance Package

Exercise 3 – Performing Data Maintenance

Module 07 – Data Quality and Cleansing

• Data Quality
• Data Profiling
• Fuzzy Transformations

Lab 07 – Data Quality

Exercise 1 – Profiling Data in SSIS

Exercise 2 – Cleansing Data using the Fuzzy Lookup Transformation

Module 08 – Advanced Enterprise Information Management

• Script Task and Script Component
• Text Mining
• Advanced Analysis and SSIS

Lab 08 – Validating Data against Regular Expressions

Exercise 1 – Using the Script Task to Process the Data Profiling Results

Exercise 2 – Validate Email Addresses

Section C

Application of SSIS to Automate Data Warehouse Management Operations

Module 09 – Dimension ETL with SSIS

• Dimension ETL Theory
• SQL Server Temporal Tables
• SSIS Slowly Changing Dimension Wizard
• Custom Dimension ETL

Lab 09 – Dimension ETL with SSIS

Exercise 1 – Designing the Initial Load of Dimensions and Staging Tables

Exercise 2 – Using the SCD Wizard

Exercise 3 – Designing a Custom SCD Solution

Module 10 – Fact ETL with SSIS

• Fact Table ETL Theory
• Data Preparation for Fact Tables
• Advanced Concepts

Lab 10 – Fact ETL with SSIS

Exercise 1 – Staging the Fact Data

Exercise 2 – Using T-SQL to Identify Dimension Keys

Exercise 3 – Using SSIS to Identify Dimension Keys

Module 11 – Introducing Personal Enterprise Information Management

• Power Pivot
• Power Query

Lab 11 – Power Query

Exercise 1 – Extracting Data with Power Query

Exercise 2 – Combining the Results of Two Queries

Module 12 – Processing SSAS Objects in SSIS

• SSAS Tabular and Multidimensional
• Processing Methods in SSIS
• Dynamic Processing and Partition Creation

Lab 12 – Analysis Services Processing

Exercise 1 – Deploying and Using an SSAS Cube

Exercise 2 – Processing an SSAS Cube

Section D

Deployment, Configuration, Administration, Execution, and Monitoring

Module 13 – Project Deployment

• Project Deployment Model
• Deployment to the SSISDB Catalog
• Administration, Security, Configuration
• Execution
• Monitoring
• The Master Package Concept

Lab 13 – SSIS Project Deployment, Configuration, Execution, and Monitoring

Exercise 1 – Preparing the SSISDB Catalog, Deploying SSIS Projects, and Creating SSISDB Environments

Exercise 2 – Configuring, Validating and Executing SSIS Packages

Exercise 3 – Scheduling the Execution of an SSIS Package

Module 14 – Package Deployment

• Package Deployment Model
• Deployment to the SSIS package store
• Administration, Configuration, and Security
• Execution
• Monitoring and Logging

Lab 14 – SSIS Package Deployment, Configuration, Execution, and Monitoring

Exercise 1 – Organizing the msdb SSIS package store, and Configuring SSIS Packages

Exercise 2 – Implementing Custom Logging, and Deploying SSIS Packages

Exercise 3 – Validating and Executing SSIS Packages, and Monitoring Their Use

Section E

Efficiency and Scalability

Module 15 – Transactions and Restartability

• Using Breakpoints in the SSIS Designer
• Implementing Transactions in SSIS
• Using SQL Server Database Snapshots
• Restartability of SSIS Packages
• Responding to Events

Lab 15 – Using Transactions and Checkpoints in SSIS Packages

Exercise 1 – Using Transactions in SSIS

Exercise 2 – Using Checkpoints in SSIS

Module 16 – Optimization and Scalability

• Leveraging SSIS and Transact-SQL
• Data Flow Engine Internals
• SSIS Optimization Techniques
• SSIS Performance Troubleshooting

Lab 16 – Optimizing SSIS Packages for Performance

Exercise 1 – Using Memory-Optimized Staging Tables

Exercise 2 – Optimizing SSIS Packages

Upon completion of this course, the student is going to be able to:

• Examine and understand existing SSIS projects and packages.
• Design and develop new SSIS projects and packages.
• Automate file and data management operations.
• Understand and implement ETL concepts in data warehouse maintenance of star-schema (as well as snowflake-schema) data stores, including dimension and fact table ETL.
• Automate data transfer into SQL Server Analysis Services databases.
• Optimize data extraction, transformation, and loading.
• Configure, deploy, administer, execute, and monitor the execution of, SSIS packages.