SSIS for Data Engineers: Control Flow, Data Flow, SSISDB & Migration to ADF

ssis — SQL Server Integration Services — is the ETL engine a surprising number of data engineers still inherit on day one, long after the industry narrative declared it dead, because a decade of finance, retail, healthcare, and insurance warehouses were built on .dtsx packages that quietly move billions of rows every night and nobody has been paid to rewrite them. The tool ships free with SQL Server, it has a visual designer that a business analyst can be taught in an afternoon, and it happens to be the most-deployed on-premises ETL platform in the world — which means that "we're a modern streaming shop, we don't touch SSIS" is a sentence that survives exactly until the acquisition, the merger, or the cloud-migration mandate that lands three hundred legacy packages in your lap with a directive to keep them running and then get rid of them.

This guide is the walkthrough you wished existed the first time an interviewer said "explain the difference between the control flow and the data flow in an SSIS package," or "walk me through what a lookup transformation actually does to the pipeline buffer," or "we have four hundred SSIS packages and a mandate to move to the cloud — what's your migration plan?" It covers the two halves of every package — the control flow that orchestrates what runs and in what order, and the data flow that streams rows through an in-memory buffer pipeline — then the SSISDB catalog and project deployment model that turns a folder of packages into a governed, parameterised, observable server-side asset, and finally the two honest migration paths to Azure Data Factory: the lift-and-shift onto the Azure-SSIS Integration Runtime that runs your existing .dtsx files unchanged, and the re-platform that rebuilds the logic as native Mapping Data Flows. Each section pairs a teaching block with a Solution-Tail interview answer — real code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the ETL practice library →, rehearse on the data-transformation practice library →, and sharpen the architecture axis with the design practice library →.

---

On this page

• Why SSIS is still everywhere for data engineers
• Control Flow — tasks, precedence, containers, events
• Data Flow — sources, transformations, destinations
• SSISDB, the project deployment model, and execution
• Migration to Azure Data Factory
• Cheat sheet — SSIS recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why SSIS is still everywhere for data engineers

The tool the industry buried is still running the nightly loads — and knowing why is the first interview signal

The one-sentence invariant: ssis is a visual, on-premises ETL platform bundled with SQL Server whose packages split cleanly into a control flow (the orchestration layer that decides which tasks run, in what order, and under what conditions) and one or more data flows (the streaming buffer engine that reads from sources, transforms rows in memory, and writes to destinations) — and it survives in 2026 not because it is the best tool but because it is the incumbent tool, embedded in thousands of production warehouses whose owners face a build-vs-migrate-vs-leave-alone decision that every data engineer will eventually be handed. The reason SSIS keeps appearing in interviews is that it is a perfect proxy for whether a candidate understands ETL fundamentals — buffers, blocking transforms, orchestration, deployment, parameterisation — and whether they can reason about a legacy-migration project without either blind loyalty ("SSIS is fine, leave it") or reflexive contempt ("rewrite everything in Spark").

The two halves of every package — the mental model that unlocks everything.

• Control flow. The top-level design surface. It is a workflow graph of tasks (Execute SQL, Data Flow, File System, Execute Process, Script) connected by precedence constraints (arrows that fire on success, failure, or completion, optionally gated by an expression). It answers "what runs and when," not "how do rows move." A control flow with no Data Flow Task moves zero rows — it just orchestrates.
• Data flow. A special task type that, when you double-click it, opens its own design surface. This is the actual ETL engine: a source pulls rows into an in-memory buffer, one or more transformations mutate or route those buffers, and a destination writes them out. Rows stream through in batches; the pipeline is the thing people mean when they say "SSIS is fast" (or "SSIS blocks and spills to disk").
• The relationship. One control flow can contain many Data Flow Tasks, plus non-data tasks (truncate a staging table, send an email, run a stored procedure). The control flow is the conductor; each data flow is one instrument. Confusing the two is the single most common junior mistake and the fastest way to fail the opening interview question.

Why it is still everywhere in 2026.

• The installed base is enormous. SSIS shipped free with every SQL Server licence since 2005. For twenty years it was the default ETL tool for any shop that already ran SQL Server — which is most enterprises. Those packages did not evaporate; they became load-bearing infrastructure.
• Migration is expensive and risky. A package that has correctly loaded the finance warehouse every night for eight years carries undocumented business logic in its derived columns, its conditional splits, and its stored-procedure calls. Rewriting it means re-deriving that logic and re-validating every downstream report. "If it isn't broken, and rewriting it is a six-month project with regulatory exposure, leave it" is a rational decision.
• The cloud mandate forces the question anyway. The countervailing force is the cloud-first policy: "no new on-prem workloads, and a plan to retire the datacentre by year-end." That collides with the SSIS estate and produces the migration project that lands on a data engineer's desk. Microsoft's own answer — the Azure-SSIS Integration Runtime — exists precisely because so many customers needed a way to keep running .dtsx in the cloud.
• The skills are transferable and the concepts are canonical. Buffers, blocking transforms, lookups, slowly changing dimensions, deployment parameterisation — these are ETL universals. An interviewer who asks about SSIS is often really asking "do you understand ETL," and SSIS is the shared vocabulary.

The four axes interviewers actually probe.

• Architecture. Can you explain the control-flow / data-flow split, the buffer model, and blocking vs non-blocking transforms without hand-waving? This is the "do you understand the engine" axis.
• Operations. Do you know the SSISDB catalog, the project deployment model, environments, and how to read catalog.executions when a package fails at 3 AM? This is the "have you actually run it in production" axis.
• Design maturity. Can you critique an SSIS design — a blocking Sort where a MERGE JOIN with sorted sources would stream, a per-row Lookup in Full Cache mode that blows out memory, a hard-coded connection string that should be a parameter? This is the "can you make it better" axis.
• Migration judgement. Given an estate and a cloud mandate, can you triage into lift-and-shift, re-platform, and retire buckets and defend the split on cost and effort? This is the "can you lead the project" axis.

What interviewers listen for.

• Do you name control flow vs data flow as orchestration vs pipeline in the first sentence? — required answer.
• Do you describe the data flow as a streaming in-memory buffer engine, not "it runs a query"? — senior signal.
• Do you name blocking transforms (Sort, Aggregate) as the memory and performance villains? — senior signal.
• Do you know SSISDB is the deployment target and catalog.executions is where you debug? — required answer.
• Do you frame migration as lift-and-shift (Azure-SSIS IR) vs re-platform (Mapping Data Flows) vs retire, not "rewrite in Spark"? — senior signal.

Worked example — the SSIS component map

Detailed explanation. The single most useful artifact for an SSIS interview is a mental map of which component lives in the control flow versus the data flow, and what each one is for. Every SSIS discussion converges on this map, and drawing it confidently in the first two minutes signals fluency. Walk through building it for a hypothetical nightly warehouse load.

• The job. Truncate a staging table, load a flat file of orders into staging, resolve customer keys, split new versus changed rows, write to the warehouse, then email a summary.
• Control-flow layer. Truncate (Execute SQL Task) → Data Flow Task → email (Send Mail Task), wired by precedence constraints.
• Data-flow layer. Flat File Source → Lookup (customer key) → Conditional Split (new / changed) → OLE DB Destination.

Question. Classify each component of the nightly load as control-flow or data-flow, and state what it does.

Input.

Component	Layer	Role
Execute SQL Task	control flow	run a T-SQL statement (truncate, MERGE, proc call)
Data Flow Task	control flow	container that hosts one data-flow pipeline
Send Mail Task	control flow	send a notification email
Flat File Source	data flow	read a delimited/fixed-width file into buffers
Lookup	data flow	join each row against a reference set for key resolution
Conditional Split	data flow	route rows to outputs by a boolean expression
OLE DB Destination	data flow	bulk-insert buffers into a SQL Server table

Code.

CONTROL FLOW (orchestration surface)
────────────────────────────────────
 [Execute SQL Task: TRUNCATE stg.orders]
              │ (precedence: Success)
              ▼
 [Data Flow Task: Load Orders]  ──────────────┐
              │ (precedence: Success)         │  double-click opens ↓
              ▼                                │
 [Send Mail Task: "Load OK"]                   │
                                               │
DATA FLOW (pipeline surface, inside the DFT)   │
───────────────────────────────────────────────
 [Flat File Source: orders.csv]
              │  buffer of rows
              ▼
 [Lookup: dbo.dim_customer on customer_code]
              │  (match output — customer_key added)
              ▼
 [Conditional Split]
        ├── new     → [OLE DB Destination: dw.fact_orders]
        └── changed → [OLE DB Command: UPDATE dw.fact_orders]

Step-by-step explanation.

1. Everything on the outer surface is control flow. TRUNCATE stg.orders is an Execute SQL Task — it runs a statement and moves zero rows through any buffer; it is pure orchestration. The precedence arrow labelled Success means the next task runs only if the truncate succeeds.
2. The Data Flow Task is the bridge. On the control-flow surface it is a single box; double-clicking it drops you into the data-flow surface where the real ETL lives. This is the split that trips up newcomers: the Data Flow Task is a control-flow task whose job is to host a data-flow pipeline.
3. Inside the data flow, the Flat File Source reads orders.csv and fills memory buffers with rows. Nothing downstream sees a "file" — it sees a stream of typed columns in a buffer.
4. The Lookup transformation joins each incoming row against dbo.dim_customer to resolve customer_code into a surrogate customer_key. It has a match output (rows that found a key) and a no-match output (rows that did not) — routing, not just enriching.
5. The Conditional Split evaluates a boolean expression per row and sends each row down exactly one output path. New orders go to an OLE DB Destination (bulk insert); changed orders go to an OLE DB Command (per-row UPDATE). Back on the control flow, when the whole data flow finishes, the Send Mail Task fires.

Output.

Layer	Components in the nightly load	Rows moved
Control flow	Execute SQL, Data Flow Task, Send Mail	0 (orchestration)
Data flow	Flat File Source, Lookup, Conditional Split, OLE DB Destination/Command	all rows

Rule of thumb. When you open any inherited package, first read the control flow top-to-bottom to understand the job, then open each Data Flow Task to understand the data movement. Never try to understand a package by reading one layer alone — the logic lives in both.

Worked example — what interviewers actually probe about SSIS

Detailed explanation. The SSIS interview has a predictable arc: an opening definitional question, a "how does the engine work" probe, an operations question, and a migration question. Candidates who answer with engine mechanics (buffers, blocking) and operational reality (SSISDB, logging) score highest; candidates who describe the drag-and-drop UI score lowest. Walk through the grading rubric.

• Opener. "What is SSIS and how is a package structured?" — invites control-flow vs data-flow.
• Engine probe. "What happens to memory when you add a Sort transform?" — probes blocking / buffers.
• Operations probe. "A package failed overnight — where do you look?" — probes SSISDB / logging.
• Migration probe. "You have 300 packages and a cloud mandate — what now?" — probes lift-and-shift vs re-platform.

Question. Draft a senior SSIS answer that pre-empts all four probes without waiting to be asked.

Input.

Interview signal	Weak answer	Senior answer
Package structure	"you drag boxes and connect them"	"control flow orchestrates tasks; data flow streams rows through buffers"
Blocking transforms	"Sort just sorts"	"Sort is fully blocking — buffers the whole set, can spill to disk"
Failure debugging	"check the logs"	"query SSISDB catalog.executions and event_messages by execution_id"
Migration	"rewrite it in Spark"	"triage: lift-and-shift on Azure-SSIS IR, re-platform hot paths to Mapping Data Flows, retire dead packages"

Code.

Senior SSIS answer template (4 minutes)
=======================================

Minute 1 — structure
  "A package has two layers. The control flow is a workflow of tasks —
   Execute SQL, Data Flow, File System — connected by precedence
   constraints. The data flow is a streaming buffer engine: source →
   transformations → destination, moving rows in memory batches."

Minute 2 — engine mechanics
  "Transforms are non-blocking (Derived Column, Lookup in cache),
   semi-blocking (Merge Join, Union All), or fully blocking (Sort,
   Aggregate). Blocking transforms hold the entire rowset in memory
   and can spill to disk under memory pressure — they're the first
   thing I profile for performance."

Minute 3 — operations
  "We deploy the project deployment model — one .ispac to the SSISDB
   catalog, with environments for dev/test/prod parameter values.
   When a package fails, I query catalog.executions for the status
   and catalog.event_messages for the error, filtered by execution_id."

Minute 4 — migration
  "For a cloud mandate I triage the estate: lift-and-shift the bulk
   onto the Azure-SSIS Integration Runtime so .dtsx runs unchanged in
   ADF; re-platform the high-value or high-cost packages to native
   Mapping Data Flows; and retire packages that no longer have a
   consumer. Never a big-bang rewrite."

Step-by-step explanation.

1. Minute 1 frames the whole tool as two layers. Saying "control flow orchestrates, data flow streams rows through buffers" in the first breath signals you understand the architecture, not just the UI.
2. Minute 2 is the engine probe. The blocking taxonomy — non-blocking, semi-blocking, fully blocking — is the senior differentiator. Naming Sort and Aggregate as fully blocking and mentioning disk spill shows you have profiled real packages.
3. Minute 3 is operations. Mentioning the project deployment model, the .ispac, the SSISDB catalog, and the exact views you query (catalog.executions, event_messages) proves production experience, not classroom knowledge.
4. Minute 4 is migration judgement. The triage framing — lift-and-shift / re-platform / retire — and the explicit rejection of a big-bang rewrite is exactly the pragmatism senior interviewers reward.
5. The whole answer is a four-minute monologue that pre-empts every follow-up. Rehearse it once and deploy it whenever SSIS comes up; it demonstrates all four axes without being led.

Output.

Grading criterion	Weak score	Senior score
Names control-flow / data-flow split	rare	mandatory
Names blocking taxonomy	rare	senior signal
Names SSISDB + logging views	occasional	mandatory
Frames migration as triage	rare	senior signal
Rejects big-bang rewrite	rare	senior signal

Rule of thumb. Treat the SSIS interview as an ETL-fundamentals interview wearing SSIS clothing. Answer with engine mechanics and operational reality; the person who only describes the designer canvas has never debugged a 3 AM failure.

Worked example — the migration decision tree

Detailed explanation. Given an SSIS estate and a cloud mandate, the senior engineer runs a short decision tree per package rather than treating the estate as one monolith. Codifying the tree makes the plan defensible: every package lands in lift-and-shift, re-platform, or retire, with a reason. Walk through the tree with three canonical packages.

