Skip to content

SSIS Control Flow: Tasks, Containers, and Precedence Constraints

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.

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 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:

  1. 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 MaxConcurrentExecutables cap, 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.
  2. Constraint evaluation is per-arrow. A task with several incoming arrows runs when the combination rule (AND/OR) over those arrows is satisfied.

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.FactSales

Even 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.

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, and EngineThreads are properties of this task, not of the package.

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 variable
Full result set → map to ONE variable of type Object, then iterate it
with a Foreach Loop using the ADO enumerator

Parameter 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 order
UPDATE dbo.LoadControl
SET LastLoadedOn = ?,
RowsLoaded = ?
WHERE SourceName = ?;
-- ADO.NET connection manager: named parameters
UPDATE dbo.LoadControl
SET LastLoadedOn = @LoadedOn,
RowsLoaded = @RowCount
WHERE SourceName = @SourceName;

Edge cases worth knowing:

  • Output parameters and return values need Direction set correctly in the mapping, and the SQL must declare them.
  • A stored procedure that returns multiple result sets only surfaces the first.
  • DATETIME mapping between SSIS types and T-SQL types is a recurring source of precision loss — prefer DT_DBTIMESTAMP with explicit DATETIME2 on the SQL side.

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.

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.TaskResult explicitly. Forgetting it produces a task that silently reports success.
  • Never swallow exceptions. Use FireError so 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 VariableDispenser is required when the variable list is not known at design time — and is a common source of deadlocks when locks are not released.

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.

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”

A static grouping. It looks cosmetic and is not:

  • Variable scope narrower than package level.
  • One place to set TransactionOption for 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”.

Repeats while a condition holds, driven by three expressions:

InitExpression: @Counter = 1
EvalExpression: @Counter <= @BatchCount
AssignExpression: @Counter = @Counter + 1

Uses: processing a table in fixed-size batches, bounded retry, walking a date range day by day.

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::LoadQuery

One 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.

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 sequence

Two 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 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.
  • OnError records, OnTaskFailed reacts — the distinction that prevents notification storms.

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.

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]

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.

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 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 month
DAY(@[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.

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.

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 -1 means “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.

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.

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 reason
BETTER:
[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.
  • 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.
  • ForceExecutionResult left on after testing.
  • Raising MaxConcurrentExecutables to “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.

  • 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 = True isolates 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 seconds
FROM catalog.executable_statistics AS es
JOIN catalog.executables AS e
ON e.executable_id = es.executable_id
WHERE es.execution_id = @execution_id
ORDER BY seconds DESC;
  • 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 = Failure on 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.
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.

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.