Databases · DBC-58

SQL Server Integration Services (SSIS) for ETL

SQL Server Integration Services (SSIS) for ETL is a hands-on course in building ETL on SQL Server 2025 with SSIS, from control flow, data flow and transformations to incremental loads, slowly changing dimensions, SSISDB and SQL Server Agent. It suits database developers, BI developers and data engineers, and you leave able to load a star schema data warehouse.

Updated 30 Oct 2026

From 8,010 THB / person 8,900 −10% excl. VAT 7% · group rates available

Corporate training quote Join the waiting list

PDFDownload the course outline

Duration18 hours · 3 days
FormatOnsite / live online
Next roundOn request
CertificateIncluded

Course overview

Many organisations still have data spread across ERP systems, application databases, Excel files and CSV files that must be combined every day for reporting and the data warehouse. This work often starts as scripts or manual copying, which break easily, cannot be audited and slow down as data grows. SQL Server Integration Services (SSIS) remains the main ETL tool that ships with SQL Server and is supported in SQL Server 2025, which makes it a cost-effective choice for organisations already running SQL Server.

This course builds on Microsoft SQL Server 2025 Database Queries and moves on to building production-ready ETL with SSIS. Learners install SQL Server Integration Services Projects in Visual Studio, build control flows and data flows, use the most common transformations, match data with Lookup, make packages flexible with parameters and expressions, load only changed data and handle slowly changing dimensions. They then put error handling and logging in place, deploy to the SSIS Catalog, schedule with SQL Server Agent and tune performance, before finishing with a project that loads a star schema data warehouse end to end. (3 days, 6 hours per day, 18 hours in total, Intermediate level.)

What you’ll gain

Explain the role of SSIS in today's ETL work and what changed in SQL Server 2025
Build SSIS projects in Visual Studio with control flows, data flows and connection managers
Use common transformations such as Derived Column, Conditional Split, Lookup and Aggregate fluently
Make packages flexible with variables, parameters and expressions
Design incremental loads and handle slowly changing dimensions
Put in place error handling, event handlers and logging that can be audited
Deploy to the SSIS Catalog (SSISDB) and schedule with SQL Server Agent
Tune data flow performance and load a star schema data warehouse

Who this course is for

Database and BI developers who build data loads on SQL Server
Data engineers who maintain existing SSIS ETL or need to build new pipelines
DBAs who deploy, schedule and look after SSIS packages in production
Data analysts who need to combine data from several systems into a data warehouse
Anyone who already writes SQL and wants to move into data integration

Prerequisites

Able to write SQL queries such as SELECT, JOIN, GROUP BY, INSERT and UPDATE
Understand tables, primary keys and foreign keys in relational databases
Basic use of SQL Server Management Studio
A Windows laptop that can run SQL Server 2025 Developer Edition and Visual Studio, with permission to install software

Curriculum

Course Details

This course builds on Microsoft SQL Server 2025 Database Queries and moves on to building production-ready ETL with SSIS. It runs for 3 days, 6 hours per day (18 hours in total, 09:00-16:00), as lectures with labs built on a single sample sales database and data warehouse throughout. Intermediate level. Every lab runs on the learner's own machine with SQL Server 2025 Developer Edition and the free Visual Studio Community edition for development. The institute does not provide machines or licences. The course focuses on ETL with SSIS and only summarises where SSIS fits compared with Azure Data Factory and Microsoft Fabric. Learners take home a lab guide, SSIS project files, SQL scripts and the sample databases.

Day 1 SSIS Foundations, Control Flow and Data Flow

Section 1: SSIS in ETL Work in 2026

ETL versus ELT and the role of SSIS in data integration
Where SSIS fits compared with Azure Data Factory and Microsoft Fabric
What changed in SQL Server 2025: Microsoft SqlClient support in ADO.NET and the deprecated 32-bit mode
Components that were removed, such as CDC by Attunity and the Microsoft Connector for Oracle
SSIS architecture: packages, projects, the runtime and the catalog

Section 2: Lab: Setting Up the Tools and a First SSIS Project

Install SQL Server 2025 Developer Edition with Integration Services
Install Visual Studio and SQL Server Integration Services Projects 2022+
Create an SSIS project with the project deployment model and get to know the designer
Prepare the source database and target data warehouse used in every lab
Lab: a first package that copies data from a CSV file into a SQL Server table

Section 3: Lab: Control Flow and Connection Managers

Common tasks: Execute SQL Task, Data Flow Task and File System Task
Precedence constraints for success, failure, completion and expressions
Group work with the Sequence container and repeat it with the For Loop container
Project-level OLE DB, ADO.NET, Flat File and Excel connection managers
Lab: a package that clears staging tables, loads data and logs the run

Section 4: Data Flow: Sources, Destinations and Data Types

How the data flow pipeline and buffers work
Read data with the OLE DB, Flat File and Excel sources
SSIS data types versus SQL Server types, and converting with Data Conversion
Thai text, code pages and Unicode issues in text files
Lab: inspect data in flight with the data viewer and fix data type problems

Section 5: Lab: Common Transformations

Derived Column to calculate and clean data with expressions
Conditional Split to route rows by condition, and Multicast to send data down several paths
Union All, Sort and Aggregate to combine and summarise data
Row Count to capture row totals for checks
Lab: clean customer data and route rows that fail the rules to a review table

Day 2 Flexible Packages and Professional Loading

