SSIS Control Flow: Tasks, Containers, and Precedence Constraints
Why this topic matters
Section titled “Why this topic matters”The control flow decides what runs, in what order, and what happens when something fails. Almost every production SSIS incident — a package that “succeeded” despite a failed step, a notification storm, an email sent before three parallel loads finished, a restart that re-ran work already committed — is a control flow design problem, not a data problem.
It is also the layer people design least deliberately. The data flow gets attention because it is visibly complex; the control flow gets drawn quickly and then quietly governs the failure behaviour of the whole job.
Fundamentals and terminology
Section titled “Fundamentals and terminology”Three building blocks:
| Concept | What it is |
|---|---|
| Task | A unit of work: run SQL, move a file, execute a package, run a data flow |
| Container | Groups tasks and gives them shared scope for variables, transactions, logging, and events |
| Precedence constraint | The arrow between two items, carrying a constraint value, an optional expression, and a combination operator |
Everything in the control flow is an executable. SSIS defines four container types, and it is worth being precise about them because the distinction is often blurred:
| Container | What it holds |
|---|---|
| Task Host | Exactly one task. Not something you add — the runtime provides it around each task |
| Sequence | A static group of tasks and containers |
| For Loop | A control flow repeated while a condition holds |
| Foreach Loop | A control flow repeated once per item from an enumerator |
The package itself, and each event handler, are also containers.
So a task is hosted by a Task Host; a Sequence, For Loop or Foreach Loop is its own container type, not a task wrapped in a Task Host. What they share is that all of them are executables in the control flow graph, which is why tasks and containers alike expose common properties such as FailPackageOnFailure, DisableEventHandlers, TransactionOption, and their own variable scope.
The mental model
Section titled “The mental model”The control flow is a directed graph, not a list. The designer draws it top-to-bottom, which misleads people into reading it as a sequence.
[Prepare staging] │ Success ┌───────┴────────┐ Two branches with no arrow between them ▼ ▼ are ELIGIBLE to run concurrently — they are not ordered top-to-bottom.[Load customers] [Load products] │ │ └───────┬────────┘ Logical AND — both constraints must be satisfied ▼ [Apply to warehouse] │ Failure ▼ [Quarantine + alert]Two rules follow:
- Executables with no path between them are eligible to run concurrently. Sequence is something you assert with an arrow, never something you get by layout. Eligibility is not a guarantee of parallelism: what actually runs at once depends on dependency state, the package’s
MaxConcurrentExecutablescap, the nesting structure, and available scheduler/machine resources. Design as if they may overlap — that is what makes contention bugs real — but never rely on them definitely overlapping for correctness or timing. - Constraint evaluation is per-arrow. A task with several incoming arrows runs when the combination rule (AND/OR) over those arrows is satisfied.
Minimal working example
Section titled “Minimal working example”The smallest useful control flow: prepare, load, apply.
[Execute SQL Task] TRUNCATE TABLE stg.SalesLine │ Success (green) ▼[Data Flow Task] inbound file → stg.SalesLine │ Success (green) ▼[Execute SQL Task] MERGE stg.SalesLine into dbo.FactSalesEven here, one decision is already made for you: if the data flow fails, the merge does not run, because the arrow is Success. Change it to Completion and you would merge a half-loaded staging table — a silent data corruption bug hiding behind an arrow colour.
Practical implementation: the tasks that matter
Section titled “Practical implementation: the tasks that matter”SSIS ships dozens of tasks. A handful cover almost every real package.
Data Flow Task
Section titled “Data Flow Task”The bridge into the pipeline engine. Each one has its own design surface — see Data Flow.
Control-flow-level guidance:
- Prefer several focused Data Flow Tasks over one enormous one. Restart granularity, parallelism, and troubleshooting all improve, because a data flow cannot be restarted partway.
- A single Data Flow Task can hold multiple independent source-to-destination paths, which the engine may run concurrently.
DefaultBufferSize,DefaultBufferMaxRows, andEngineThreadsare properties of this task, not of the package.
Execute SQL Task
Section titled “Execute SQL Task”Runs T-SQL or a stored procedure. This is where set-based work belongs.
Typical uses: truncate or prepare staging; run the MERGE that applies staged rows; read a watermark or batch id into a variable; write batch audit rows.
Two configuration details cause most of the trouble.
ResultSet — None, Single row, Full result set, or XML.
Single row → map each returned column to a variableFull result set → map to ONE variable of type Object, then iterate it with a Foreach Loop using the ADO enumeratorParameter mapping — the placeholder syntax depends on the connection manager type, and mixing them up is a classic failure:
| Connection manager | Placeholder | Parameter name |
|---|---|---|
| OLE DB | ? |
Ordinal: 0, 1, 2 … |
| ADO.NET | @ParamName |
Name: ParamName |
| ODBC | ? |
Ordinal, 1-based: 1, 2 … |
-- OLE DB connection manager: positional parameters, mapped 0,1,2 in orderUPDATE dbo.LoadControlSET LastLoadedOn = ?, RowsLoaded = ?WHERE SourceName = ?;-- ADO.NET connection manager: named parametersUPDATE dbo.LoadControlSET LastLoadedOn = @LoadedOn, RowsLoaded = @RowCountWHERE SourceName = @SourceName;Edge cases worth knowing:
- Output parameters and return values need
Directionset correctly in the mapping, and the SQL must declare them. - A stored procedure that returns multiple result sets only surfaces the first.
DATETIMEmapping between SSIS types and T-SQL types is a recurring source of precision loss — preferDT_DBTIMESTAMPwith explicitDATETIME2on the SQL side.
Execute Package Task
Section titled “Execute Package Task”Runs another package — the basis of master/child orchestration.
| Setting | Meaning |
|---|---|
| Reference type | Project Reference for packages in the same project (normal under the project model); External Reference for file system or another catalog |
| Parameter bindings | Bind child package parameters to the parent’s variables or parameters — how a batch id or run date is passed down |
ExecuteOutOfProcess |
Runs the child in a separate process; isolates memory and crashes, adds startup cost, changes debugging behaviour |
A child package failure propagates to the Execute Package Task, so normal precedence constraints work on it.
Script Task
Section titled “Script Task”A C#/VB task for what no built-in task does: calling a web service, non-trivial file manipulation, custom validation, formatted notifications.
// Variables must be listed in ReadOnlyVariables / ReadWriteVariables,// or acquired explicitly through VariableDispenser with a lock.public void Main(){ try { var runDate = (DateTime)Dts.Variables["User::RunDate"].Value; var archiveRoot = (string)Dts.Variables["User::ArchiveRoot"].Value;
var target = System.IO.Path.Combine(archiveRoot, runDate.ToString("yyyyMMdd")); System.IO.Directory.CreateDirectory(target);
Dts.Variables["User::ArchivePath"].Value = target;
Dts.Events.FireInformation(0, "PrepareArchive", $"Archive folder ready: {target}", string.Empty, 0, ref _fireAgain);
Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception ex) { // Surface it through the normal logging path so OnError handlers see it Dts.Events.FireError(0, "PrepareArchive", ex.Message, string.Empty, 0); Dts.TaskResult = (int)ScriptResults.Failure; }}private bool _fireAgain = true;Discipline that keeps Script Tasks from becoming a liability:
- Always set
Dts.TaskResultexplicitly. Forgetting it produces a task that silently reports success. - Never swallow exceptions. Use
FireErrorso failures reach event handlers and the catalog log. - Do not let it become the application. Script code lives inside package XML: invisible to review, untestable, unreusable. If the same logic appears in three packages, it belongs in a stored procedure or a custom component.
- Locking variables manually via
VariableDispenseris required when the variable list is not known at design time — and is a common source of deadlocks when locks are not released.
File System Task
Section titled “File System Task”Copy, move, rename, delete, create directory — without code. The everyday pattern is process → archive → quarantine:
Foreach Loop (files in inbound folder)├── [Data Flow Task] load the current file├── [Success] File System Task move file → Archive\yyyyMMdd\└── [Failure] File System Task move file → Quarantine\Both source and destination can be driven from variables, which is how date-stamped archive folders happen without hard-coded paths.
Other tasks worth knowing
Section titled “Other tasks worth knowing”| Task | Use it for |
|---|---|
| Bulk Insert Task | Fastest path for a clean flat file straight into a table, with no transformation and no per-row error handling |
| Expression Task | Sets a variable from an SSIS expression without a Script Task |
| FTP Task | Basic transfer; SFTP needs a Script Task or an external tool |
| Send Mail Task | Basic SMTP; many teams call Database Mail from an Execute SQL Task instead |
| Analysis Services Processing Task | Refreshing a model after a warehouse load |
| XML Task | XSLT, XPath, validation, diff/merge on XML documents |
| WMI / Message Queue / Transfer * tasks | Occasional administrative and integration needs |
Containers, scope, and what they actually buy you
Section titled “Containers, scope, and what they actually buy you”Sequence Container
Section titled “Sequence Container”A static grouping. It looks cosmetic and is not:
- Variable scope narrower than package level.
- One place to set
TransactionOptionfor a group of tasks. - Disable as a unit while developing.
- A single checkpoint restart point — including as the documented workaround for loop containers, which do not record checkpoint state.
- A synchronisation boundary — everything inside must finish before the container’s outgoing constraint is evaluated.
That last point is the cleanest way to express “all of these, then that”.
For Loop Container
Section titled “For Loop Container”Repeats while a condition holds, driven by three expressions:
InitExpression: @Counter = 1EvalExpression: @Counter <= @BatchCountAssignExpression: @Counter = @Counter + 1Uses: processing a table in fixed-size batches, bounded retry, walking a date range day by day.
Foreach Loop Container
Section titled “Foreach Loop Container”Repeats once per item from an enumerator — the workhorse container.
| Enumerator | Iterates over |
|---|---|
| Foreach File | Files in a folder matching a mask, optionally recursing |
| Foreach ADO | Rows in an Object variable holding a recordset |
| Foreach From Variable | Items in an array or collection in an Object variable |
| Foreach Item | A hard-coded list defined in the container |
| Foreach Nodelist | XML nodes selected by XPath |
| Foreach ADO.NET Schema Rowset | Schema metadata such as tables or columns |
| Foreach SMO | SQL Server objects, e.g. every database on an instance |
The critical step is Variable Mappings: each iteration writes the current item into a variable — index 0 for the file enumerator, one entry per column for the ADO enumerator.
The metadata-driven load pattern:
[Execute SQL Task] SELECT TableName, LoadQuery FROM dbo.LoadManifest ResultSet: Full result set → User::Manifest (Object) │ ▼[Foreach Loop] ADO enumerator over User::Manifest Variable Mappings: 0 → User::TableName, 1 → User::LoadQuery └── [Data Flow Task] OLE DB Source, access mode "SQL command from variable" = User::LoadQueryOne package loads many tables. Its hard limit: data flow column metadata is fixed at design time, so every iteration must produce the same shape. Genuinely different shapes need separate data flows, a generic staging table, or a different tool.
Variable scope in the control flow
Section titled “Variable scope in the control flow”A variable belongs to the package or to a specific container/task. Inner scopes see outer variables; outer scopes cannot see inner ones.
Package├── User::BatchId visible everywhere└── Foreach Loop Container ├── User::CurrentFilePath visible only inside the loop └── Sequence Container └── User::RowCount visible only inside the sequenceTwo variables with the same name in different scopes shadow one another, and the designer will happily let you create them. A task reads the nearest one in scope, which may not be the one you set. This is a genuinely nasty bug to find.
Practical advice: default to package scope with distinct names. Use container scope only where a loop genuinely needs its own counter, and never reuse a name across scopes.
A realistic end-to-end example
Section titled “A realistic end-to-end example”A fictional nightly retail load, showing orchestration, synchronisation, failure branches, and event handlers together.
MasterLoad.dtsx
[Execute SQL Task] "Open batch" EXEC dbo.usp_EtlBatch_Open @PackageName = ?, @ExecutionId = ?, @BatchId = ? OUTPUT Result → User::BatchId │ Success ▼[Sequence Container] "Reference and dimensions" ├── [Execute Package Task] LoadStoreDim.dtsx ─┐ no arrows between them: ├── [Execute Package Task] LoadProductDim.dtsx ─┤ these three run in parallel └── [Execute Package Task] LoadCalendarDim.dtsx ─┘ │ Success (the container completes only when all three do) ▼[Foreach Loop] "Per-region sales files" Enumerator: Foreach File, @[$Project::InboundFolder], *.csv, fully qualified Mapping: 0 → User::CurrentFilePath │ ├── [Data Flow Task] "Stage sales lines" DelayValidation = True │ │ │ ├── [Success] [File System Task] move → Archive\yyyyMMdd\ │ └── [Failure] [File System Task] move → Quarantine\ │ │ │ ▼ │ [Execute SQL Task] record the quarantined file │ └── (loop continues to the next file either way) │ Success ▼[Execute SQL Task] "Apply to warehouse" EXEC dbo.usp_FactSales_ApplyBatch @BatchId = ? │ ├──[Expression and Constraint]──> [Execute SQL Task] "Raise reject alert" │ @[User::RejectedRows] > @[User::RejectThreshold] │ │ Success ▼[Execute SQL Task] "Close batch" EXEC dbo.usp_EtlBatch_Close @BatchId = ?, @Status = 'Success'
Event handlers (package scope): OnError → INSERT dbo.EtlErrorLog (every error, for diagnosis) OnTaskFailed → EXEC dbo.usp_EtlBatch_Close @Status = 'Failed' (once per failed task)Design decisions worth naming:
- Dimensions are parallel but the fact load waits, expressed by the Sequence Container rather than by hoping the timings work out.
- A bad file quarantines itself and the loop continues, because one region’s broken export should not block the other regions.
- The reject threshold is an explicit branch, so “succeeded while silently discarding 40% of rows” cannot happen quietly.
OnErrorrecords,OnTaskFailedreacts — the distinction that prevents notification storms.
Internal execution behavior
Section titled “Internal execution behavior”How the runtime walks the graph
Section titled “How the runtime walks the graph”At execution the runtime evaluates the package’s executables, starting with those that have no incoming constraints. When an executable finishes, each outgoing constraint is evaluated; a downstream executable becomes eligible when its incoming constraint combination is satisfied. Eligible executables are dispatched subject to MaxConcurrentExecutables.
Two consequences:
- A task with an unsatisfiable constraint simply never runs. It is not an error. A branch that silently did nothing is usually a Failure constraint whose upstream task succeeded.
- Constraint evaluation happens once, when the upstream item completes — not continuously.
Execution results vs propagation
Section titled “Execution results vs propagation”Each executable ends with a result: Success, Failure, or Completion. Three properties then decide what that means for everything above it:
| Property | Level | Effect |
|---|---|---|
FailPackageOnFailure |
Task / container | Does this item’s failure fail the package? Also required for checkpoint restart points |
FailParentOnFailure |
Task / container | Does the failure propagate to the containing container? |
MaximumErrorCount |
Task / container / package | How many errors this item tolerates before it is considered failed. Default 1 |
This is the mechanism people reach for the blue arrow to get. A Completion constraint changes what runs next; it does not stop the failure being a failure. If a step genuinely must be non-fatal, raise its MaximumErrorCount (or handle it in an event handler) as a deliberate decision.
WRONG — the package still fails: [Optional cleanup] ──Completion──> [Continue] ...and the failed cleanup task still errors, still fails the package.
RIGHT: [Optional cleanup] MaximumErrorCount = 2, FailPackageOnFailure = False [Optional cleanup] ──Completion──> [Continue]Event bubbling
Section titled “Event bubbling”Events raised inside a container propagate up the container hierarchy. An OnError handler at package level fires for errors raised anywhere beneath it, unless a child sets DisableEventHandlers = True.
That is convenient and dangerous: a single failing task can raise several errors, and an OnError handler that sends email produces a burst of messages for one incident. Full treatment in Error Handling, Logging, Event Handlers, Transactions, and Checkpoints.
Precedence constraints in depth
Section titled “Precedence constraints in depth”Constraint values
Section titled “Constraint values”| Value | Colour | Runs the next item when |
|---|---|---|
| Success | Green | The previous item succeeded |
| Failure | Red | The previous item failed |
| Completion | Blue | The previous item finished, either way |
Evaluation operations
Section titled “Evaluation operations”| Evaluation operation | Runs when |
|---|---|
| Constraint | The constraint value matches (default) |
| Expression | The expression evaluates to True |
| Expression and Constraint | Both |
| Expression or Constraint | Either |
-- Only reload when the extract actually produced rows@[User::RowsExtracted] > 0
-- Full refresh only on the first of the monthDAY(@[System::StartTime]) == 1
-- Alert only when rejects cross a threshold@[User::RejectedRows] > @[User::RejectThreshold]
-- Skip the expensive step in a "quick run" mode@[$Package::RunMode] != "Quick"Expressions must return Boolean, and SSIS expression typing is strict — comparing a String variable to a number fails validation rather than coercing. Test with the editor’s Evaluate Expression button before deploying.
Multiple constraints into one task
Section titled “Multiple constraints into one task”| Setting | Arrow style | Meaning |
|---|---|---|
| Logical AND | Solid | Every incoming constraint must be satisfied |
| Logical OR | Dotted | Any one satisfied constraint is enough |
The classic bug: three parallel loads point at “send success email” with Logical OR left on, so the email fires after the first branch finishes.
A subtler one: mixing Success and Failure arrows into the same task under Logical AND makes it unreachable, because the upstream items cannot both succeed and fail.
Parallel execution
Section titled “Parallel execution”SSIS may run control flow items concurrently whenever no path forces sequence. Treat that as eligibility, not a schedule: the runtime dispatches executables whose incoming constraints are satisfied, bounded by MaxConcurrentExecutables and by what the machine can actually run.
MaxConcurrentExecutables— a package property capping concurrent executables. Default-1means “logical processors + 2”. Lower it when packages compete on a busy server; raise it only with evidence.EngineThreads— a Data Flow Task property influencing pipeline threads. A hint, not a hard limit, and unrelated to control flow concurrency.
Parallelism is a design decision, not a late tuning knob:
| Risk | What happens | Mitigation |
|---|---|---|
| Same destination table | Lock contention and deadlocks, especially with TABLOCK fast load |
Serialise those loads, or load to separate staging tables and apply once |
| Same busy source | Saturating the source makes everything slower than sequential | Stagger, or read from a replica |
| Implicit dependency | Fact load starts before dimensions finish | Draw the constraint; never rely on timing |
| Memory | Several data flows allocating buffers at once | Cap concurrency; measure |
The clean shape is: parallel independent extracts → synchronisation point (Sequence Container or Logical AND) → sequential dependent work.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”Transactions. TransactionOption (Required / Supported / NotSupported) is set on packages, containers, and tasks. Required starts a transaction if none exists; Supported (the default) joins one if present. Distributed transactions require MSDTC running and reachable on every machine involved — the single most common reason SSIS transactions work in development and fail in production.
Checkpoints. Restart points are containers and tasks with FailPackageOnFailure = True. Critically, checkpoint data is not saved for For Loop and Foreach Loop containers — a restarted package re-runs the whole loop. Microsoft’s documented workaround is to wrap the loop in a Sequence Container, but the loop body still re-runs, so the work inside must tolerate it.
Transactions and checkpoints together interact badly: a transacted container that committed before the failure can be re-run after restart, repeating committed work. Microsoft explicitly warns against combining them casually.
ForceExecutionResult. Forces a task or container to report Success, Failure, or Completion regardless of what happened. Genuinely useful for testing failure branches and checkpoint behaviour — and genuinely dangerous if left set. Add it to your pre-deployment review checklist.
Disable. Disabled items are skipped and report success. A disabled task left in a deployed package is invisible in the log and looks like a step that ran.
Nested containers. Containers inside containers inside loops become unreadable fast, and event/transaction/checkpoint semantics compound. Beyond two levels, extract a child package.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”Anti-pattern: Completion constraints as error suppression
Section titled “Anti-pattern: Completion constraints as error suppression”BAD: [Load file] ──Completion──> [Apply to warehouse] ──> [Mark success]
Load fails → apply runs against a half-loaded staging table → package still reports failure → warehouse is now wrong AND the job looks failed for the wrong reasonBETTER: [Load file] ──Success────> [Apply to warehouse] ──> [Mark success] └─Failure────> [Quarantine + record + alert]Anti-pattern: the arrow that means “wait for everything”
Section titled “Anti-pattern: the arrow that means “wait for everything””BAD: three parallel loads, all pointing at [Send success email], Multiple constraints = Logical OR → email after the first one
BETTER: wrap the three in a Sequence Container, one arrow out of the container. The container completes only when all children do, and the intent is visible on the canvas.Other frequent mistakes
Section titled “Other frequent mistakes”- Business logic in Script Tasks that would be clearer, testable, and reusable as a stored procedure.
- Relying on layout for order — two tasks drawn one above the other with no arrow between them are eligible to run concurrently, in either order, or overlapping.
- A loop container as a checkpoint restart point, which does not work.
ForceExecutionResultleft on after testing.- Raising
MaxConcurrentExecutablesto “speed things up” without measuring, then hitting lock contention. - Not mapping Foreach Loop variables, producing a loop that iterates the right number of times using stale values.
Failure modes, recovery, and troubleshooting
Section titled “Failure modes, recovery, and troubleshooting”| Symptom | Likely cause | Check |
|---|---|---|
| A branch never runs | Constraint never satisfied (e.g. Failure arrow, upstream succeeded) | Constraint value and evaluation operation |
| Downstream ran too early | Logical OR where AND was meant | Multiple-constraints setting on the target task |
| Package fails although the step “was optional” | Completion constraint ≠ error suppression | MaximumErrorCount, FailPackageOnFailure |
| Package succeeds although a task failed | MaximumErrorCount raised, or item disabled, or ForceExecutionResult set |
Task properties |
| Loop runs the right number of times, wrong data | Variable Mappings missing or wrong index | Foreach Loop → Variable Mappings |
| Loop uses the same file every iteration | Connection manager not driven by a property expression | Connection manager Expressions collection |
| Restart re-runs the whole loop | Checkpoints are not recorded for loop containers | Restructure; wrap in Sequence Container |
| Transaction errors only in production | MSDTC not running or blocked by firewall | MSDTC configuration on all participating machines |
| Deadlocks between parallel branches | Concurrent writes to the same table | Serialise, or stage separately and apply once |
Debugging in the designer — available at design time only, and worth using deliberately:
- Breakpoints on tasks, at specific events (
OnPreExecute,OnPostExecute,OnError), and on iterations of loop containers. - The Locals and Watch windows to inspect variable values at a breakpoint — the fastest way to catch a scope-shadowing bug.
- Progress / Execution Results tab for per-task timing.
None of this exists in production. There, diagnosis comes from catalog logging: catalog.executable_statistics for per-task results and durations, catalog.event_messages for errors.
Performance and resource considerations
Section titled “Performance and resource considerations”- Concurrency is a resource decision. Each concurrently running Data Flow Task allocates its own buffers. Four parallel flows can use four times the memory.
- Execute Package Task with
ExecuteOutOfProcess = Trueisolates memory but pays process startup per call — meaningful when called inside a loop. - Full-cache Lookups warm up during pre-execute, so several parallel data flows can spend a long time doing nothing visible before rows move.
- Serialising for safety often beats parallelising for speed when the contention is on a shared destination.
Measure with catalog.executable_statistics before changing concurrency:
SELECT executable_name, execution_result, start_time, end_time, DATEDIFF(SECOND, start_time, end_time) AS secondsFROM catalog.executable_statistics AS esJOIN catalog.executables AS e ON e.executable_id = es.executable_idWHERE es.execution_id = @execution_idORDER BY seconds DESC;Security and configuration considerations
Section titled “Security and configuration considerations”- Script Tasks run with the package’s identity and can do anything that account can. Review them as you would any privileged code.
- File System Tasks fail differently under a service account than under a developer’s interactive session — mapped drives do not exist, and UNC access depends on the service or proxy account.
- Connection managers referenced by control flow tasks should be parameterised, not hard-coded, so the same package targets different environments.
- Sensitive values used by Execute SQL Tasks (for example a connection built in an expression) can end up in logs at Verbose level. Keep secrets in parameters marked sensitive.
Testing, validation, and operational monitoring
Section titled “Testing, validation, and operational monitoring”- Test the failure branches, not just the happy path.
ForceExecutionResult = Failureon a task is the cheapest way to prove the quarantine branch and the alert actually work — then remove it. - Assert on outcomes, not on completion. A package that finishes is not a package that worked; check row counts and control totals in SQL afterwards.
- Monitor per-task duration trends, not only failures. A task that has crept from 4 to 40 minutes is a failure that has not happened yet.
- Alert on packages that did not run, which no failure alert will ever tell you about.
Trade-offs and alternatives
Section titled “Trade-offs and alternatives”| Decision | Option A | Option B | Choose A when |
|---|---|---|---|
| Orchestration | Master package with Execute Package Tasks | External scheduler with per-package jobs | Dependencies are internal to one deployment unit |
| Iteration | Foreach Loop inside one package | One package per source, scheduled separately | Sources share shape and error handling |
| Non-fatal step | MaximumErrorCount |
Failure branch that records and continues | The failure genuinely needs no compensating action |
| Atomicity | TransactionOption = Required + MSDTC |
Staging + one set-based apply | Multiple heterogeneous writes must commit together |
| Custom logic | Script Task | Stored procedure called by Execute SQL Task | The logic is genuinely not expressible in SQL |
The general lean: push logic into SQL where it can be reviewed, tested, and reused; keep the control flow as a thin, readable orchestration layer.
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”Q: A task is connected by a Completion constraint and its upstream task failed. Does the package fail?
Yes, by default. The Completion constraint only decides what runs next. The failed task still raises an error, and with MaximumErrorCount = 1 and FailPackageOnFailure = True the package fails. To make a step genuinely non-fatal you change those properties deliberately.
Q: Three parallel loads must all finish before a summary step. Two ways to express that — which do you prefer and why? Either three arrows into the summary task with Logical AND, or wrap the three in a Sequence Container with one arrow out. Prefer the container: the intent is visible on the canvas, it gives the group a shared variable and transaction scope, and it survives someone later adding a fourth load (which they would otherwise wire with the default multiple-constraint setting and break).
Q: Why do checkpoints not help inside a Foreach Loop? Checkpoint data is not recorded for For Loop and Foreach Loop containers, so on restart the whole loop re-runs from the first iteration. The documented workaround is to wrap the loop in a Sequence Container so the container becomes the restart point — but the loop body still re-executes, so the work inside must be idempotent. In practice, designing the load to be re-runnable beats configuring checkpoints.
Q: You need one package to load 30 tables with different column layouts. How? A Foreach Loop with an ADO enumerator over a manifest gives you dynamic queries, but not dynamic column metadata — data flow metadata is fixed at design time. Options: a generic approach that stages everything as text and shapes it in SQL; one data flow per distinct shape; or accept 30 small packages generated from a template. What you cannot do is have one built-in data flow adapt its columns per iteration.
Q: A scheduled package reports success but the warehouse is unchanged. Where do you look in the control flow?
Disabled tasks (skipped items report success), ForceExecutionResult left set from testing, MaximumErrorCount raised so real errors were tolerated, an expression-based precedence constraint that evaluated false, and a Foreach Loop that enumerated zero files. All of these produce a green package that did nothing.
Q: When would you set ExecuteOutOfProcess = True?
When a child package needs memory isolation — for example a large data flow whose buffer allocation you do not want competing inside the parent’s process, or a child using an unstable third-party component whose crash should not take the parent down. The cost is process startup per invocation, which matters if it is inside a loop, and a different debugging experience.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Fundamentals
- SSIS Data Flow: Sources, Destinations, Transformations, and Pipeline Internals
- SSIS Variables, Parameters, Expressions, Connection Managers, and Configuration
- SSIS Error Handling, Logging, Event Handlers, Transactions, and Checkpoints
- SSIS Large-Volume ETL and Performance Engineering