• Q1. Does the package still have a live downstream consumer? → no = retire; yes = Q2.
• Q2. Is it cheap to run and low-change (stable, off-peak, small)? → yes = lift-and-shift; no = Q3.
• Q3. Is it a hot path (expensive, frequently changed, or a scaling bottleneck)? → yes = re-platform to Mapping Data Flow; no = lift-and-shift.
• Q4 (parallel). Does it depend on a bespoke Script Component or third-party component with no cloud equivalent? → yes = re-platform or keep on-prem; flag it.

Question. Walk the decision tree for three packages and record the disposition of each.

Input.

Package	Live consumer?	Cheap/stable?	Hot path?	Bespoke component?
nightly_finance_load.dtsx	yes	yes	no	no
hourly_clickstream_agg.dtsx	yes	no	yes	no
legacy_fax_import.dtsx	no	—	—	yes

Code.

# Migration triage helper (illustrative)
def triage(has_consumer: bool,
           cheap_stable: bool,
           hot_path: bool,
           bespoke_component: bool) -> str:
    """Return the migration disposition for one SSIS package."""
    if not has_consumer:
        return "RETIRE"                       # nobody reads its output
    if bespoke_component:
        return "RE-PLATFORM (or keep on-prem); flag custom code"
    if hot_path:
        return "RE-PLATFORM to Mapping Data Flow"
    if cheap_stable:
        return "LIFT-AND-SHIFT to Azure-SSIS IR"
    return "LIFT-AND-SHIFT (default)"


print(triage(True,  True,  False, False))
# → LIFT-AND-SHIFT to Azure-SSIS IR
print(triage(True,  False, True,  False))
# → RE-PLATFORM to Mapping Data Flow
print(triage(False, False, False, True))
# → RETIRE

Step-by-step explanation.

1. nightly_finance_load.dtsx has a live consumer, is cheap and stable, and is not a scaling bottleneck. It lands in lift-and-shift: run the existing .dtsx unchanged on the Azure-SSIS Integration Runtime. Zero refactor, minimal risk — the right call for the long tail of "boring but critical" packages.
2. hourly_clickstream_agg.dtsx has a live consumer but is expensive and a scaling bottleneck (it is a hot path). It lands in re-platform: rebuild the logic as an ADF Mapping Data Flow that scales out on Spark. The refactor cost is justified by the ongoing compute savings and the elasticity.
3. legacy_fax_import.dtsx has no live consumer — the fax gateway was decommissioned years ago and nobody noticed the package still runs. It lands in retire. Every estate has these; finding and killing them is free savings and the fastest migration win.
4. The parallel Q4 branch catches the packages with a bespoke Script Component or a purchased third-party transform that has no cloud equivalent. These get flagged: either re-platform the custom logic natively or accept keeping that package on-prem behind a hybrid runtime. Never let a bespoke component silently break a lift-and-shift.
5. The output of the tree is an estate spreadsheet: every package tagged retire / lift-and-shift / re-platform with a one-line reason. That spreadsheet is the migration plan, and it is what the senior candidate produces in the interview instead of "we'll rewrite it in Spark."

Output.

Package	Disposition	Reason
nightly_finance_load.dtsx	lift-and-shift	cheap, stable, live consumer
hourly_clickstream_agg.dtsx	re-platform	hot path; scale-out saves money
legacy_fax_import.dtsx	retire	no live consumer

Rule of thumb. Never migrate an SSIS estate as one monolith. Run the per-package decision tree, produce the tagged inventory, and sequence the work retire-first (free wins), then lift-and-shift (fast bulk), then re-platform (the expensive hot paths). The inventory is the deliverable.

Senior interview question on the SSIS estate

A senior interviewer often opens with: "You've just joined a company that runs 300 SSIS packages on an ageing on-prem SQL Server, orchestrated by SQL Agent jobs. There is a board-level mandate to exit the datacentre within a year. Walk me through how you'd inventory the estate, triage it, and sequence a migration — and how you'd avoid breaking the nightly finance load in the process."

Solution Using an automated SSISDB inventory + a triaged, sequenced migration plan

-- Step 1 — inventory every deployed package and its last-run health from SSISDB
SELECT  f.name                        AS folder,
        p.name                        AS project,
        pkg.name                      AS package,
        COUNT(e.execution_id)         AS runs_30d,
        SUM(CASE WHEN e.status = 7 THEN 1 ELSE 0 END) AS failures_30d,
        MAX(e.end_time)               AS last_run,
        AVG(DATEDIFF(SECOND, e.start_time, e.end_time)) AS avg_seconds
FROM        catalog.folders   AS f
JOIN        catalog.projects  AS p   ON p.folder_id  = f.folder_id
JOIN        catalog.packages  AS pkg ON pkg.project_id = p.project_id
LEFT JOIN   catalog.executions AS e  ON e.project_id = p.project_id
                                    AND e.package_name = pkg.name
                                    AND e.start_time > DATEADD(DAY, -30, SYSDATETIMEOFFSET())
GROUP BY    f.name, p.name, pkg.name
ORDER BY    runs_30d DESC;

-- Step 2 — flag "no live consumer" candidates: packages that haven't run in 90 days
SELECT  p.name AS project, pkg.name AS package, MAX(e.end_time) AS last_run
FROM        catalog.packages AS pkg
JOIN        catalog.projects AS p ON p.project_id = pkg.project_id
LEFT JOIN   catalog.executions AS e ON e.project_id = p.project_id
                                   AND e.package_name = pkg.name
GROUP BY    p.name, pkg.name
HAVING      MAX(e.end_time) IS NULL
        OR  MAX(e.end_time) < DATEADD(DAY, -90, SYSDATETIMEOFFSET());

# Step 3 — join the two queries into a triage scorecard
import pandas as pd

inv = pd.read_sql(INVENTORY_SQL, conn)          # from Step 1
inv["failure_rate"] = inv["failures_30d"] / inv["runs_30d"].clip(lower=1)

def disposition(row):
    if pd.isna(row["last_run"]) or row["runs_30d"] == 0:
        return "RETIRE"
    if row["avg_seconds"] > 1800 or row["runs_30d"] > 200:   # expensive or hot
        return "RE-PLATFORM"
    return "LIFT-AND-SHIFT"

inv["disposition"] = inv.apply(disposition, axis=1)
plan = inv.sort_values(["disposition", "runs_30d"], ascending=[True, False])
plan.to_csv("ssis_migration_plan.csv", index=False)

Step-by-step trace.

Step	Input	Output
1. Inventory	SSISDB catalog views	one row per package: runs, failures, avg runtime, last run
2. Dead-package scan	executions history	list of packages idle > 90 days
3. Score	inventory + thresholds	each package tagged retire / lift-and-shift / re-platform
4. Sequence	tagged plan	retire first, then lift-and-shift bulk, then re-platform hot paths
5. Protect finance	pin nightly_finance_load	lift-and-shift with a parallel-run validation window

After running the inventory, roughly 15% of the 300 packages have not executed in 90 days and are retired outright; ~70% are cheap and stable and are batched into lift-and-shift onto the Azure-SSIS IR; ~15% are hot paths flagged for a Mapping Data Flow re-platform. The nightly finance load is lift-and-shifted first but kept in a two-week parallel-run window (old on-prem and new cloud run side by side, outputs diffed) before the on-prem job is disabled.

Output:

Bucket	Share of estate	Approach	Sequence
Retire	~15%	disable + archive	week 1 (free wins)
Lift-and-shift	~70%	Azure-SSIS IR, unchanged .dtsx	weeks 2–8 (bulk)
Re-platform	~15%	Mapping Data Flows	weeks 8–26 (hot paths)
Finance load	(in lift-and-shift)	parallel-run validation	week 2, gated cutover

Why this works — concept by concept:

• SSISDB as the source of truth — the catalog views (catalog.executions, catalog.packages) already record every run, its status, and its runtime. Inventorying from SSISDB is faster and more accurate than reading .dtsx XML by hand, and it surfaces the dead packages nobody remembers.
• Triage before touching code — tagging every package retire / lift-and-shift / re-platform first means you never spend re-platform effort on a package that should be retired, and you never big-bang-rewrite a package a lift-and-shift would have handled for free.
• Retire-first sequencing — killing the 15% dead packages is zero-risk and immediately shrinks the estate you have to migrate, making every later phase cheaper.
• Parallel-run validation for the critical path — the finance load is lift-and-shifted but run in parallel with the on-prem original, outputs diffed row-for-row, before cutover. This is how you honour "don't break the nightly finance load" while still meeting the mandate.
• Cost — the inventory is a handful of SQL queries (minutes); the plan is a spreadsheet. The expensive work (re-platform) is deliberately confined to ~15% of packages. Compared to a naive full rewrite (O(all packages) of engineering), this is O(hot paths) plus O(config) for the bulk — the difference between a one-quarter project and a two-year one.

ETL
Topic — etl
ETL problems on legacy pipeline migration

Practice →

Design Topic — design Design problems on ETL estate triage

Practice →

---

2. Control Flow — tasks, precedence, containers, events

The orchestration layer: tasks wired by precedence constraints, wrapped in containers, watched by event handlers

The mental model in one line: the control flow is the workflow graph at the top of every SSIS package — a set of tasks (Execute SQL, Data Flow, File System, Execute Process, Script) connected by precedence constraints (arrows that fire on Success, Failure, or Completion, optionally gated by an expression), grouped into containers (Sequence, For Loop, Foreach Loop) for scoping and iteration, and surrounded by event handlers (OnError, OnPostExecute) that react to lifecycle events — and it is parameterised by variables and parameters whose scope and evaluation timing decide whether your dynamic logic works or silently reads a stale value. Master the control flow and you can make any package resumable, restartable, and dynamic; misunderstand precedence-constraint logic or variable scope and you ship a package that runs the wrong branch or reads the wrong file.

The task types you meet in every package.

• Execute SQL Task. Runs a T-SQL statement or stored procedure against a connection. Used to truncate staging, call a MERGE proc, capture a value into a variable (via a result-set binding), or run DDL. The workhorse of the control flow.
• Data Flow Task. Hosts one data-flow pipeline (Section 3). On the control flow it is a single box; it is where all row movement happens.
• File System Task. Copy, move, delete, or rename files and folders. Common in file-ingest patterns (archive the processed file after load).
• Execute Process Task. Shells out to an external executable (a .bat, 7z.exe, a Python script). The escape hatch when SSIS has no native component.
• Script Task. Runs custom C# or VB.NET in the control flow — arbitrary logic, API calls, complex variable manipulation. Powerful and dangerous; a common migration blocker.
• Send Mail Task / Execute Package Task. Notify on completion; call a child package (the master-package pattern that composes many packages into one orchestrated run).

Precedence constraints — the arrows that are also logic.

• The three value constraints. Success (green — run next only if the prior task succeeded), Failure (red — run next only if it failed; the classic error branch), Completion (blue — run next regardless of outcome).
• The expression constraint. Add a boolean SSIS expression that must also be true for the arrow to fire — e.g. @RowCount > 0 to skip a downstream step when nothing loaded. You can combine value + expression.
• Multiple constraints into one task — AND vs OR. When several arrows point at one task, the default is logical AND (all must be satisfied). Switch to logical OR (dashed arrows) when any one satisfied constraint should trigger the task — critical for "run cleanup if either branch finished."
• The common bug. Leaving the default AND when you meant OR, so the cleanup task never runs because the failure branch AND the success branch can never both be satisfied.

Containers — scoping and iteration.

• Sequence Container. Groups tasks into a logical unit with a shared scope. Used to give a block of tasks one transaction, one disable switch, or one set of scoped variables.
• For Loop Container. Classic counter loop (@i = 0; @i < 10; @i = @i + 1) evaluated by SSIS expressions. Rare in ETL; used for retry loops or fixed-count iteration.
• Foreach Loop Container. Iterates over an enumerator — files in a folder, rows in an ADO recordset, nodes in XML, items in a variable collection. The backbone of the "process every file in the drop folder" pattern; it assigns each item to a variable the inner tasks consume.

Event handlers — the lifecycle reactors.

• OnError. Fires when a task raises an error. The place to log the error to a table, send an alert, or set a failure flag — without cluttering the main control flow.
• OnPostExecute / OnPreExecute. Fire after / before each executable. Used for row-count auditing and instrumentation.
• OnWarning / OnTaskFailed / OnVariableValueChanged. Finer-grained hooks. Event handlers inherit scope: an OnError on the package fires for any child task unless a child defines its own.

Variables and parameters — dynamic behaviour done right.

• Variables. Package- or container-scoped named values (User::FilePath, User::RowCount). Mutable at runtime by tasks (result-set bindings, Script Tasks, expressions). Scope matters: a variable scoped to a container is invisible outside it.
• Parameters. Project- or package-level inputs set at deployment/execution time (the project deployment model, Section 4). Immutable during a run — the clean way to inject a connection string or a batch date per environment.
• EvaluateAsExpression. Set a variable's value to a computed expression that re-evaluates each time it is read — e.g. a file path built from a base folder plus a date. The most common dynamic-SSIS pattern and the most common source of "why is it reading the old value" confusion (a static-valued variable does not re-compute).

Worked example — a precedence-constraint gate with an expression

Detailed explanation. A nightly load should only run the expensive warehouse MERGE if the staging load actually brought in rows; otherwise it should skip straight to cleanup. This is a precedence constraint combining a Success value with an expression on a row-count variable. Build it.

• The variable. User::StagingRowCount (Int32), populated by the staging Data Flow Task's row count.
• The gate. The arrow from the staging DFT to the MERGE task fires on Success && @StagingRowCount > 0.
• The skip. A second arrow (OR) routes to cleanup when the count is zero.

Question. Configure the precedence constraint so the MERGE runs only when staging loaded at least one row.

Input.

Element	Value
Variable	User::StagingRowCount (Int32)
Source of value	Row Count transform inside the staging data flow
Constraint type	Expression and Constraint
Constraint value	Success
Expression	@[User::StagingRowCount] > 0

Code.

Precedence constraint editor — arrow from [DFT: Load Staging] → [Execute SQL: MERGE to DW]

  Evaluation operation : Expression and Constraint
  Value                : Success
  Expression           : @[User::StagingRowCount] > 0
  Multiple constraints : Logical AND   (this is the only inbound arrow to MERGE)

-- The MERGE task's statement (runs only when the gate opens)
MERGE dw.fact_orders AS tgt
USING stg.orders      AS src
   ON tgt.order_id = src.order_id
WHEN MATCHED AND tgt.row_hash <> src.row_hash THEN
    UPDATE SET tgt.total_cents = src.total_cents,
               tgt.status      = src.status,
               tgt.row_hash    = src.row_hash,
               tgt.updated_at  = SYSDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (order_id, customer_key, total_cents, status, row_hash)
    VALUES (src.order_id, src.customer_key, src.total_cents, src.status, src.row_hash);

