Skip to content

SQL Server Integration Services (SSIS)

SQL Server Integration Services is Microsoft’s data integration platform: a workflow engine (the control flow) wrapped around a streaming row pipeline (the data flow). These ten articles cover it the way it is actually used in production — how the engine behaves, why a feature exists, where designs fail at scale, and what a senior engineer is expected to be able to reason about.

Every example is fictional and self-contained. Every version-sensitive statement has been checked against current Microsoft documentation, and the two current deprecations that most published material still ignores are called out where they matter.

The articles are written to stand alone, but they build. If you are working through the section rather than looking something up, this order compounds best.

# Article You will be able to
1 Fundamentals Explain the two-engine model, the execution lifecycle, and why validation fails before any row moves
2 Control Flow Design orchestration that fails predictably — constraints, containers, scope, parallelism
3 Data Flow and Pipeline Internals Reason about buffers, execution trees, and blocking behaviour rather than guessing
# Article You will be able to
4 Variables, Parameters, Expressions, and Configuration Build once and configure per environment, and debug how catalog parameter values really resolve
5 Error Handling, Logging, and Restartability Quarantine bad rows, diagnose failures from the catalog, and design loads you can simply re-run
# Article You will be able to
6 Large-Volume ETL and Performance Engineering Isolate the real bottleneck and fix it structurally, not by guessing at buffer settings
7 Incremental Loads: Watermarks, CDC, and Change Tracking Detect change correctly, including deletes, retention gaps, and late-arriving data
# Article You will be able to
8 Deployment, SSISDB, Environments, and Operations Operate the catalog deliberately — configuration, validation, permissions, retention
9 DevOps and Automation Ship one immutable artifact through environments with automated verification
10 Modernizing SSIS Workloads Decide per workload whether to retire, keep, lift, re-orchestrate, or rebuild
If you are… Read
New to SSIS Fundamentals, then Control Flow and Data Flow
Debugging “it failed before doing anything” The execution lifecycle in Fundamentals
Debugging “works in Visual Studio, fails from Agent” Failure modes in Fundamentals
Fighting a slow package Performance Engineering, then blocking transformations in Data Flow
Running out of memory on a large load Buffers and execution trees in Data Flow
Getting one build to run in three environments Variables and Parameters and Deployment and SSISDB
Tired of one bad row killing a load Error Handling
Reading unusable reject files Error metadata in Error Handling
Moving off nightly full reloads Incremental Loads
Investigating silently missing rows Watermark correctness in Incremental Loads
Watching SSISDB outgrow the warehouse Retention and maintenance in Deployment and SSISDB
Automating deployments DevOps and Automation
Chasing configuration drift Drift in DevOps and Automation
Asked whether SSIS should be replaced Modernizing SSIS Workloads
Preparing for a senior interview Every article ends with scenario questions; start with Fundamentals and Data Flow
  1. Control flow and data flow are different engines. Most confusion about errors, restartability, and parallelism comes from treating them as one thing.
  2. Metadata is fixed at design time. Data flows cannot change shape at runtime, which is why VS_NEEDSNEWMETADATA exists and why “dynamic” pipelines have hard limits.
  3. Buffer capacity is driven by declared column width, not actual data. Narrowing rows beats every other tuning knob.
  4. Blocking transformations decide your memory profile. A single Sort turns a streaming pipeline into a batch one that must fit in memory.
  5. Set-based work belongs in SQL. Extract lean, transform minimally, bulk-load to staging, let the database do joins and aggregation.
  6. A Completion constraint does not suppress a failure. It changes what runs next; the package still fails.
  7. Idempotence beats checkpoints. A load you can simply re-run is worth more than any restart configuration — and checkpoints do not work inside loop containers anyway.
  8. State advances last. A watermark, LSN, or version moved before the apply commits is silent, permanent data loss.
  9. Build once, configure many. A separate build per environment means what shipped was never what was tested.
  10. The SSIS CDC Flow Components and the Attunity connectors are deprecated (February 2024), and Data Migration Assistant is deprecated — yet a great deal of current-looking published material still teaches all three.

Starting points in the official documentation

Section titled “Starting points in the official documentation”