Section 6: Lab: Lookup and Merge Join

The Lookup transformation with full cache, partial cache and no cache
Handle rows that are not found with the no match output
The Cache Transform and Cache connection manager for repeated lookups
Merge Join compared with Lookup, and when to join in SQL instead
Lab: add product and customer surrogate keys to sales data

Section 7: Variables, Parameters and Expressions

Package variables and variable scope
Project and package parameters for values that differ between environments
The SSIS expression language and property expressions
Connection strings driven by parameter values
Lab: make a package switch server and folder without editing the package

Section 8: Lab: Processing Many Files with the Foreach Loop

The Foreach Loop container with the File and ADO enumerators
Read every file in a folder, then move processed files to an archive
Record the file name and load time in the data for later audit
An overview of the Script Task and Script Component for work standard components cannot do
Lab: load every branch sales file from an inbound folder

Section 9: Lab: Incremental Loads

Full loads versus incremental loads and their effect on time and resources
Watermarks from a modified-date column or the last loaded ID
Detect changed rows by comparison or with hash values
Load into staging and then MERGE through an Execute SQL Task
An overview of SQL Server Change Data Capture and Change Tracking

Section 10: Lab: Slowly Changing Dimensions

SCD Type 1 and Type 2, and choosing the right one for the business
The Slowly Changing Dimension wizard and its performance limits
Set-based SCD Type 2 with staging and MERGE
Handle effective dates, expiry dates and a current flag
Lab: load a customer dimension that keeps the history of branch moves

Day 3 Deployment, Operations, Tuning and Capstone

Section 11: Lab: Error Handling and Logging

Component error outputs: fail, ignore and redirect row
OnError and OnPostExecute event handlers
Keep failed rows for review along with the error code and column
SSISDB logging levels and a custom run log table
Lab: a package that keeps running on bad data and reports a summary every time

Section 12: Lab: Deploying to the SSIS Catalog (SSISDB)

Create the SSIS Catalog and its folders, projects and packages
Deploy a project from Visual Studio and from an .ispac file
Environments and environment variables for Dev, Test and Production
Run packages from SSMS and with T-SQL
Lab: deploy the project and bind an environment so values change per server

Section 13: Lab: Scheduling and Monitoring with SQL Server Agent

Create a SQL Server Agent job that runs a package from SSISDB
Schedules, multi-step jobs and alerts when a run fails
Credentials and proxy accounts for the right level of permissions
Monitor runs with the SSISDB reports and catalog views
Lab: schedule a nightly load job with failure alerts

Section 14: Tuning Data Flow Performance

Find bottlenecks by timing runs and with the performance data in SSISDB
Blocking and non-blocking transformations and their effect on memory
Tune DefaultBufferMaxRows, DefaultBufferSize and AutoAdjustBufferSize
Fast load, batch size and handling indexes during large loads
Lab: measure load time before and after tuning with several million rows

Section 15: Capstone: Loading a Star Schema Data Warehouse

Plan the order of work: staging, then dimensions, then facts
Build a master package that calls child packages with the Execute Package Task
Load the fact table with surrogate key lookups and row count checks
Deploy, schedule and present the work with a pre-production checklist

Schedule & training options

For individuals — public rounds

No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770

Join the waiting list Ask about rounds

For organisations — in-house / private

Tailor the content to your team’s tools and projects
Your dates, at your office or live online
Quotation with tax ID for procurement

Corporate training quote

Instructors

Sanitwong KamonpakornInstructor

Frequently asked questions

How does this course differ from Microsoft SQL Server 2025 Database Queries?

This course builds on Microsoft SQL Server 2025 Database Queries, which teaches SQL querying. This course puts that SQL to work building ETL with SSIS, from control flow, data flow and transformations to incremental loads and slowly changing dimensions, through to deploying to SSISDB, scheduling with SQL Server Agent and loading a star schema data warehouse.

Is SSIS still supported in SQL Server 2025?

Yes. SQL Server 2025 still includes Integration Services and adds Microsoft SqlClient support in the ADO.NET connection manager. Some features have been deprecated or removed, such as 32-bit mode, the legacy Integration Services Service, CDC by Attunity and the Microsoft Connector for Oracle, and the course summarises what this means for existing packages.

What machine and software do I need, and does the institute provide them?

The institute does not provide machines or licences. You bring your own 64-bit Windows laptop, ideally with 16 GB of RAM or more, with SQL Server 2025 Developer Edition, Visual Studio Community with SQL Server Integration Services Projects 2022+, and SQL Server Management Studio installed. All are free for development and learning, and an installation guide is sent before the training day.

Should I use SSIS or move to Azure Data Factory or Microsoft Fabric?

SSIS suits organisations running SQL Server on premises that want ETL they control themselves without extra cloud charges. Azure Data Factory and Microsoft Fabric suit work where data lives in the cloud or needs to scale with usage. The course summarises the selection criteria on the first day but does not teach those cloud tools in depth.

What level of experience do I need?

You should be able to write SQL queries such as SELECT, JOIN, GROUP BY, INSERT and UPDATE and use SQL Server Management Studio at a basic level. No previous SSIS or C# experience is needed.

What do I take away from the course?

You leave with the SSIS project that loads a star schema data warehouse, which you built in the capstone, plus a lab guide, project files and SQL scripts for every lab, the sample databases and a certificate of completion.

Articles are published in Thai.