# SSIS expression cheat: build a dated archive path for the File System Task
# (variable User::ArchivePath, EvaluateAsExpression = True)
@[User::ArchiveFolder] + "orders_" +
   (DT_WSTR, 4) YEAR(GETDATE())  +
   RIGHT("0" + (DT_WSTR, 2) MONTH(GETDATE()), 2) +
   RIGHT("0" + (DT_WSTR, 2) DAY(GETDATE()),   2) + ".csv"

Step-by-step explanation.

1. Inside the staging data flow, a Row Count transform writes the number of rows that flowed through into User::StagingRowCount. The variable is package-scoped so the control flow can read it after the DFT completes.
2. On the arrow from the staging DFT to the MERGE task, the evaluation operation is set to Expression and Constraint — both conditions must hold. The Value is Success (the DFT must have succeeded) and the Expression is @[User::StagingRowCount] > 0.
3. Because MERGE has only this one inbound arrow, the multiple-constraints setting is irrelevant (AND vs OR only matters with several arrows). If the count is zero, the arrow does not fire and MERGE is skipped.
4. A second arrow from the staging DFT to a cleanup/archive task uses Expression and Constraint with @[User::StagingRowCount] == 0, so exactly one downstream path runs regardless of the count. This is the branch-on-data pattern.
5. The archive path is built with an SSIS expression on a variable with EvaluateAsExpression = True, so it re-computes the current date every run — the (DT_WSTR, n) casts and RIGHT("0" + ..., 2) zero-padding are the canonical SSIS date-string idiom.

Output.

Staging row count	MERGE arrow fires?	Path taken
12,345	yes (Success && count > 0)	MERGE to DW → archive
0	no	skip MERGE → cleanup only
(DFT failed)	no (Success not met)	OnError handler → alert

Rule of thumb. Use Expression and Constraint precedence to make packages skip expensive work when there is nothing to do, and always give the "empty" case its own explicit path. A package that runs a full MERGE against zero staged rows every night is wasting a maintenance window.

Worked example — a Foreach Loop over files in a drop folder

Detailed explanation. The most common control-flow pattern in file ingestion: iterate every .csv in a folder, load each into staging via a data flow, then archive it. The Foreach Loop Container with a File enumerator assigns each file path to a variable that the inner Data Flow Task's connection uses via an expression. Build it end to end.

• Enumerator. Foreach File Enumerator over \\drop\orders\*.csv, returning the fully-qualified name.
• Variable mapping. Each file path → User::CurrentFilePath.
• Dynamic connection. The Flat File Connection Manager's ConnectionString property is an expression bound to @[User::CurrentFilePath].
• Archive. A File System Task moves the processed file to an archive folder.

Question. Configure the Foreach Loop so each file in the drop folder is loaded and then archived.

Input.

Element	Value
Container	Foreach Loop, File enumerator
Folder / mask	\\drop\orders\ / *.csv
Retrieve	Fully qualified file name
Loop variable	User::CurrentFilePath (String)
Connection binding	Flat File CM ConnectionString = @[User::CurrentFilePath]

Code.

FOREACH LOOP CONTAINER  (File enumerator: \\drop\orders\*.csv, fully qualified)
  Variable Mappings:  Index 0 → User::CurrentFilePath
  │
  ├─[Data Flow Task: Load one file]
  │     Flat File Source  →  Derived Column (add source_file, load_ts)
  │                       →  OLE DB Destination: stg.orders
  │       (Flat File CM.ConnectionString  = @[User::CurrentFilePath]   ← expression)
  │
  └─[File System Task: Move file]
        Operation   : Move file
        Source      : @[User::CurrentFilePath]
        Destination : @[User::ArchiveFolder]   (expression)

# Derived Column expressions inside the data flow
source_file  =  TOKEN(@[User::CurrentFilePath], "\\", TOKENCOUNT(@[User::CurrentFilePath], "\\"))
load_ts      =  GETDATE()

-- Staging table the destination writes into
CREATE TABLE stg.orders (
    order_id     BIGINT       NOT NULL,
    customer_code VARCHAR(32) NOT NULL,
    total_cents  BIGINT       NOT NULL,
    status       VARCHAR(20)  NOT NULL,
    source_file  VARCHAR(260) NOT NULL,   -- provenance: which file this row came from
    load_ts      DATETIME2    NOT NULL
);

Step-by-step explanation.

1. The Foreach Loop Container is configured with the Foreach File Enumerator pointed at \\drop\orders\ with the mask *.csv, retrieving the fully qualified file name (so the connection manager gets a complete path, not just a bare filename).
2. In Variable Mappings, index 0 maps to User::CurrentFilePath. On each iteration the container assigns the next file's full path to that variable before running the inner tasks.
3. The Flat File Connection Manager's ConnectionString property is set via the Expressions collection to @[User::CurrentFilePath]. Because it re-evaluates each iteration, the same Data Flow Task reads a different physical file every loop — one DFT, N files.
4. Inside the data flow, a Derived Column adds provenance: source_file (the bare filename, extracted with TOKEN/TOKENCOUNT on the backslash) and load_ts (GETDATE()). Every staged row now records which file and when — invaluable for debugging a bad file later.
5. After the DFT succeeds, the File System Task moves the processed file to the archive folder using @[User::CurrentFilePath] as source. Archiving inside the loop guarantees a re-run does not reprocess already-loaded files.

Output.

Iteration	CurrentFilePath	Rows loaded	Archived to
1	\\drop\orders\orders_20260803_01.csv	4,102	\\archive\orders\
2	\\drop\orders\orders_20260803_02.csv	3,880	\\archive\orders\
3	\\drop\orders\orders_20260803_03.csv	5,551	\\archive\orders\

Rule of thumb. Drive dynamic file loads with a Foreach File Enumerator and an expression-bound connection string, always add source-file + load-timestamp provenance columns in a Derived Column, and archive inside the loop so re-runs are idempotent. This one pattern covers the majority of real-world SSIS ingestion.

Worked example — an OnError event handler that logs and alerts

Detailed explanation. Rather than wiring failure branches into every task, centralise error handling in an OnError event handler at the package scope: it fires for any task that errors, logs the SSIS system error variables to a table, and sends one alert. Build it.

• Scope. OnError on the package executable (inherited by all child tasks).
• Log. Execute SQL Task inserts System::ErrorCode, System::ErrorDescription, System::SourceName, System::StartTime into an error-log table.
• Alert. Send Mail Task with the error description.
• Guard. Set Propagate = False on the handler if you want to swallow the error for a specific non-critical task (used carefully).

Question. Build a package-scoped OnError handler that logs the failure and alerts, capturing the SSIS system error variables.

Input.

System variable	Meaning
System::ErrorCode	numeric SSIS/OLE DB error code
System::ErrorDescription	human-readable error text
System::SourceName	the task that failed
System::PackageName	the package
System::StartTime	when the failing executable started

Code.

-- Error-log table the handler writes to
CREATE TABLE etl.package_errors (
    error_id        BIGINT IDENTITY PRIMARY KEY,
    package_name    VARCHAR(128) NOT NULL,
    source_name     VARCHAR(128) NOT NULL,
    error_code      INT          NOT NULL,
    error_desc      VARCHAR(2000) NOT NULL,
    occurred_at     DATETIME2    NOT NULL DEFAULT SYSDATETIME()
);

EVENT HANDLER: OnError  (scope = package)
  │
  ├─[Execute SQL Task: Log error]
  │     Connection : DW
  │     SQLStatement:
  │        INSERT INTO etl.package_errors
  │              (package_name, source_name, error_code, error_desc)
  │        VALUES (?, ?, ?, ?);
  │     Parameter Mapping:
  │        0 → System::PackageName       (VARCHAR)
  │        1 → System::SourceName        (VARCHAR)
  │        2 → System::ErrorCode         (LONG)
  │        3 → System::ErrorDescription  (VARCHAR)
  │
  └─[Send Mail Task: Alert on-call]
        Subject : "SSIS failure: " + @[System::PackageName]
        Body    : @[System::SourceName] + " failed: " + @[System::ErrorDescription]

# Send Mail Task subject/body expressions
Subject = "SSIS failure: " + @[System::PackageName] + " / " + @[System::SourceName]
Body    = "Error " + (DT_WSTR, 12) @[System::ErrorCode] + ": " +
          @[System::ErrorDescription]

Step-by-step explanation.

1. The handler is defined on the package executable, so it inherits down to every child task — a failure anywhere fires this one handler unless a child task defines its own OnError with a different scope.
2. The Execute SQL Task inserts a row into etl.package_errors using parameter markers (?) bound to the SSIS system variables. System::SourceName tells you which task failed; System::ErrorDescription gives the message you would otherwise have to reconstruct from logs.
3. Parameter mapping order matters: SSIS binds ? markers positionally (0, 1, 2, 3) to the mapped variables, and the SQL types (VARCHAR, LONG) must match the target columns. A mismatched type here is a classic silent failure.
4. The Send Mail Task builds a subject and body from the same system variables via expressions, casting the numeric ErrorCode with (DT_WSTR, 12) so it can be concatenated into the string body.
5. Propagate (in System::Propagate) controls whether the error bubbles up after the handler runs. Left at the default True, the task still fails the package (correct for a critical load). Set to False only when you deliberately want to log-and-continue for a genuinely non-critical task.

Output.

Failing task	error_code	error_desc (logged)	Alert sent?
OLE DB Destination	-1071607685	"Violation of PRIMARY KEY constraint..."	yes
Flat File Source	-1071615033	"The column delimiter was not found."	yes
Execute SQL (MERGE)	50000	"Deadlock victim; transaction rolled back."	yes

Rule of thumb. Centralise error handling in a package-scoped OnError handler that logs the system error variables to a table and alerts once, instead of duplicating failure branches on every task. Keep Propagate = True so the package still reports failure to SSISDB — you want the run marked failed, not silently swallowed.

Interview question on control-flow orchestration

A senior interviewer might ask: "Design an SSIS control flow that loads every file dropped in a folder into staging, only runs the warehouse MERGE when at least one file brought in rows, archives processed files, logs any error to a table with the failing task name, and can be safely re-run after a mid-batch crash without double-loading. Walk me through the tasks, containers, precedence constraints, variables, and event handlers."

Solution Using a Foreach Loop, an expression-gated MERGE, and an OnError audit handler

CONTROL FLOW
════════════
[Execute SQL: init run — INSERT etl.run_log, capture @RunId]
        │ Success
        ▼
[Sequence Container: "Ingest"]
   └─[Foreach Loop (File: \\drop\orders\*.csv)]  → User::CurrentFilePath
        ├─[Execute SQL: skip if already loaded]
        │     IF EXISTS (SELECT 1 FROM stg.load_manifest WHERE file_name = ?)
        │       → set User::AlreadyLoaded = 1
        ├─[Data Flow: load file]          (precedence: @AlreadyLoaded == 0)
        │     Flat File Source → Row Count(User::FileRows) → Derived Column
        │                     → OLE DB Destination: stg.orders
        ├─[Execute SQL: record manifest]  INSERT stg.load_manifest(file_name, rows)
        └─[File System: move to archive]
        │ Success
        ▼
[Execute SQL: MERGE to DW]   (precedence: Success && @TotalRows > 0)
        │ Success
        ▼
[Execute SQL: finalize run — UPDATE etl.run_log SET status='OK']

EVENT HANDLER (OnError, package scope)
   [Execute SQL: INSERT etl.package_errors(...system error vars...)]
   [Send Mail: alert on-call]

-- Idempotency backbone: a load manifest keyed by file name
CREATE TABLE stg.load_manifest (
    file_name   VARCHAR(260) PRIMARY KEY,
    rows_loaded INT          NOT NULL,
    loaded_at   DATETIME2    NOT NULL DEFAULT SYSDATETIME()
);

-- The "already loaded?" guard (Execute SQL Task, single-row result → variable)
SELECT CASE WHEN EXISTS (SELECT 1 FROM stg.load_manifest WHERE file_name = ?)
            THEN 1 ELSE 0 END AS already_loaded;

-- The gated MERGE (runs only when @TotalRows > 0)
MERGE dw.fact_orders AS tgt
USING (SELECT * FROM stg.orders) AS src
   ON tgt.order_id = src.order_id
WHEN MATCHED AND tgt.row_hash <> src.row_hash THEN
    UPDATE SET tgt.total_cents = src.total_cents, tgt.status = src.status,
               tgt.row_hash = src.row_hash, tgt.updated_at = SYSDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (order_id, customer_key, total_cents, status, row_hash)
    VALUES (src.order_id, src.customer_key, src.total_cents, src.status, src.row_hash);

Step-by-step trace.

Step	Mechanism	Result
Init	Execute SQL → @RunId	run row created in etl.run_log
Per file	Foreach + manifest guard	already-loaded files skipped
Load	DFT + Row Count → @FileRows	rows staged; @TotalRows accumulated
Manifest	Execute SQL INSERT	file recorded so re-runs skip it
Gate	precedence Success && @TotalRows > 0	MERGE runs only if data arrived
Error	OnError handler	failure logged with task name; alert sent

After a mid-batch crash (say the process dies after loading file 2 of 5), a re-run re-enters the Foreach Loop, the manifest guard skips files 1 and 2 (already recorded), and processing resumes at file 3 — no double-loading. If zero files bring rows, the MERGE is skipped entirely. Any error anywhere lands one row in etl.package_errors with the failing SourceName and fires a single alert.

Output:

Scenario	Behaviour
Clean run, 5 files	5 loaded, manifested, archived; MERGE runs; run_log = OK
Re-run after crash at file 3	files 1–2 skipped; 3–5 loaded; MERGE runs on remainder
Empty drop folder	loop body never runs; MERGE skipped; run_log = OK (0 rows)
PK violation in DFT	OnError logs task + message; alert sent; package fails

Why this works — concept by concept:

• Foreach Loop + expression-bound connection — one Data Flow Task processes N files by re-evaluating the connection string from User::CurrentFilePath each iteration. No copy-paste of the data flow per file.
• Load manifest for idempotency — recording each processed file in stg.load_manifest and guarding on it makes the whole package safely re-runnable after a partial failure. This is the SSIS answer to "exactly-once" for file ingestion.
• Expression-and-constraint gate — the MERGE only runs when @TotalRows > 0, so an empty night does not burn a maintenance window on a no-op MERGE.
• Package-scoped OnError handler — centralised logging of the system error variables (SourceName, ErrorDescription) means every failure is captured with the failing task named, without failure branches littering the control flow.
• Cost — the manifest is one indexed lookup per file (O(1)); the gate is a variable comparison (free); the error handler only runs on failure. Compared to a naive package that reloads every file every run and always MERGEs, this is O(new files) instead of O(all files), plus zero wasted MERGE passes on empty nights.

