About This Course
Data integration is at the heart of every modern enterprise data strategy. Whether consolidating data from legacy systems, feeding data warehouses, synchronising operational databases, or automating business reporting pipelines, organisations depend on reliable, high-performance ETL processes to turn raw data into actionable insight. SQL Server Integration Services (SSIS) remains one of the most powerful and widely deployed ETL platforms in the Microsoft ecosystem, enabling data engineers and BI professionals to build sophisticated integration workflows without sacrificing control or scalability.
The Database Integration using SQL Server Integration Services (SSIS) Training offered by TrainingComplex is a comprehensive, hands-on programme designed to take participants from the foundations of SSIS architecture through to advanced package development, deployment, and operational management. Across ten days of structured instruction, delegates will work directly within SQL Server Data Tools (SSDT) and the SSIS Catalog, building real ETL solutions that mirror the kinds of integration challenges found in banking, telecoms, public sector, healthcare, and manufacturing environments.
The curriculum covers the full SSIS development lifecycle: designing Control Flow and Data Flow components, managing connections and variables, applying transformations such as Lookup, Merge Join, Derived Column, and Slowly Changing Dimension, handling errors and logging, parameterising packages for reuse, and deploying solutions to production using the SSIS Catalog on SQL Server. Participants will also explore performance tuning techniques, incremental load strategies, and best practices for maintaining large-scale integration solutions in enterprise settings.
By the end of this programme, participants will be confident designing and delivering SSIS solutions that meet business requirements for data accuracy, timeliness, and scalability. Whether you are new to SSIS or looking to formalise and deepen your existing knowledge, this course provides the technical depth and practical experience needed to operate effectively as a data integration professional.
Who Should Attend
- Database administrators seeking to expand their ETL and data integration capabilities
- BI developers and data engineers responsible for building or maintaining data pipelines
- SQL developers transitioning into data warehousing or integration roles
- Data warehouse architects who need hands-on SSIS implementation skills
- IT professionals involved in system integration, migration, or consolidation projects
- Analytics engineers who need to automate data movement between source systems and reporting layers
Learning Objectives
- Understand the SSIS architecture, components, and its role within the Microsoft data platform
- Navigate SQL Server Data Tools (SSDT) and configure SSIS projects and solutions
- Design and implement Control Flow logic using tasks, containers, and precedence constraints
- Build Data Flow pipelines using source adapters, transformations, and destination components
- Apply advanced transformations including Lookup, Merge Join, Slowly Changing Dimension, and Multicast
- Implement variables, parameters, and expressions to create dynamic, reusable packages
- Configure robust error handling, event handling, and logging strategies
- Develop incremental load patterns using change detection and delta processing techniques
- Deploy SSIS packages to the SSIS Catalog and manage environments and configurations
- Automate package execution using SQL Server Agent and schedule integration workflows
- Apply performance tuning techniques to optimise data throughput in high-volume scenarios
- Implement security best practices for protecting sensitive connection credentials and package configurations
- Troubleshoot failed packages using execution reports, breakpoints, and data viewers
Course Modules
Module 1: Introduction to SSIS and the Microsoft Data Platform
- Overview of the Microsoft data platform and SSIS's role in ETL and ELT architectures
- SSIS architecture: packages, projects, connections, tasks, and the SSIS runtime engine
- Installing and configuring SQL Server Data Tools (SSDT) and the SSIS designer environment
- Understanding the SSIS project deployment model vs. the legacy package deployment model
- Comparing SSIS with other Microsoft integration technologies such as Azure Data Factory and understanding when SSIS is the appropriate choice
- Exploring the SSIS execution model: in-process vs. out-of-process execution and the role of the dtexec utility
Module 2: Navigating the SSIS Designer and Project Structure
- Working with the SSIS designer: Control Flow, Data Flow, Parameters, and Connection Managers tabs
- Creating and organising SSIS projects and solutions in SSDT
- Configuring OLE DB, ADO.NET, and Flat File connection managers for common data sources
- Managing project-level vs. package-level connection managers and their use cases
- Using expressions on connection manager properties to dynamically resolve server names and file paths at runtime
- Configuring and testing connection managers for Excel, ODBC, and HTTP sources to handle less common integration scenarios
Module 3: Building Control Flow Logic
- Understanding the Control Flow canvas and the role of tasks and containers
- Using the Execute SQL Task, File System Task, and Script Task for common control operations
- Implementing precedence constraints to define conditional and sequential execution paths
- Grouping related tasks with Sequence Containers and looping with For Loop and Foreach Loop containers
- Using the Send Mail Task and WMI Data Reader Task to extend control flow with notification and environment inspection capabilities
- Applying annotations, task descriptions, and visual grouping conventions to produce maintainable and self-documenting control flow designs
Module 4: Designing the Data Flow Pipeline
- Understanding the Data Flow Task and the separation of Control Flow from Data Flow execution
- Configuring source adapters: OLE DB Source, Flat File Source, and Excel Source
- Configuring destination adapters: OLE DB Destination, SQL Server Destination, and Flat File Destination
- Monitoring data flow execution with row counts and data viewers during development
- Understanding how SSIS buffers data in memory through the pipeline and the implications of buffer allocation for throughput and resource consumption
- Using the Raw File source and destination for intermediate staging to improve pipeline performance and enable restartable data flow patterns
Module 5: Core Data Transformations
- Applying the Derived Column transformation to compute new columns using SSIS expressions
- Using the Data Conversion transformation to handle data type mismatches between source and destination
- Filtering rows with the Conditional Split transformation based on business rules
- Removing duplicates and sorting data with the Sort transformation and understanding its performance implications
- Applying the Character Map transformation to standardise string casing, Unicode normalisation, and encoding for consistent downstream processing
- Using the Audit transformation to enrich data rows with package metadata such as execution instance ID, machine name, and package version for traceability
Module 6: Advanced Data Transformations
- Implementing the Lookup transformation for reference data matching and cache management strategies
- Combining data streams with Merge, Merge Join, and Union All transformations
- Handling Slowly Changing Dimensions (SCD) using the SCD Wizard for Type 1 and Type 2 changes
- Distributing data to multiple destinations with Multicast and routing errors with Error Output redirection
- Using the Fuzzy Lookup and Fuzzy Grouping transformations to match and consolidate records with inconsistent or misspelled data values
- Implementing the Pivot and Unpivot transformations to restructure tabular data between normalised and denormalised formats required by source and target systems
Module 7: Variables, Parameters, and Dynamic Expressions
- Declaring and scoping SSIS variables at package and container level
- Creating project and package parameters to externalise configuration values
- Writing SSIS expressions to dynamically set properties such as connection strings, file paths, and SQL statements
- Using property expressions to build flexible, environment-aware packages
- Understanding SSIS expression syntax in depth: data type casting, string functions, conditional operators, and common expression patterns used in production packages
- Passing variable values between parent and child packages using package parameters and the Execute Package Task to maintain state across a multi-package solution
Module 8: Error Handling, Event Handling, and Logging
- Understanding SSIS error codes, failure propagation, and the difference between task failure and data errors
- Configuring event handlers (OnError, OnTaskFailed, OnPostExecute) to respond to execution events
- Enabling and configuring built-in SSIS logging providers: SQL Server, text file, and Windows Event Log
- Designing a custom audit logging pattern using Execute SQL Tasks and package variables
- Capturing and redirecting data flow error rows to a dedicated error table to enable post-execution analysis and selective reprocessing of rejected records
- Implementing checkpoint files to allow failed packages to restart from the last successful checkpoint rather than reprocessing all previously completed tasks
Module 9: Incremental Load Patterns and Change Detection
- Understanding full load vs. incremental load strategies and when to apply each
- Implementing datetime-based incremental loads using high-water mark variables and parameterised queries
- Using the CDC Control Task and CDC Source to capture Change Data Capture records from SQL Server
- Designing delta processing workflows that insert, update, and delete target records based on source changes
- Applying the SSIS Lookup transformation in conjunction with OLE DB Command to implement an upsert pattern for targets that do not natively support merge operations
- Managing high-water mark state persistence across package executions using a dedicated control table to ensure reliable and auditable incremental load boundaries
Module 10: Package Parameterisation and Reusability
- Designing reusable package templates with consistent naming conventions, logging, and error handling patterns
- Using the Execute Package Task to call child packages and pass variables between parent and child
- Implementing package configurations and understanding when to use them alongside the project deployment model
- Refactoring hardcoded values into parameters and expressions for environment portability
- Breaking complex ETL workflows into cohesive child packages organised by subject area to improve maintainability, parallel execution opportunities, and independent testability
- Versioning SSIS projects in source control systems such as Git and establishing branching and release workflows that align with enterprise deployment practices
Module 11: Deployment to the SSIS Catalog
- Understanding the SSIS Catalog (SSISDB) architecture: folders, projects, packages, and environments
- Deploying SSIS projects to the SSIS Catalog using SSDT and the Integration Services Deployment Wizard
- Creating Catalog environments and environment variables to manage configuration across Dev, UAT, and Production
- Executing packages from the Catalog and reviewing execution reports and operational logs
- Binding environment variables to project and package parameters to achieve fully automated, environment-specific configuration without modifying the deployed project
- Using T-SQL stored procedures in SSISDB such as catalog.create_execution, catalog.set_execution_parameter_value, and catalog.start_execution to integrate Catalog-based execution into custom orchestration frameworks
Module 12: Automating Execution with SQL Server Agent
- Creating SQL Server Agent jobs to schedule and automate SSIS package execution
- Configuring job steps, proxies, and credentials for secure package execution from SQL Server Agent
- Setting up alerting and notification on job failure using Database Mail and operator configurations
- Monitoring scheduled job history and correlating Agent job logs with SSIS Catalog execution reports
- Parameterising Agent job steps to pass environment references and execution parameter overrides, enabling a single job definition to serve multiple deployment environments
- Implementing multi-step Agent jobs with conditional step flow logic to orchestrate dependent package sequences and handle partial failure scenarios gracefully
Module 13: Performance Tuning, Security, and Troubleshooting
- Identifying performance bottlenecks using Data Flow path statistics, execution trees, and the Buffer Manager
- Applying tuning techniques: adjusting DefaultBufferSize, DefaultBufferMaxRows, and enabling parallelism
- Securing sensitive connection credentials using package protection levels and Catalog encryption
- Troubleshooting failed packages using breakpoints, data viewers, execution reports, and package restart checkpoints
- Diagnosing pipeline blocking transformations—distinguishing fully blocking, partially blocking, and non-blocking components—and restructuring the data flow to minimise buffer stalls and improve throughput
- Implementing role-based access control in the SSIS Catalog using catalog-level roles (ssis_admin, ssis_logreader, dc_operator) to enforce least-privilege security across development, operations, and auditing teams
Requirements & Prerequisites
Participants should have a working knowledge of SQL and be comfortable writing queries, joins, and basic stored procedures in a SQL Server environment. Familiarity with relational database concepts such as tables, schemas, primary keys, and foreign keys is essential. Prior exposure to SQL Server Management Studio (SSMS) is expected, though no previous SSIS experience is required.
Training Methodology
- Guided instructor-led demonstrations within SQL Server Data Tools (SSDT), walking participants through each component before they build it themselves
- Hands-on lab exercises after each module where delegates construct real SSIS packages against sample enterprise databases
- Scenario-based ETL design challenges that simulate common business integration problems such as data consolidation, system migration, and incremental warehouse loading
- Peer review sessions where participants present their package designs and receive structured feedback from the instructor and cohort
- Live debugging workshops using SSIS data viewers, breakpoints, and execution reports to identify and resolve package failures
- Performance benchmarking exercises comparing different data flow configurations to understand the impact of tuning decisions
- Case studies drawn from banking, public sector, and logistics environments illustrating real SSIS deployment patterns and lessons learned
- Take-home reference templates including reusable SSIS package skeletons, logging frameworks, and deployment checklists
Organisational Benefits
- Reduced time-to-insight through automated, reliable ETL pipelines that eliminate manual data movement
- Improved data quality resulting from structured transformation and validation logic built into integration packages
- Lower operational costs as SSIS automation replaces time-consuming manual extraction and loading tasks
- Faster delivery of BI and analytics projects as data engineers can build integration layers independently and efficiently
- Increased system reliability through consistent error handling, alerting, and audit logging within SSIS solutions
- Greater agility in responding to new data source integrations or system migrations with reusable package templates
- Reduced risk during data migrations and consolidations through controlled, testable ETL workflows
- Stronger internal data engineering capability, reducing dependence on external consultants for integration projects
Where Is Database Integration using SQL Server Integration Services (SSIS) Training Offered?
This course runs in-person across: Zanzibar, Mombasa, Abuja, Addis Ababa, Nakuru, Arusha, Pretoria, Dar es Salaam, Kisumu, Riyadh, Nairobi, Dubai, Kigali, Cape Town, Lagos, Naivasha, Kampala, Accra, Johannesburg, plus Live Virtual (online) cohorts. Browse live dates for your city below.
Upcoming Schedules
| Location | Format | Dates | Price |
|---|
| Zanzibar, Tanzania |
In-Person |
28 Sept 2026 – 9 Oct 2026 |
USD 5,000 |
| Mombasa, Kenya |
In-Person |
5 Oct 2026 – 16 Oct 2026 |
KES 230,000 |
| Abuja, Nigeria |
In-Person |
5 Oct 2026 – 16 Oct 2026 |
USD 5,600 |
| Addis Ababa, Ethiopia |
In-Person |
12 Oct 2026 – 23 Oct 2026 |
USD 4,800 |
| Nakuru, Kenya |
In-Person |
12 Oct 2026 – 23 Oct 2026 |
KES 230,000 |
| Live Virtual |
Online |
17 Oct 2026 – 6 Dec 2026 |
KES 160,000 |
| Arusha, Tanzania |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 4,200 |
| Pretoria, South Africa |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 6,600 |
| Live Virtual |
Online |
19 Oct 2026 – 28 Oct 2026 |
KES 160,000 |
| Dar es Salaam, Tanzania |
In-Person |
19 Oct 2026 – 30 Oct 2026 |
USD 4,600 |
| Zanzibar, Tanzania |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 5,000 |
| Kisumu, Kenya |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
KES 240,000 |
| Riyadh, Saudi Arabia |
In-Person |
19 Oct 2026 – 30 Oct 2026 |
USD 9,000 |
| Nairobi, Kenya |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
KES 230,000 |
| Dubai, United Arab Emirates |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 8,200 |
+234 more dates — view live schedule →
Frequently Asked Questions
Do I need prior SSIS experience to attend this course?
No prior SSIS experience is required. The course begins with SSIS fundamentals and progressively builds to advanced topics, making it suitable for SQL developers and DBAs who are new to SSIS but already comfortable working with SQL Server.
What software will participants need during the training?
Participants will work with SQL Server Data Tools (SSDT) for Visual Studio and SQL Server (Developer Edition or higher). TrainingComplex provides a pre-configured lab environment for all in-person cohorts, so delegates do not need to install anything in advance — though guidance is provided for those who wish to set up their own environment.
Will I receive a certificate upon completing the course?
Yes. Participants who attend the full programme and complete the practical assessments will receive a TrainingComplex Certificate of Completion in Database Integration using SSIS, which can be shared with employers and included in professional portfolios.
Does this course prepare me for any Microsoft certification exams?
While this course is not an official Microsoft certification preparation programme, the skills and knowledge covered align closely with the competencies assessed in Microsoft's data platform certifications, particularly those related to data engineering and database administration on Azure and on-premises SQL Server.
Can this training be customised for our organisation's specific integration environment?
Yes. TrainingComplex offers tailored in-house delivery of this programme where lab exercises and case studies can be adapted to reflect your organisation's actual source systems, data models, and integration requirements. Contact us to discuss a customised engagement.