ETL
Topic — etl
ETL problems on idempotent file ingestion

Practice →

Design Topic — design Design problems on workflow orchestration

Practice →

---

3. Data Flow — sources, transformations, destinations

The streaming buffer engine: rows flow through memory in batches, and which transform you pick decides whether it streams or stalls

The mental model in one line: the data flow is SSIS's pipeline engine — a source reads rows into fixed-size in-memory buffers, a chain of transformations mutates or routes those buffers, and a destination writes them out — and the single most important property of any transform is whether it is non-blocking (passes buffers straight through: Derived Column, Conditional Split, Lookup), semi-blocking (holds some buffers to reconcile inputs: Merge Join, Union All), or fully blocking (must consume the entire input before emitting anything: Sort, Aggregate) — because blocking transforms buffer the whole dataset in memory and spill to disk under pressure, and they are the first thing a senior engineer profiles when a package is slow. Understanding the buffer model is what separates "I can drag a Sort onto the canvas" from "I removed the Sort by requesting sorted data from the source and the package went from 40 minutes to 4."

Sources and destinations.

• Sources. OLE DB Source (query a SQL table/view/proc), Flat File Source (delimited/fixed-width files), Excel, XML, ADO.NET, ODBC. A source's job is to fill buffers with typed columns; the "external columns → output columns" mapping is where data-type mismatches bite.
• Destinations. OLE DB Destination (the workhorse — use Fast Load for bulk insert), SQL Server Destination (in-process, same box only), Flat File, ADO.NET, OLE DB Command (per-row DML — slow, avoid for volume). Choosing the wrong destination or forgetting Fast Load is the number-one cause of slow SSIS loads.
• The buffer. SSIS allocates fixed-size buffers (default ~10 MB / 10,000 rows, tunable via DefaultBufferMaxRows / DefaultBufferSize / AutoAdjustBufferSize). Wider rows mean fewer rows per buffer; trimming unused columns early keeps buffers dense and the pipeline fast.

The blocking taxonomy — memorise this table.

• Non-blocking (synchronous, row-by-row). Derived Column, Data Conversion, Conditional Split, Multicast, Lookup (in cache), Row Count, Copy Column. Buffers pass straight through; the transform reuses the same buffer. Cheapest; prefer these.
• Semi-blocking (partially async). Merge Join, Merge, Union All, pivot/unpivot. Hold some buffers to reconcile multiple inputs but emit progressively. Moderate memory.
• Fully blocking (asynchronous). Sort, Aggregate, Fuzzy Grouping/Lookup, Term Extraction. Must read every input row before producing any output; allocate new buffers for the whole set; spill to disk when memory runs out. Expensive; eliminate where possible.

The Lookup transformation — the most-used and most-misconfigured transform.

• What it does. For each incoming row, looks up a matching row in a reference dataset (a table or query) by a join key, and adds columns from the reference to the pipeline. The SSIS way to resolve business keys into surrogate keys.
• Cache modes. Full cache (load the entire reference set into memory before the flow starts — fastest lookups, highest memory, blocks at startup), Partial cache (cache on demand, bounded by a memory limit — good for large reference sets with skewed access), No cache (query the reference DB per row — lowest memory, highest latency).
• Match and no-match outputs. A Lookup has a match output (rows that found a reference row) and can redirect no-match rows to their own output instead of failing — the basis of the "insert new dimension members" pattern.
• The classic mistake. Full cache on a 200-million-row reference table, blowing out memory at startup. Use partial or no cache, or restrict the reference query to only the needed columns and rows.

Derived Column, Conditional Split, Merge Join — the everyday transforms.

• Derived Column. Adds or replaces columns using SSIS expressions — string manipulation, casts, null-coalescing, hashing. The place you compute a row_hash, clean whitespace, or build a business date.
• Conditional Split. Routes each row to exactly one of several outputs based on ordered boolean expressions (plus a default). The SSIS CASE — used to split new vs changed vs unchanged rows.
• Merge Join. Joins two sorted inputs (inner/left/full) — semi-blocking, streams if both sources are pre-sorted. The scalable alternative to a full-cache Lookup when the reference set is huge but both inputs can be sorted cheaply (e.g. by the database via ORDER BY + IsSorted).
• Union All. Concatenates multiple inputs into one output — semi-blocking, used to recombine split streams or merge multiple sources.

Worked example — resolving surrogate keys with a Lookup

Detailed explanation. The canonical dimension-resolve: incoming order rows carry a natural customer_code; the warehouse fact needs the surrogate customer_key. A Lookup against dim_customer (full cache, since the dimension is small) adds the key on the match output and redirects unknown customers to a no-match output that inserts a placeholder dimension member. Build it.

• Reference. dbo.dim_customer(customer_key, customer_code, ...), ~50k rows → full cache.
• Join. pipeline customer_code = reference customer_code.
• Add. customer_key to the pipeline.
• No-match. redirect to an "insert unknown member" branch.

Question. Configure a Lookup that resolves customer_code to customer_key and handles unknown customers gracefully.

Input.

Setting	Value
Reference table	dbo.dim_customer (query: only customer_key, customer_code)
Cache mode	Full cache
Join columns	customer_code (pipeline) = customer_code (reference)
Add columns	customer_key
No-match behaviour	Redirect rows to no-match output

Code.

-- Reference query for the Lookup (only the columns needed — keeps the cache small)
SELECT customer_key, customer_code
FROM   dbo.dim_customer;

DATA FLOW
 [OLE DB Source: stg.orders]
        │
        ▼
 [Lookup: dim_customer]
   Cache mode : Full cache
   Join       : orders.customer_code = dim_customer.customer_code
   Add column : customer_key
   No match   : Redirect rows to no match output
        ├── (match)    → [OLE DB Destination: dw.fact_orders]   -- has customer_key
        └── (no match) → [Derived Column: customer_key = -1]
                         → [OLE DB Command: INSERT dbo.dim_customer (placeholder)]
                         → [Union All back into the match stream]  (optional)

-- The OLE DB Command on the no-match branch inserts an "inferred member"
INSERT INTO dbo.dim_customer (customer_code, customer_name, is_inferred)
SELECT ?, 'UNKNOWN', 1
WHERE NOT EXISTS (SELECT 1 FROM dbo.dim_customer WHERE customer_code = ?);

Step-by-step explanation.

1. The reference query selects only customer_key and customer_code — not SELECT *. In full cache mode SSIS loads the entire reference into memory before the flow starts, so trimming to two columns keeps that cache small and startup fast.
2. Full cache is the right mode here because dim_customer is ~50k rows — trivially memory-resident. For a multi-million-row reference you would switch to partial or no cache, or pivot to a Merge Join on sorted inputs.
3. The Lookup joins on customer_code and adds customer_key to matched rows. Matched rows flow to the fact destination already carrying their surrogate key — no per-row database round-trip.
4. Unknown customers (a code not yet in the dimension) go to the no-match output instead of failing the whole buffer. A Derived Column stamps a sentinel customer_key = -1 and an OLE DB Command inserts an inferred member so the code exists next run — the late-arriving-dimension pattern.
5. Optionally the no-match branch is Union All-ed back so those rows still land in the fact with customer_key = -1, to be corrected when the real dimension row arrives. The key design choice is redirect, don't fail — one bad code should not abort the load.

Output.

Incoming customer_code	Lookup result	customer_key	Destination
CUST-0007 (known)	match	4021	fact_orders
CUST-9999 (new)	no match	-1 (inferred)	fact_orders + insert dim member
CUST-0007 (known)	match	4021	fact_orders

Rule of thumb. For Lookups, always restrict the reference query to the needed columns, choose the cache mode by reference size (full for small dimensions, partial/no-cache or Merge Join for huge ones), and redirect no-match rows to an inferred-member branch rather than failing the flow. A Lookup that fails on the first unknown key is a fragile load.

Worked example — Derived Column + Conditional Split for insert/update routing

Detailed explanation. Incremental dimension load: staged rows must be split into new (insert), changed (update), and unchanged (ignore). A Derived Column computes a hash of the tracked attributes, a Lookup fetches the existing hash, and a Conditional Split routes on the comparison. Build the transform chain.

• Hash. Derived Column builds row_hash from the tracked columns.
• Existing hash. Lookup against the current dimension returns the stored row_hash (no-match = new).
• Split. Conditional Split: no-match → insert; hash differs → update; hash equal → ignore.

Question. Configure the Derived Column and Conditional Split so each staged row is routed to insert, update, or ignore.

Input.

Element	Value
Tracked columns	customer_name, segment, country
Hash expression	concatenate + cast, feed a Script/Checksum or T-SQL HASHBYTES upstream
Lookup	on customer_code, returns existing_hash, no-match redirected
Split conditions	isnull(existing_hash) → New; row_hash != existing_hash → Changed; else Unchanged

Code.

# Derived Column: build a comparable string then hash it
# (SSIS has no native SHA; compute a stable concatenation and hash it in SQL,
#  or use a Script Component. Here we build the canonical string in SSIS.)
attr_string = (DT_STR,4000,1252)
    (TRIM(customer_name) + "|" + TRIM(segment) + "|" + TRIM(country))

DATA FLOW
 [OLE DB Source: stg.dim_customer_incoming]
        │
        ▼
 [Derived Column: attr_string]           -- canonical concatenation
        │
        ▼
 [Lookup: dim_customer (returns existing_hash)]   No match → redirect
        │
        ▼
 [Conditional Split]
    Order  Output name   Condition
      1    New           ISNULL(existing_hash)
      2    Changed       (existing_hash != HashOf(attr_string))
      -    Unchanged     (default output — no condition)
        ├── New       → [OLE DB Destination: INSERT dim_customer]
        ├── Changed   → [OLE DB Command : UPDATE dim_customer]
        └── Unchanged → (Row Count sink, discarded)

-- Compute the hash in the source query so the comparison is exact and cheap
SELECT customer_code, customer_name, segment, country,
       CONVERT(CHAR(64),
         HASHBYTES('SHA2_256',
           CONCAT(TRIM(customer_name), '|', TRIM(segment), '|', TRIM(country))), 2
       ) AS row_hash
FROM   stg.dim_customer_incoming;

Step-by-step explanation.

1. Rather than fight SSIS's lack of a native SHA function, compute row_hash in the OLE DB Source query with T-SQL HASHBYTES('SHA2_256', ...). The pipeline then carries a deterministic 64-char hash of the tracked attributes.
2. The Lookup against the current dimension returns the stored existing_hash for that customer_code. Its no-match output is redirected so that a brand-new code produces a NULL existing_hash rather than failing.
3. The Conditional Split evaluates conditions in order and sends each row to the first matching output. ISNULL(existing_hash) catches brand-new members (route to insert). The next condition, existing_hash != row_hash, catches changed members (route to update).
4. Rows matching neither condition fall through to the default Unchanged output — the dimension row is identical, so nothing is written. Sending unchanged rows to a Row Count sink (and discarding) keeps them out of the expensive DML paths.
5. New rows go to an OLE DB Destination (bulk insert, Fast Load); changed rows go to an OLE DB Command (per-row UPDATE). Because only genuinely changed rows take the slow per-row path, the load stays fast even on a large dimension — the whole point of hashing.

Output.

customer_code	existing_hash	row_hash	Split output	Action
CUST-0007	(null)	a1f3...	New	INSERT
CUST-0012	9bc2...	7de1...	Changed	UPDATE
CUST-0031	4a55...	4a55...	Unchanged	ignore

Rule of thumb. Detect change with a hash of the tracked columns (computed in SQL, not SSIS), route with a Conditional Split ordered new → changed → unchanged, and send only new rows to a Fast Load destination and only changed rows to per-row UPDATE. Hashing turns an O(rows) full-compare into an O(rows) single-column comparison and keeps the update path narrow.

Worked example — Merge Join vs Lookup for a huge reference set

Detailed explanation. When the reference set is too large to full-cache (say a 300-million-row dim_product), a per-row No-Cache Lookup hammers the database and a Full-Cache Lookup blows out memory. The scalable answer is a Merge Join on two sorted inputs: sort both in the database (ORDER BY) and tell SSIS they are pre-sorted, so the Merge Join streams without a blocking Sort. Compare the two approaches.

• Lookup (no cache). One DB query per pipeline row — network-bound, hammers the reference DB.
• Merge Join (sorted). Both inputs sorted by the database; SSIS marks them IsSorted; the join streams. Semi-blocking, no in-SSIS Sort.

Question. Replace a memory-blowing Full-Cache Lookup on dim_product with a streaming Merge Join, and explain how to avoid an SSIS Sort transform.

Input.

Approach	Reference handling	Memory	DB load
Full-cache Lookup	load all 300M rows	huge (spills)	one big scan
No-cache Lookup	per-row query	low	very high (N queries)
Merge Join (sorted)	stream sorted inputs	moderate	two sorted scans

Code.

-- Left input (fact staging) sorted by the join key IN THE DATABASE
SELECT order_id, product_code, quantity, total_cents
FROM   stg.orders
ORDER  BY product_code;      -- DB does the sort; no SSIS Sort transform

-- Right input (product dimension) sorted by the same key
SELECT product_code, product_key, category
FROM   dbo.dim_product
ORDER  BY product_code;

DATA FLOW
 [OLE DB Source: orders  (ORDER BY product_code)]   ── mark IsSorted=True, SortKey(product_code)=1
        │
        │        [OLE DB Source: dim_product (ORDER BY product_code)] ── IsSorted=True
        │                    │
        ▼                    ▼
 [Merge Join: Left outer join on product_code]
        │  (adds product_key, category)
        ▼
 [OLE DB Destination: dw.fact_orders]

# Advanced Editor → Output → set on BOTH sources:
#   IsSorted = True
#   Output Columns → product_code → SortKeyPosition = 1
# (This is a PROMISE to SSIS. If the data isn't actually sorted, the join is wrong.)

Step-by-step explanation.

1. Both sources issue ORDER BY product_code so the database performs the sort — far cheaper than an SSIS Sort transform, which is fully blocking and spills to disk. The database can often satisfy the order from an index with no sort at all.
2. In each source's Advanced Editor, IsSorted = True is set on the output and SortKeyPosition = 1 on product_code. This tells SSIS the stream is already ordered by that key, so the Merge Join can consume it directly without inserting its own Sort.
3. The Merge Join performs a left outer join on product_code, adding product_key and category from the dimension. Because both inputs arrive sorted, it merges them in a single streaming pass — semi-blocking, bounded memory.
4. The critical caveat: IsSorted = True is a promise, not a check. If the underlying query does not actually return rows in that order (e.g. someone removes the ORDER BY), the Merge Join silently produces wrong results. The ORDER BY and the IsSorted flag must always be kept in sync.
5. Compared to a Full-Cache Lookup (300M rows in memory → disk spill) or a No-Cache Lookup (300M individual queries), the sorted Merge Join scans each input once and joins in stream — the scalable pattern for large-reference joins.

Output.

Metric	Full-cache Lookup	No-cache Lookup	Merge Join (sorted)
Memory	very high (spills)	low	moderate
DB queries	1 large scan	N (per row)	2 sorted scans
Blocking	startup block	none	semi-blocking
Scales to 300M ref	no	poorly	yes

Rule of thumb. When a reference set is too big to full-cache, do not reach for a no-cache Lookup — sort both inputs in the database, mark them IsSorted, and use a Merge Join so the join streams. Never insert an SSIS Sort transform to satisfy a Merge Join; push the order into the source query where an index can serve it.

Interview question on the data-flow engine

A senior interviewer might ask: "You inherit an SSIS data flow that loads a 40-million-row fact table and takes 90 minutes. Profiling shows a Sort transform, a Full-Cache Lookup against a 200-million-row dimension, and an OLE DB Command doing per-row inserts. Walk me through the buffer and blocking mechanics that make it slow, and rewrite the data flow to stream."

Solution Using sorted sources + Merge Join + Fast Load, eliminating the blocking Sort and per-row DML

-- 1. Push the sort into the source query (index-backed; no SSIS Sort)
SELECT f.order_id, f.product_code, f.quantity, f.total_cents
FROM   stg.orders AS f
ORDER  BY f.product_code;     -- served by ix_stg_orders_product_code

-- 2. Product dimension, sorted, only the columns needed
SELECT product_code, product_key, category
FROM   dbo.dim_product
ORDER  BY product_code;       -- served by the PK / a covering index

# 3. Rewritten data flow
 [OLE DB Source: orders   ORDER BY product_code]   IsSorted=True (product_code:1)
 [OLE DB Source: product  ORDER BY product_code]   IsSorted=True (product_code:1)
        └──────────────┬──────────────┘
                       ▼
 [Merge Join: left outer on product_code]   -- replaces the Full-Cache Lookup
                       │  adds product_key, category
                       ▼
 [OLE DB Destination: dw.fact_orders]
     Access mode        : Table or view — fast load
     Rows per batch     : 50000
     Max insert commit  : 100000
     Table lock         : checked

# 4. Package-level buffer tuning (Data Flow Task properties)
DefaultBufferMaxRows  = 50000
DefaultBufferSize     = 20971520      # 20 MB
AutoAdjustBufferSize  = True          # SSIS 2016+: size buffers by row width
EngineThreads         = 8

Step-by-step trace.

Bottleneck (before)	Mechanic	Fix (after)
SSIS Sort transform	fully blocking; buffers whole set; spills to disk	ORDER BY in source; DB/index sorts
Full-Cache Lookup (200M)	loads entire ref to memory at startup	Merge Join on sorted inputs — streams
OLE DB Command inserts	per-row round trip (O(rows) latency)	OLE DB Destination Fast Load — bulk batches
Small buffers	many buffer swaps	AutoAdjustBufferSize + wider buffers

After the rewrite, the fully blocking Sort disappears (the database serves the order from an index), the Full-Cache Lookup is replaced by a semi-blocking Merge Join that never materialises the 200M-row dimension, and the per-row OLE DB Command becomes a Fast Load OLE DB Destination doing 50k-row batches. The run drops from ~90 minutes to single-digit minutes, and memory stays flat instead of spilling.

Output:

Metric	Before	After
Runtime	~90 min	~6 min
Peak memory	high (Sort + 200M cache spill)	moderate (streaming)
Blocking transforms	2 (Sort, full-cache)	0 fully blocking (1 semi)
Destination mode	per-row OLE DB Command	Fast Load bulk batches
DB round trips	N (per row)	2 sorted scans + bulk insert

Why this works — concept by concept:

• Eliminate the fully blocking Sort — a Sort transform reads every input row before emitting any, buffering the whole 40M-row set and spilling to disk. Pushing ORDER BY into the source lets an index serve the order with zero SSIS-side buffering.
• Merge Join instead of Full-Cache Lookup — full cache materialises the entire 200M-row dimension in memory at startup; a sorted Merge Join streams both inputs in one pass with bounded memory. Same join result, no memory blowout.
• Fast Load destination — the OLE DB Command runs one INSERT per row (network latency × N). Switching to the OLE DB Destination in Table-or-view Fast Load mode batches rows into bulk-insert commits, collapsing N round trips into O(rows / batch).
• Buffer tuning — AutoAdjustBufferSize and a larger DefaultBufferSize pack more rows per buffer, cutting the number of buffer handoffs and letting the pipeline threads (EngineThreads) stay busy.
• Cost — two index-served sorted scans plus a streaming join plus bulk inserts is O(rows) with a tiny constant and flat memory, versus the original O(rows) with disk-spill sorts, a 200M-row memory cache, and per-row network latency. The engineering is a query rewrite plus three property changes — hours of work for a 15× speedup.

Data transformation
Topic — data-transformation
Data-transformation problems on lookups and joins

Practice →

Database Topic — database Database problems on streaming pipelines

Practice →

---

4. SSISDB, the project deployment model, and execution

From a folder of .dtsx files to a governed server asset: the .ispac, the catalog, environments, and execution stored procedures

The mental model in one line: ssisdb is the SQL Server database and Integration Services Catalog that hosts deployed SSIS projects under the project deployment model — you build the project into a single .ispac file, deploy it into a catalog folder, bind its parameters to environment-specific values via environments and environment variables, execute it through the catalog.create_execution / set_execution_parameter_value / start_execution stored-procedure trio, and observe every run through the catalog.executions, catalog.event_messages, and catalog.execution_data_statistics views — and knowing this catalog is the difference between "I can build a package in Visual Studio" and "I run SSIS in production and debug it at 3 AM." The project deployment model (default since SQL Server 2012) replaced the older package deployment model precisely because it made parameters, environments, and centralised logging first-class.

Project vs package deployment model.

• Package deployment model (legacy). Each .dtsx is deployed and configured individually; configuration lives in XML config files, SQL config tables, or environment variables scattered per package. Hard to govern; the pre-2012 default.
• Project deployment model (current). The whole project — all packages plus shared connection managers and parameters — builds into one .ispac and deploys as a unit into the SSISDB catalog. Parameters replace the old configuration sprawl; environments provide per-stage values. This is what every modern SSIS shop uses.
• The .ispac. A single deployable artifact (a zip of the project's packages and metadata). Build it in Visual Studio / SSDT; deploy it with the deployment wizard, PowerShell, or catalog.deploy_project.

The SSISDB catalog — the structure.

• Folders. Top-level organisation in the catalog (SSISDB > MyFolder). Projects and environments live inside folders.
• Projects and packages. A deployed project holds its packages and its parameters. Project parameters apply across packages; package parameters are scoped to one.
• Parameters. Typed inputs (String, Int32, Boolean, ...) with default values, marked required or sensitive (encrypted). Bound at execution time to literals or environment variables.
• Environments and environment variables. An environment is a named set of environment variables (e.g. DEV with DbServer = dev-sql, PROD with DbServer = prod-sql). A project references an environment and maps its parameters to environment variables — so the same .ispac runs against dev or prod purely by choosing the environment reference.

Executing a project from T-SQL — the stored-procedure trio.

• catalog.create_execution. Creates an execution instance for a package and returns an execution_id. Optionally attaches an environment reference so parameter values resolve from environment variables.
• catalog.set_execution_parameter_value. Overrides a parameter for this execution (including built-in parameters like LOGGING_LEVEL and SYNCHRONIZED).
• catalog.start_execution. Starts the created execution. With SYNCHRONIZED = 1 the call blocks until the package finishes and surfaces the final status.

Logging and observability — the views you query at 3 AM.

• catalog.executions. One row per run: execution_id, status (1 created, 2 running, 7 succeeded, 3/4 cancelled/failed), start/end times, the package and project. Your first stop.
• catalog.event_messages. Per-run event and error messages (filter by operation_id = execution_id, message_type for errors). Where the actual error text lives.
• catalog.execution_data_statistics. Rows sent between data-flow components per execution — the "where did the rows go / which component is the bottleneck" view.
• Logging level. BASIC (default), PERFORMANCE, VERBOSE, NONE, plus customised levels. Set per execution via set_execution_parameter_value on LOGGING_LEVEL. VERBOSE for debugging, BASIC for steady state (VERBOSE is expensive).

Worked example — deploy an .ispac and bind an environment

Detailed explanation. The canonical deployment: build the project to Orders.ispac, deploy it into an SSISDB folder, create DEV and PROD environments with the connection values, and map the project's parameters to environment variables so the same artifact runs in either stage. Do it in T-SQL / PowerShell.

• Deploy. Orders.ispac → catalog folder ETL.
• Environments. DEV, PROD, each with DbServer and BatchDate variables.
• Reference + mapping. Project references each environment; parameters DbServer / BatchDate map to the environment variables.

Question. Deploy the project and set up environment-based parameterisation for dev and prod.

Input.

Object	Value
Artifact	Orders.ispac
Folder	ETL
Project parameters	DbServer (String), BatchDate (String)
Environments	DEV, PROD

Code.

# 1. Deploy the .ispac with PowerShell (SqlServer module)
$ispac = "C:\build\Orders.ispac"
$server = "prod-sql\SSISDB"
Import-Module SqlServer
$cat = (Get-Item "SQLSERVER:\SSIS\prod-sql\Default\Catalogs\SSISDB")
# Ensure folder exists
$folder = $cat.Folders["ETL"]
if (-not $folder) { $folder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($cat, "ETL", "ETL projects"); $folder.Create() }
[byte[]] $bytes = [System.IO.File]::ReadAllBytes($ispac)
$folder.DeployProject("Orders", $bytes)

-- 2. Create environments and variables in SSISDB
EXEC catalog.create_environment          @folder_name = N'ETL', @environment_name = N'DEV';
EXEC catalog.create_environment          @folder_name = N'ETL', @environment_name = N'PROD';

EXEC catalog.create_environment_variable
     @folder_name=N'ETL', @environment_name=N'DEV',
     @variable_name=N'DbServer', @data_type=N'String', @sensitive=0,
     @value=N'dev-sql', @description=N'target SQL instance';

EXEC catalog.create_environment_variable
     @folder_name=N'ETL', @environment_name=N'PROD',
     @variable_name=N'DbServer', @data_type=N'String', @sensitive=0,
     @value=N'prod-sql', @description=N'target SQL instance';

-- 3. Reference each environment from the project and map parameters to variables
DECLARE @ref_id BIGINT;
EXEC catalog.create_environment_reference
     @folder_name=N'ETL', @project_name=N'Orders',
     @environment_name=N'PROD', @reference_type=N'R',   -- relative reference
     @reference_id=@ref_id OUTPUT;

EXEC catalog.set_object_parameter_value
     @object_type=20,                         -- 20 = project parameter
     @folder_name=N'ETL', @project_name=N'Orders',
     @parameter_name=N'DbServer',
     @value_type=N'R',                        -- R = referenced (from environment variable)
     @parameter_value=N'DbServer';

Step-by-step explanation.

1. The PowerShell step reads Orders.ispac as bytes and calls DeployProject into the ETL folder, creating the folder first if needed. Deployment is a single transactional operation — the whole project lands or none of it does.
2. catalog.create_environment creates the DEV and PROD environments inside the folder. Environments are containers; they hold variables but no values yet.
3. catalog.create_environment_variable populates each environment with DbServer (and similarly BatchDate). @sensitive=1 would encrypt the value (used for passwords); DbServer is non-sensitive.
4. catalog.create_environment_reference links the Orders project to an environment. A relative reference (R) resolves by name within the same folder, so promoting the project between servers keeps working.
5. catalog.set_object_parameter_value with @value_type='R' binds the project parameter DbServer to the environment variable of the same name — not a literal. Now selecting the PROD reference at execution time makes DbServer resolve to prod-sql; selecting DEV makes it dev-sql. One .ispac, environment-driven.

Output.

Stage	Environment reference	DbServer resolves to
Dev run	DEV	dev-sql
Prod run	PROD	prod-sql
Same .ispac	—	no rebuild needed

Rule of thumb. Never hard-code connection strings or batch dates in a package — expose them as project parameters, bind them to environment variables via an environment reference, and switch dev/prod by choosing the reference at execution time. The .ispac you test in dev is byte-identical to the one you run in prod.

Worked example — execute a package via the catalog stored procedures

Detailed explanation. Run the deployed Load.dtsx from T-SQL: create an execution against the PROD environment reference, bump the logging level, override a runtime parameter, and start it synchronously so the calling job sees the final status. This is exactly what a SQL Agent job step (or an ADF Stored Procedure activity) does under the hood.

• Create. catalog.create_execution with the environment reference → @exec_id.
• Configure. set_execution_parameter_value for LOGGING_LEVEL and SYNCHRONIZED, and a package parameter override.
• Start. catalog.start_execution @exec_id.
• Check. read catalog.executions for the status.

Question. Write the T-SQL that executes Load.dtsx in the PROD environment with verbose logging, overriding BatchDate, and returns the final status.

Input.

Element	Value
Folder / project / package	ETL / Orders / Load.dtsx
Environment reference	PROD
Logging level	3 (VERBOSE)
Synchronized	1 (block until done)
Parameter override	BatchDate = '2026-08-03'

Code.

DECLARE @exec_id BIGINT;

-- 1. Create the execution, attaching the PROD environment reference
EXEC catalog.create_execution
     @folder_name      = N'ETL',
     @project_name     = N'Orders',
     @package_name     = N'Load.dtsx',
     @reference_id     = @prod_ref_id,     -- the environment reference id for PROD
     @use32bitruntime  = 0,
     @execution_id     = @exec_id OUTPUT;

-- 2a. Verbose logging for this run (parameter object_type 50 = system parameter)
EXEC catalog.set_execution_parameter_value
     @exec_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=3;

-- 2b. Run synchronously so the Agent job step gets the final status
EXEC catalog.set_execution_parameter_value
     @exec_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1;

-- 2c. Override a package parameter (object_type 30 = package parameter)
EXEC catalog.set_execution_parameter_value
     @exec_id, @object_type=30, @parameter_name=N'BatchDate', @parameter_value=N'2026-08-03';

-- 3. Start it (blocks because SYNCHRONIZED = 1)
EXEC catalog.start_execution @exec_id;

-- 4. Read the final status
SELECT execution_id, status,   -- 7 = succeeded, 4 = failed
       start_time, end_time,
       DATEDIFF(SECOND, start_time, end_time) AS duration_s
FROM   catalog.executions
WHERE  execution_id = @exec_id;

Step-by-step explanation.

1. catalog.create_execution creates the execution instance and returns @exec_id. Passing @reference_id for the PROD environment means every parameter bound to an environment variable resolves from PROD — the connection strings point at prod without any literal in the call.
2. The first set_execution_parameter_value sets LOGGING_LEVEL = 3 (VERBOSE) using @object_type = 50 (system parameter). Verbose logging captures per-component detail — good for a debug run, expensive for steady state.
3. The second sets SYNCHRONIZED = 1. Without this, start_execution returns immediately and the run continues asynchronously; a SQL Agent job step would report success even if the package later failed. SYNCHRONIZED = 1 makes the call block and surface the real outcome.
4. The third overrides the package parameter BatchDate (@object_type = 30) with a literal for this run — how you inject a per-run value that is not environment-fixed (e.g. a re-run for a specific date).
5. start_execution runs the package; because it is synchronized, control returns only when the package finishes. The final SELECT from catalog.executions reports status = 7 (succeeded) or 4 (failed) plus timing — exactly what the wrapping job or ADF activity checks.

Output.

execution_id	status	duration_s	Interpretation
480213	7	214	succeeded
480219	4	12	failed (check event_messages)
480225	2	(null)	still running (async; SYNCHRONIZED=0)

Rule of thumb. Drive SSIS from T-SQL with the create_execution / set_execution_parameter_value / start_execution trio, always set SYNCHRONIZED = 1 when a caller needs the real status, and bump LOGGING_LEVEL to VERBOSE only for the run you are debugging. This is the same path SQL Agent and ADF use — knowing it lets you script and schedule SSIS anywhere.

Worked example — reading the catalog logs after a failure

Detailed explanation. A package failed overnight. The senior move is to go straight to the SSISDB catalog views: find the failed execution, pull its error messages, and inspect the data-flow row statistics to see where the rows stopped. Write the diagnostic queries.

• Find. the failed execution(s) in the last 24h from catalog.executions.
• Diagnose. the error text from catalog.event_messages.
• Locate. the failing component and row counts from catalog.execution_data_statistics.

Question. Write the three queries a data engineer runs to diagnose an overnight SSIS failure.

Input.

View	Purpose
catalog.executions	run status, timing, which package
catalog.event_messages	error / warning message text
catalog.execution_data_statistics	rows sent between data-flow components

Code.

-- 1. Which runs failed in the last 24 hours? (status 4 = failed)
SELECT execution_id, folder_name, project_name, package_name,
       start_time, end_time, status
FROM   catalog.executions
WHERE  status = 4
  AND  start_time > DATEADD(DAY, -1, SYSDATETIMEOFFSET())
ORDER  BY start_time DESC;

-- 2. The error messages for one failed execution (message_type 120 = Error)
SELECT em.message_time, em.message_source_name, em.message
FROM   catalog.event_messages AS em
WHERE  em.operation_id = 480219          -- the failed execution_id
  AND  em.message_type = 120             -- 120 = Error (110 = Warning)
ORDER  BY em.message_time;

-- 3. Where did the rows stop? Row counts between data-flow components
SELECT ds.source_component_name,
       ds.destination_component_name,
       SUM(ds.rows_sent) AS rows_sent
FROM   catalog.execution_data_statistics AS ds
WHERE  ds.execution_id = 480219
GROUP  BY ds.source_component_name, ds.destination_component_name
ORDER  BY rows_sent DESC;

Step-by-step explanation.

1. Query 1 lists every failed run (status = 4) in the last day with the folder/project/package so you know which package failed and when. This is the triage entry point — start from the execution, not the file.
2. Query 2 pulls the actual error text from catalog.event_messages, filtered by operation_id (which equals the execution_id) and message_type = 120 (Error). message_source_name names the component that raised it — often the exact task or data-flow element.
3. Query 3 uses catalog.execution_data_statistics to show how many rows moved between each pair of data-flow components. If the Flat File Source sent 40,000 rows to the Lookup but the Lookup sent 0 downstream, the failure is at the Lookup — the row-count deltas localise the break.
4. message_type values matter: 120 is Error, 110 is Warning, 70 is Information. Filtering to Errors first, then broadening to Warnings, is the efficient way to read a noisy VERBOSE log.
5. Together these three queries answer "what failed, why, and where the rows stopped" without opening Visual Studio — the production-debugging workflow that separates operators from designers.

Output.

Query	Yields	Example finding
executions	failed runs	Load.dtsx failed 02:14, status 4
event_messages	error text	"Violation of PRIMARY KEY constraint 'pk_fact_orders'" at OLE DB Destination
data_statistics	row deltas	Source→Lookup: 40,000; Lookup→Dest: 39,998 (2 rows failed insert)

Rule of thumb. Debug SSIS failures from the SSISDB catalog, not the designer: catalog.executions for what failed, catalog.event_messages (filter message_type = 120) for why, and catalog.execution_data_statistics for where the rows stopped. Bookmark these three queries — they are your 3 AM runbook.

Interview question on SSISDB and deployment

A senior interviewer might ask: "You need to promote an SSIS project from dev to prod with different connection strings and a per-run batch date, schedule it nightly, and be able to diagnose any failure without opening Visual Studio. Walk me through the deployment model, environments, the execution path, scheduling, and the logging views — end to end."

Solution Using project deployment + environments + a scheduled synchronized execution + catalog logging

-- 1. Deploy (via wizard/PowerShell) then wire environments (abbreviated)
EXEC catalog.create_environment @folder_name=N'ETL', @environment_name=N'PROD';
EXEC catalog.create_environment_variable @folder_name=N'ETL', @environment_name=N'PROD',
     @variable_name=N'DbServer', @data_type=N'String', @sensitive=0, @value=N'prod-sql';
-- reference PROD from the Orders project and bind DbServer (as in the earlier example)

-- 2. A reusable "run this package in PROD" wrapper proc for the Agent job
CREATE OR ALTER PROCEDURE etl.run_orders_load @batch_date DATE AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @exec_id BIGINT, @ref BIGINT;

    SELECT @ref = reference_id
    FROM   catalog.environment_references er
    JOIN   catalog.projects p ON p.project_id = er.project_id
    WHERE  p.name = N'Orders' AND er.environment_name = N'PROD';

    EXEC catalog.create_execution
         @folder_name=N'ETL', @project_name=N'Orders', @package_name=N'Load.dtsx',
         @reference_id=@ref, @execution_id=@exec_id OUTPUT;

    EXEC catalog.set_execution_parameter_value @exec_id, 50, N'SYNCHRONIZED', 1;
    EXEC catalog.set_execution_parameter_value @exec_id, 50, N'LOGGING_LEVEL', 1;  -- BASIC
    EXEC catalog.set_execution_parameter_value @exec_id, 30, N'BatchDate',
         @parameter_value = @batch_date;

    EXEC catalog.start_execution @exec_id;

    -- surface failure to the Agent job
    IF (SELECT status FROM catalog.executions WHERE execution_id = @exec_id) = 4
        THROW 50000, N'Orders Load failed — see catalog.event_messages', 1;
END;

-- 3. SQL Agent job step (T-SQL subsystem) runs the wrapper nightly at 02:00
EXEC etl.run_orders_load @batch_date = CONVERT(DATE, SYSDATETIME());

Step-by-step trace.

Stage	Mechanism	Result
Deploy	.ispac → SSISDB folder ETL	project governed centrally
Parameterise	environment PROD + reference + binding	DbServer = prod-sql at run time
Wrap	etl.run_orders_load proc	one call runs the package synchronously
Schedule	SQL Agent job, nightly 02:00	@batch_date = today
Fail-fast	THROW on status 4	Agent step fails; alert fires
Diagnose	catalog views	error + row-stop located without SSDT

After deployment, the nightly Agent job calls etl.run_orders_load with today's date. The wrapper resolves the PROD environment reference (so connections point at prod), runs the package synchronized at BASIC logging, and THROWs if the run failed — which fails the Agent step and triggers the operator alert. Any failure is then diagnosed purely from catalog.executions + catalog.event_messages + catalog.execution_data_statistics.

Output:

Concern	Answer
Dev→prod config	environment reference; no literal in package
Per-run value	BatchDate overridden per execution
Scheduling	SQL Agent T-SQL step calling the wrapper proc
Failure surfacing	SYNCHRONIZED=1 + THROW on status 4
Diagnosis	three catalog queries, no Visual Studio

Why this works — concept by concept:

• Project deployment model + .ispac — the whole project deploys as one governed unit into SSISDB, so parameters and connections live in the catalog, not in scattered config files.
• Environments + references — one .ispac runs in dev or prod by choosing the environment reference; connection strings resolve from environment variables, never from hard-coded literals.
• Synchronized execution + THROW — SYNCHRONIZED = 1 makes the wrapper proc block for the real status, and THROW on status 4 propagates failure to the SQL Agent job so the alert actually fires (the silent-async trap avoided).
• Catalog logging views — executions / event_messages / execution_data_statistics make every run observable and every failure diagnosable from T-SQL, which is what "debug without opening Visual Studio" actually means.
• Cost — deployment is one artifact; scheduling is one Agent job; diagnosis is three indexed catalog queries. Compared to the legacy package-deployment sprawl (per-package XML configs, no central log), the project model is O(1) config per project and O(1) query per diagnosis — dramatically lower operational load.

Design
Topic — design
Design problems on deployment and parameterisation

Practice →

Database Topic — database Database problems on job orchestration

Practice →

---

5. Migration to Azure Data Factory

Two honest paths off SSIS: lift-and-shift onto the Azure-SSIS Integration Runtime, or re-platform to Mapping Data Flows

The mental model in one line: migrating ssis to azure data factory is a choice between lift-and-shift — provision an Azure-SSIS Integration Runtime (a managed cluster of Azure VMs that runs the SSIS engine), point it at an SSISDB catalog hosted in Azure SQL Database or Managed Instance, and execute your existing .dtsx packages unchanged via the Execute SSIS Package activity — and re-platform — rebuild the package's logic as a native ADF Mapping Data Flow that scales out on a managed Spark cluster with no SSIS engine at all — and the senior skill is knowing which packages deserve which path and being able to defend the split on cost, refactor effort, and feature parity. Lift-and-shift buys speed and zero refactor at the price of ongoing IR compute; re-platform buys elasticity and a cloud-native footprint at the price of engineering effort and re-validation.

Lift-and-shift — Azure-SSIS Integration Runtime.

• What it is. A fully-managed cluster of Azure VMs, provisioned inside a Data Factory, that hosts the SSIS runtime. You choose node size and count; ADF handles patching and scaling. Your .dtsx runs on it exactly as on-prem.
• The SSISDB in the cloud. The Azure-SSIS IR needs an SSISDB catalog, hosted in Azure SQL Database or Azure SQL Managed Instance. You deploy your .ispac there just as on-prem.
• The Execute SSIS Package activity. An ADF pipeline activity that runs a specified package on the IR, with parameter and environment overrides — the ADF-native way to trigger and schedule your existing packages.
• When it wins. The long tail of stable, cheap, rarely-changed packages, and any package with bespoke Script Components or third-party transforms you cannot cheaply rebuild. Fastest path off on-prem; near-zero refactor.
• The cost. The IR is billed while running (per node-hour). A big always-on IR is expensive; scheduling it to start, run the batch, and stop is the cost-control pattern.

Re-platform — Mapping Data Flows.

• What it is. ADF's visual, code-free data-transformation feature that compiles to and executes on a managed Apache Spark cluster. Sources, transformations (lookup, derived column, join, aggregate, conditional split), and sinks — conceptually the SSIS data flow, but distributed and serverless.
• Why re-platform. Elastic scale-out (Spark parallelism vs one SSIS box), no SSIS engine or IR to keep warm, cloud-native lineage and monitoring, pay-per-use compute. The right home for hot paths that a single SSIS node cannot scale.
• The mapping. SSIS Lookup → Data Flow Lookup; Derived Column → Derived Column; Conditional Split → Conditional Split; Merge Join → Join; OLE DB Destination → Sink. Much SSIS logic maps 1:1; the friction is Script Components, complex expressions, and package-level control flow (which becomes the ADF pipeline).
• When it wins. High-volume, frequently-changed, or scaling-constrained packages where ongoing elasticity and cloud-native operations outweigh the one-time rebuild cost.

The decision — and the interview signal.

• Default to lift-and-shift for the bulk. It is fast and low-risk; most of the estate is stable and cheap.
• Re-platform the hot paths. Reserve the expensive rebuild for packages where scale or change-rate justifies it.
• Retire the dead. (Section 1) — free wins first.
• The senior signal. Naming both paths, refusing a big-bang rewrite, and tying the choice to cost + effort + parity is what interviewers reward. "We'd lift-and-shift everything onto the IR and re-platform the two hot paths" beats both "keep it on-prem" and "rewrite it all in Spark."

ADF orchestration around the migrated work.

• Pipelines and activities. The ADF pipeline replaces the SSIS master package / SQL Agent job — it orchestrates Execute SSIS Package activities, Mapping Data Flow activities, Copy activities, and Stored Procedure activities.
• Triggers. Schedule, tumbling-window, and event triggers replace SQL Agent schedules.
• Integration runtimes. Azure IR (for Mapping Data Flows and Copy), Azure-SSIS IR (for .dtsx), and Self-Hosted IR (to reach on-prem sources during a hybrid transition) — pick the runtime per activity.

Worked example — provision an Azure-SSIS IR and run a .dtsx

Detailed explanation. The lift-and-shift setup: provision an Azure-SSIS IR bound to an Azure-hosted SSISDB, deploy the .ispac to that catalog, and add an Execute SSIS Package activity to an ADF pipeline that runs the package on a schedule. Show the IR provisioning and the pipeline JSON.

• IR. Azure-SSIS IR, Standard_D4_v3, 2 nodes, bound to SSISDB in Azure SQL DB.
• Deploy. Orders.ispac → the cloud SSISDB ETL folder.
• Activity. Execute SSIS Package activity referencing ETL/Orders/Load.dtsx, with a BatchDate parameter.

Question. Provision the IR and define the ADF pipeline that runs the existing package.

Input.

Element	Value
IR name	azure-ssis-ir
Node size / count	Standard_D4_v3 / 2
Catalog	SSISDB on etl-sql.database.windows.net
Package	ETL/Orders/Load.dtsx
Parameter	BatchDate

Code.

# 1. Provision the Azure-SSIS Integration Runtime (Az.DataFactory)
Set-AzDataFactoryV2IntegrationRuntime `
  -ResourceGroupName "rg-etl" -DataFactoryName "adf-etl" `
  -Name "azure-ssis-ir" -Type Managed `
  -Location "West Europe" -NodeSize "Standard_D4_v3" -NodeCount 2 `
  -CatalogServerEndpoint "etl-sql.database.windows.net" `
  -CatalogAdminCredential $sqlCred `
  -CatalogPricingTier "S2" -Edition "Standard"

Start-AzDataFactoryV2IntegrationRuntime `
  -ResourceGroupName "rg-etl" -DataFactoryName "adf-etl" -Name "azure-ssis-ir"

{
  "name": "pl_run_orders_load",
  "properties": {
    "activities": [
      {
        "name": "ExecuteOrdersLoad",
        "type": "ExecuteSSISPackage",
        "typeProperties": {
          "connectVia": { "referenceName": "azure-ssis-ir", "type": "IntegrationRuntimeReference" },
          "packageLocation": {
            "packagePath": "ETL/Orders/Load.dtsx",
            "type": "SSISDB"
          },
          "loggingLevel": "Basic",
          "projectParameters": {
            "BatchDate": { "value": "@formatDateTime(utcNow(),'yyyy-MM-dd')" }
          }
        }
      }
    ],
    "annotations": ["lift-and-shift"]
  }
}

{
  "name": "trg_nightly_0200",
  "properties": {
    "type": "ScheduleTrigger",
    "typeProperties": {
      "recurrence": { "frequency": "Day", "interval": 1, "startTime": "2026-08-03T02:00:00Z", "timeZone": "UTC" }
    },
    "pipelines": [ { "pipelineReference": { "referenceName": "pl_run_orders_load", "type": "PipelineReference" } } ]
  }
}

Step-by-step explanation.

1. Set-AzDataFactoryV2IntegrationRuntime provisions a Managed (Azure-SSIS) IR of 2 Standard_D4_v3 nodes, bound to an SSISDB catalog it will create on the Azure SQL server. Start-...IntegrationRuntime boots the cluster — after which it runs .dtsx exactly like on-prem SSIS.
2. The .ispac is deployed to the cloud SSISDB (via the deployment wizard pointed at etl-sql.database.windows.net, or PowerShell) into the ETL folder — identical to the on-prem deployment step, just a different catalog server.
3. The ADF pipeline's ExecuteSSISPackage activity references the package by its SSISDB path and runs it on azure-ssis-ir (via connectVia). No package logic changes — this is pure lift-and-shift.
4. projectParameters.BatchDate is set with an ADF expression, @formatDateTime(utcNow(),'yyyy-MM-dd'), so each run injects today's date — the ADF equivalent of the T-SQL set_execution_parameter_value override from Section 4.
5. The Schedule Trigger fires the pipeline nightly at 02:00 UTC, replacing the SQL Agent schedule. To control cost, the IR can be started before and stopped after the batch (a second pipeline with Web/PowerShell activities), so you pay node-hours only during the load window.

Output.

Aspect	On-prem SSIS	Lift-and-shift (Azure-SSIS IR)
Package logic	.dtsx	identical .dtsx (unchanged)
Catalog	SSISDB on SQL Server	SSISDB on Azure SQL DB
Scheduler	SQL Agent	ADF Schedule Trigger
Compute	on-prem box	managed IR node-hours
Refactor effort	—	~none

Rule of thumb. Lift-and-shift is a configuration exercise, not a rewrite: provision the Azure-SSIS IR, redeploy the same .ispac to a cloud SSISDB, and trigger it with an Execute SSIS Package activity. Control the IR's cost by starting and stopping it around the batch window rather than leaving it always-on.

Worked example — re-platform a lookup + derived column to a Mapping Data Flow

Detailed explanation. For a hot path, rebuild the SSIS data flow (Flat File Source → Lookup → Derived Column → Conditional Split → Destination) as an ADF Mapping Data Flow. The transforms map almost 1:1; the control flow becomes the ADF pipeline. Show the Mapping Data Flow script (ADF's data-flow DSL).

• Source. delimited file in ADLS Gen2.
• Lookup. against a dim_customer dataset (Delta/SQL).
• Derived Column. compute row_hash and load_ts.
• Conditional Split. new / changed / unchanged.
• Sinks. insert sink and update (alterRow) sink.

Question. Express the re-platformed logic as an ADF Mapping Data Flow.

Input.

SSIS component	Mapping Data Flow equivalent
Flat File Source	source (DelimitedText dataset)
Lookup	lookup transformation
Derived Column	derive transformation
Conditional Split	split transformation
OLE DB Destination / Command	sink (+ alterRow for upsert)

Code.

// ADF Mapping Data Flow script (data-flow DSL)
source(output(
        order_id as long, customer_code as string,
        total_cents as long, status as string
      ),
      allowSchemaDrift: false,
      format: 'delimited') ~> Orders

source(output(
        customer_code as string, customer_key as long, existing_hash as string
      )) ~> DimCustomer

Orders, DimCustomer lookup(Orders@customer_code == DimCustomer@customer_code,
        broadcast: 'auto')                                   ~> LookupCust

LookupCust derive(
        row_hash = sha2(256, concat(coalesce(status,''), '|', toString(total_cents))),
        load_ts  = currentTimestamp()
      )                                                       ~> AddHash

AddHash split(
        isNull(existing_hash),                 // New
        existing_hash != row_hash,             // Changed
        disjoint: false
      )                                         ~> Route@(New, Changed, Unchanged)

Route@New  sink(allowSchemaDrift: false, skipDuplicateMapInputs: true) ~> InsertFact
Route@Changed alterRow(updateIf(true())) ~> MarkUpdate
MarkUpdate sink(deletable:false, insertable:false, updateable:true,
        keys:['order_id']) ~> UpdateFact

{
  "name": "pl_replatform_orders",
  "properties": {
    "activities": [
      {
        "name": "OrdersDataFlow",
        "type": "ExecuteDataFlow",
        "typeProperties": {
          "dataFlow": { "referenceName": "df_orders_load", "type": "DataFlowReference" },
          "compute": { "coreCount": 8, "computeType": "General" }
        }
      }
    ],
    "annotations": ["re-platform"]
  }
}

Step-by-step explanation.

1. The two source transforms replace the SSIS Flat File Source and the Lookup's reference query — Orders reads the delimited file from ADLS, DimCustomer reads the dimension. Schemas are declared inline, just like SSIS external columns.
2. The lookup transform joins Orders to DimCustomer on customer_code — the direct analogue of the SSIS Lookup. broadcast: 'auto' lets Spark broadcast the small dimension (the distributed equivalent of SSIS full cache), and unmatched rows carry nulls (the no-match case).
3. The derive transform computes row_hash with Spark's native sha2(256, ...) and a load_ts — replacing the SSIS Derived Column plus the T-SQL HASHBYTES workaround. Here the hash is first-class in the engine, no round-trip needed.
4. The split transform routes rows exactly like the SSIS Conditional Split: isNull(existing_hash) → New, existing_hash != row_hash → Changed, default → Unchanged. The stream names (New, Changed, Unchanged) become the downstream branch handles.
5. New rows go to an insert sink; changed rows pass through alterRow(updateIf(true())) and an update-only sink keyed on order_id — the Mapping Data Flow way to express insert-vs-update, replacing the OLE DB Destination + OLE DB Command split. The whole thing runs on Spark (coreCount: 8), so it scales out instead of being pinned to one SSIS node.

Output.

SSIS data flow	Mapping Data Flow	Notes
Full-cache Lookup	lookup + broadcast:auto	Spark broadcast = distributed cache
Derived Column + HASHBYTES	derive + sha2()	native hash, no SQL round-trip
Conditional Split	split (New/Changed/Unchanged)	1:1 mapping
Dest + OLE DB Command	insert sink + alterRow/update sink	upsert via keys
single-node engine	Spark cluster (coreCount)	scales out

Rule of thumb. Re-platforming a data flow is mostly a 1:1 transform translation (Lookup→lookup, Derived Column→derive, Conditional Split→split, Destination→sink) that gains Spark scale-out and native functions like sha2(). Budget the real effort for Script Components, exotic expressions, and the control-flow-to-pipeline mapping — those are where re-platform time actually goes.

Worked example — the migration scorecard

Detailed explanation. To defend the lift-and-shift-vs-re-platform split, score each candidate package on the axes that actually decide it: run frequency, runtime cost, change rate, scaling headroom, and bespoke-component presence. The scorecard turns a judgement call into a repeatable rule. Walk through scoring three packages.

• Axes. frequency, avg runtime, change rate, scaling constraint, bespoke components.
• Rule. high frequency + high runtime + scaling-constrained → re-platform; otherwise lift-and-shift; bespoke component → flag.

Question. Score three packages and assign each a migration path with a defensible reason.

Input.

Package	Runs/day	Avg runtime	Changes/yr	Scaling-constrained?	Bespoke component?
nightly_finance	1	12 min	1	no	no
hourly_clickstream	24	35 min	20	yes (single node maxed)	no
weekly_actuarial	0.14	8 min	2	no	yes (Script Component)

Code.

def score_and_route(runs_day, runtime_min, changes_yr, scaling_constrained, bespoke):
    # weighted "re-platform pressure" score
    score = 0
    score += 2 if runs_day >= 6 else 0
    score += 2 if runtime_min >= 30 else 0
    score += 1 if changes_yr >= 12 else 0
    score += 3 if scaling_constrained else 0
    if bespoke:
        return "LIFT-AND-SHIFT (flag: rebuild Script Component before any re-platform)"
    return "RE-PLATFORM to Mapping Data Flow" if score >= 5 else "LIFT-AND-SHIFT to Azure-SSIS IR"

print(score_and_route(1,  12, 1,  False, False))  # nightly_finance
# → LIFT-AND-SHIFT to Azure-SSIS IR
print(score_and_route(24, 35, 20, True,  False))  # hourly_clickstream
# → RE-PLATFORM to Mapping Data Flow
print(score_and_route(0.14, 8, 2, False, True))   # weekly_actuarial
# → LIFT-AND-SHIFT (flag: rebuild Script Component before any re-platform)

Step-by-step explanation.

1. nightly_finance runs once a day, is quick, barely changes, and is not scaling-constrained — its re-platform pressure score is 0. It lifts-and-shifts: the rebuild cost would buy nothing, and it is on the critical path where risk must be minimised.
2. hourly_clickstream runs 24× a day, takes 35 minutes, changes often, and has maxed out a single SSIS node — score 2+2+1+3 = 8, well over the threshold. It re-platforms: Spark scale-out cuts the runtime and the elasticity absorbs the change rate. The rebuild cost is repaid by ongoing compute savings.
3. weekly_actuarial is cheap and infrequent but contains a bespoke Script Component with no direct Mapping Data Flow equivalent. It lifts-and-shifts with a flag: the Script Component must be rebuilt natively before any future re-platform, so re-platforming now would be expensive and risky for little gain.
4. The scoring weights encode the real decision drivers: scaling constraint (weight 3) dominates because it is the one thing lift-and-shift cannot fix — a package that has outgrown a single node stays outgrown on the IR. Frequency and runtime (weight 2 each) capture ongoing cost.
5. The bespoke-component check short-circuits the score: no matter how attractive re-platform looks, custom code that has no engine equivalent makes re-platform a code-rewrite project, so those packages default to lift-and-shift with an explicit flag rather than a silent surprise mid-migration.

Output.

Package	Score	Path	Reason
nightly_finance	0	lift-and-shift	cheap, stable, critical → minimise risk
hourly_clickstream	8	re-platform	scaling-constrained hot path → Spark scale-out
weekly_actuarial	(bespoke)	lift-and-shift + flag	Script Component blocks cheap re-platform

Rule of thumb. Score migration candidates on frequency, runtime, change rate, scaling constraint, and bespoke components, weighting the scaling constraint highest because it is the one problem lift-and-shift cannot solve. Let bespoke components short-circuit to lift-and-shift-with-a-flag so custom code never ambushes the migration mid-flight.

Interview question on SSIS-to-ADF migration

A senior interviewer might ask: "You own the migration of 400 SSIS packages to Azure with a nine-month deadline and a fixed cloud budget. Walk me through the target architecture, how you decide lift-and-shift vs re-platform per package, how you keep the on-prem finance load safe during cutover, how you control the Azure-SSIS IR cost, and what your first 30 days look like."

Solution Using a triaged, phased migration onto Azure-SSIS IR + selective Mapping Data Flow re-platforms

# 1. Triage the 400 packages into three buckets (from Section 1 + the scorecard)
#    retire  : no live consumer          → disable + archive
#    shift   : stable / cheap / bespoke  → Azure-SSIS IR (unchanged .dtsx)
#    replat  : scaling-constrained hot   → ADF Mapping Data Flows
buckets = {"retire": [], "shift": [], "replat": []}
for pkg in inventory:                        # inventory from SSISDB catalog views
    buckets[route(pkg)].append(pkg)          # route() = scorecard from prior example

// 2. Cost-controlled Azure-SSIS IR: start before the batch window, stop after
{
  "name": "pl_ir_window",
  "properties": {
    "activities": [
      { "name": "StartIR", "type": "WebActivity",
        "typeProperties": { "method": "POST",
          "url": "https://management.azure.com/.../azure-ssis-ir/start?api-version=2018-06-01" } },
      { "name": "RunBatch", "type": "ExecutePipeline", "dependsOn": [{ "activity": "StartIR", "dependencyConditions": ["Succeeded"] }],
        "typeProperties": { "pipeline": { "referenceName": "pl_nightly_batch", "type": "PipelineReference" } } },
      { "name": "StopIR", "type": "WebActivity", "dependsOn": [{ "activity": "RunBatch", "dependencyConditions": ["Completed"] }],
        "typeProperties": { "method": "POST",
          "url": "https://management.azure.com/.../azure-ssis-ir/stop?api-version=2018-06-01" } }
    ]
  }
}

// 3. Parallel-run guard for the finance load during cutover (diff old vs new)
{
  "name": "pl_finance_parallel_validate",
  "properties": {
    "activities": [
      { "name": "RunCloudLoad", "type": "ExecuteSSISPackage",
        "typeProperties": { "connectVia": { "referenceName": "azure-ssis-ir", "type": "IntegrationRuntimeReference" },
          "packageLocation": { "packagePath": "ETL/Finance/Load.dtsx", "type": "SSISDB" } } },
      { "name": "DiffRowCounts", "type": "Lookup", "dependsOn": [{ "activity": "RunCloudLoad", "dependencyConditions": ["Succeeded"] }],
        "typeProperties": { "source": { "type": "SqlSource",
          "sqlReaderQuery": "SELECT ABS(a.cnt - b.cnt) AS delta FROM (SELECT COUNT(*) cnt FROM onprem.dw.fact_finance) a CROSS JOIN (SELECT COUNT(*) cnt FROM cloud.dw.fact_finance) b" } } },
      { "name": "FailIfDrift", "type": "IfCondition", "dependsOn": [{ "activity": "DiffRowCounts", "dependencyConditions": ["Succeeded"] }],
        "typeProperties": { "expression": { "value": "@greater(activity('DiffRowCounts').output.firstRow.delta, 0)", "type": "Expression" } } }
    ]
  }
}

Step-by-step trace.

Phase	Weeks	Action
Inventory + triage	1–2	SSISDB catalog inventory → retire / shift / replat buckets
Retire	2	disable + archive the no-consumer packages (free wins)
IR + SSISDB in Azure	2–3	provision Azure-SSIS IR + cloud SSISDB; validate one package
Lift-and-shift bulk	3–20	redeploy .ispacs; wrap in Execute SSIS Package activities
Finance parallel-run	6–8	run cloud + on-prem in parallel; diff; gated cutover
Re-platform hot paths	12–36	rebuild the scaling-constrained packages as Mapping Data Flows

The first 30 days are inventory, triage, retiring dead packages, standing up the Azure-SSIS IR and a cloud SSISDB, and lift-and-shifting a single low-risk package end-to-end to prove the pipeline. The finance load is lift-and-shifted early but guarded by a parallel-run pipeline that diffs on-prem vs cloud row counts and blocks cutover on any drift. The IR is wrapped in a start-run-stop window so it bills only during batch hours, holding the fixed budget. The scaling-constrained hot paths are re-platformed to Mapping Data Flows last, when the bulk is safely in the cloud.

Output:

Concern	Answer
Target architecture	ADF pipelines + Azure-SSIS IR (shift) + Mapping Data Flows (replat)
Per-package decision	scorecard: scaling-constrained hot path → replat; else shift; dead → retire
Finance safety	parallel-run pipeline; row-count diff gates cutover
IR cost control	start-run-stop window; billed only during the batch
First 30 days	inventory, retire, stand up IR+SSISDB, prove one lift-and-shift

Why this works — concept by concept:

• Triage-first, big-bang-never — bucketing all 400 packages into retire / shift / replat before any code work means engineering effort lands only where it pays, and the 9-month deadline is met by making ~85% of the estate a configuration exercise.
• Azure-SSIS IR for the bulk — lift-and-shift runs the existing .dtsx unchanged, so most packages migrate with zero refactor and zero re-validation of business logic — the only fast way to move 400 packages in nine months.
• Parallel-run row-count diff — running cloud and on-prem finance loads side by side and gating cutover on a zero row-count delta is how you honour "don't break finance" while still cutting over. Drift blocks the switch automatically.
• Start-run-stop IR window — the Azure-SSIS IR bills per node-hour, so wrapping it in Web activities that start it before and stop it after the batch keeps a fixed budget intact instead of paying for an always-on cluster.
• Cost — inventory + triage is days; lift-and-shift is O(config) per package; re-platform is confined to the ~15% hot paths (O(hot paths) of engineering). Compared to a full rewrite (O(400 packages)), this phased triage is the difference between a nine-month delivery and a multi-year one — and the parallel-run guard keeps the risk on the critical path near zero.

ETL
Topic — etl
ETL problems on cloud migration pipelines

Practice →

Design
Topic — design
Design problems on SSIS-to-ADF migration

Practice →

---

Cheat sheet — SSIS recipes

• Control flow vs data flow. Control flow = orchestration (tasks wired by precedence constraints: Execute SQL, Data Flow, File System, Execute Process, Script). Data flow = the streaming buffer engine (source → transformations → destination). One control flow hosts many Data Flow Tasks; a control flow with no DFT moves zero rows. Read the control flow to learn the job, open each DFT to learn the data movement.
• Precedence constraint logic. Three value constraints — Success (green), Failure (red), Completion (blue) — optionally combined with a boolean expression (Expression and Constraint). Multiple arrows into one task default to logical AND; switch to logical OR (dashed) when any one satisfied arrow should fire the task. The classic bug: leaving AND when you meant OR, so a cleanup task never runs.
• Blocking taxonomy (memorise). Non-blocking (Derived Column, Conditional Split, Lookup-in-cache, Multicast, Row Count) pass buffers straight through. Semi-blocking (Merge Join, Union All) hold some buffers. Fully blocking (Sort, Aggregate, Fuzzy Lookup) buffer the entire set and spill to disk. Eliminate fully blocking transforms first when tuning — push ORDER BY into the source instead of using a Sort.
• Lookup cache modes. Full cache (load entire reference to memory at startup — fastest, highest memory; only for small dimensions), Partial cache (on-demand, memory-bounded), No cache (per-row query — lowest memory, highest latency). Always restrict the reference query to needed columns; redirect no-match rows to an inferred-member branch instead of failing the flow. For huge references, prefer a Merge Join on sorted inputs over any Lookup.
• Change detection. Compute a row_hash of tracked columns (in the source query via HASHBYTES('SHA2_256', CONCAT(...)), not SSIS), Lookup the stored hash, and Conditional Split ordered New (ISNULL(existing_hash)) → Changed (hash != existing_hash) → Unchanged (default). Fast Load only the New rows; per-row UPDATE only the Changed rows; discard Unchanged.
• Merge Join without a Sort. Add ORDER BY key to both source queries, set IsSorted = True and SortKeyPosition = 1 in each source's Advanced Editor, then join. IsSorted is a promise — if the data isn't actually ordered the join is silently wrong, so keep the ORDER BY and the flag in sync.
• Destination performance. Use OLE DB Destination in Table-or-view Fast Load mode with a batch size (Rows per batch, Maximum insert commit size) for bulk inserts; never use OLE DB Command for volume (it does one round-trip per row). Tune DefaultBufferMaxRows, DefaultBufferSize, and AutoAdjustBufferSize (SSIS 2016+) to pack rows densely.
• Project deployment model. Build the project to a single .ispac, deploy into an SSISDB catalog folder, expose config as project/package parameters (never hard-coded connection strings), and bind them to environment variables via an environment reference so one artifact runs dev or prod by choosing the reference. This replaced the legacy per-package XML-config sprawl.
• Execute from T-SQL. catalog.create_execution (returns execution_id, attach the environment reference_id) → catalog.set_execution_parameter_value (set LOGGING_LEVEL, set SYNCHRONIZED = 1, override package params) → catalog.start_execution. SYNCHRONIZED = 1 makes the caller block for the real status — without it a job step reports success on an async run that later fails.
• Debug from the catalog (3 AM runbook). catalog.executions (status 7 = succeeded, 4 = failed) for what failed; catalog.event_messages filtered message_type = 120 for why (the error text + failing component); catalog.execution_data_statistics for where the rows stopped (row counts between components). No Visual Studio required.
• Event handlers + variables. Centralise error handling in a package-scoped OnError handler that logs System::SourceName / System::ErrorDescription to a table and alerts once; keep Propagate = True so the run is still marked failed. Use EvaluateAsExpression = True variables for dynamic paths/dates (they re-compute on read); a plain-valued variable does not re-evaluate.
• Migration paths to ADF. Lift-and-shift = Azure-SSIS Integration Runtime runs your .dtsx unchanged via the Execute SSIS Package activity (fast, near-zero refactor, pay IR node-hours — start/stop it around the batch to control cost). Re-platform = rebuild the data flow as a Mapping Data Flow on Spark (elastic scale-out, native sha2(), pay-per-use — reserve for scaling-constrained hot paths). Retire dead packages first. Never a big-bang rewrite.
• Migration triage rule. Score each package on frequency, runtime, change rate, scaling constraint (highest weight — the one thing lift-and-shift can't fix), and bespoke components. Scaling-constrained hot path → re-platform; bespoke Script Component → lift-and-shift with a rebuild flag; everything stable and cheap → lift-and-shift; no live consumer → retire. Guard the critical path with a parallel-run row-count diff before cutover.

Frequently asked questions

What is the difference between the control flow and the data flow in SSIS?

The control flow is the orchestration layer of an SSIS package — a workflow graph of tasks (Execute SQL, Data Flow, File System, Execute Process, Script) connected by precedence constraints that fire on Success, Failure, or Completion, optionally gated by an expression. It decides what runs and in what order; a control flow with no Data Flow Task moves zero rows. The data flow is a special task type that opens its own design surface — the actual ETL engine, where a source reads rows into in-memory buffers, transformations mutate or route those buffers, and a destination writes them out. The relationship is one-to-many: a single control flow can contain many Data Flow Tasks plus non-data tasks (truncate staging, call a proc, send mail). Confusing the two — treating the Data Flow Task as "just another box" without realising it hosts an entire streaming pipeline — is the single most common junior mistake and the fastest way to fail the opening SSIS interview question.

What are blocking transformations in SSIS and why do they matter?

Transformations fall into three classes by how they treat the buffer pipeline. Non-blocking transforms (Derived Column, Conditional Split, Lookup in cache, Multicast, Row Count) pass buffers straight through row-by-row — cheapest, prefer them. Semi-blocking transforms (Merge Join, Union All) hold some buffers to reconcile multiple inputs but still emit progressively — moderate memory. Fully blocking transforms (Sort, Aggregate, Fuzzy Grouping/Lookup) must consume the entire input before producing any output, so they buffer the whole dataset in memory and spill to disk under memory pressure. Blocking transforms matter because they are the number-one performance and memory villain in slow packages: a Sort on a 40-million-row set materialises all 40 million rows before emitting the first. The senior fix is usually to eliminate the blocking transform — push ORDER BY into the source query so an index serves the order, or replace a full-cache Lookup on a huge dimension with a Merge Join on sorted inputs — turning a disk-spilling block into a streaming pass.

What is SSISDB and the project deployment model?

SSISDB is the SQL Server database and Integration Services Catalog that hosts deployed SSIS projects, and the project deployment model (default since SQL Server 2012) is how modern SSIS ships to it. You build the whole project — all packages, shared connection managers, and parameters — into a single .ispac file and deploy it as a unit into an SSISDB folder. Configuration lives in typed parameters bound to environment variables via an environment reference, so the same .ispac runs against dev or prod purely by choosing the environment. This replaced the older package deployment model, where each .dtsx was configured individually through scattered XML config files and SQL config tables — a governance nightmare. SSISDB also centralises observability: catalog.executions records every run and its status, catalog.event_messages holds the error text, and catalog.execution_data_statistics shows row counts between data-flow components. Knowing SSISDB is the line between "I can build a package in Visual Studio" and "I run and debug SSIS in production."

How do I debug an SSIS package that failed overnight?

Go straight to the SSISDB catalog views, not the designer. First, query catalog.executions for runs with status = 4 (failed) in the last day — this tells you which package failed and when. Second, query catalog.event_messages filtered by operation_id = <execution_id> and message_type = 120 (Error) — this gives you the actual error text and message_source_name, the component that raised it (often the exact task or data-flow element, e.g. a PK violation at the OLE DB Destination). Third, query catalog.execution_data_statistics for that execution to see the row counts flowing between each pair of data-flow components — if the source sent 40,000 rows to the Lookup but the Lookup sent 0 downstream, the break is at the Lookup. These three queries answer "what failed, why, and where the rows stopped" without opening Visual Studio, and they work for any deployed project. Bump LOGGING_LEVEL to VERBOSE (via catalog.set_execution_parameter_value) only for a targeted re-run, since verbose logging is expensive at steady state.

Should I migrate SSIS to Azure Data Factory by lift-and-shift or re-platform?

Both, chosen per package. Lift-and-shift provisions an Azure-SSIS Integration Runtime — a managed cluster that hosts the SSIS engine — points it at an SSISDB catalog in Azure SQL Database/Managed Instance, and runs your existing .dtsx files unchanged via the Execute SSIS Package activity. It is fast, near-zero refactor, and the right choice for the long tail of stable, cheap, rarely-changed packages and anything with bespoke Script Components; the cost is ongoing IR node-hours (control it by starting/stopping the IR around the batch window). Re-platform rebuilds the data flow as a native ADF Mapping Data Flow that scales out on managed Spark — elastic, cloud-native, pay-per-use — reserved for scaling-constrained hot paths where a single SSIS node has maxed out and the ongoing compute savings repay the one-time rebuild. The senior answer is a triaged split: retire dead packages first, lift-and-shift the bulk, re-platform only the hot paths — never a big-bang rewrite in Spark.

Is SSIS still relevant for data engineers in 2026?

Yes, primarily as inherited infrastructure and as a migration challenge. SSIS shipped free with every SQL Server licence for two decades, so its installed base — thousands of .dtsx packages quietly loading finance, retail, healthcare, and insurance warehouses every night — is enormous and load-bearing, and those packages did not evaporate when the industry moved on. A data engineer joining almost any established enterprise has a real chance of inheriting an SSIS estate, and the countervailing cloud-first mandate is exactly what produces the SSIS-to-ADF migration project that lands on their desk. Beyond that, SSIS is a canonical ETL-fundamentals interview: an interviewer asking about buffers, blocking transforms, lookups, slowly changing dimensions, and deployment parameterisation is really probing whether you understand ETL — SSIS is the shared vocabulary. So while few teams start greenfield projects on SSIS in 2026, understanding it — the control-flow/data-flow split, the buffer model, SSISDB, and the honest migration paths — remains a practical and interview-relevant skill.

Practice on PipeCode

• Drill the ETL practice library → for the incremental-load, idempotent-ingestion, watermarking, and legacy-migration problems that mirror real SSIS work.
• Rehearse on the data-transformation practice library → for the lookup, join, derived-column, and change-detection patterns that make up every SSIS data flow.
• Sharpen the architecture axis with the design practice library → for the estate-triage, deployment, and SSIS-to-ADF migration topology questions senior interviewers open with.
• Layer in the database practice library → for the streaming-pipeline, buffering, and job-orchestration reps that underpin both SSIS data flows and their ADF Mapping Data Flow replacements.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the control-flow / data-flow / SSISDB / migration decision map against real graded inputs.

Turn SSIS knowledge into interview muscle memory

Docs explain the components. PipeCode drills explain the decision — when a fully blocking Sort should become an index-served `ORDER BY`, when a full-cache Lookup should become a Merge Join, when a package should lift-and-shift onto the Azure-SSIS IR versus re-platform to a Mapping Data Flow, and when it should simply be retired. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice ETL problems →
Practice design problems →