Skip to content

SSIS Error Handling, Logging, Event Handlers, Transactions, and Checkpoints

An ETL job that fails is normal. An ETL job that fails badly — no diagnostic detail, half the data loaded, no safe way to re-run — is a design failure that costs someone a night.

The mechanisms in this article are individually simple. What matters is combining them into something operable: a package that quarantines bad rows instead of dying, tells you exactly what went wrong, leaves the target in a known state, and can be re-run without thinking.

SSIS error handling operates at three distinct levels, and confusing them is the root of most bad designs.

Level Unit of failure Governed by
Row (data flow) One row Error output disposition: Fail Component / Ignore Failure / Redirect Row
Task (control flow) One executable FailPackageOnFailure, FailParentOnFailure, MaximumErrorCount, Failure constraints
Package The whole execution Error counts, event handlers, the caller’s interpretation of the result

A red precedence constraint cannot catch a bad row. An error output cannot catch a failed Execute SQL Task. Design each level deliberately.

The mental model: fail loudly, quarantine precisely, re-run safely

Section titled “The mental model: fail loudly, quarantine precisely, re-run safely”
┌──────────────────────────────────────────────────────────┐
│ Is this row bad, or is the whole batch untrustworthy? │
└───────────────┬──────────────────────┬───────────────────┘
│ │
one bad row the batch is wrong
│ │
▼ ▼
Redirect Row to a Fail Component,
reject table with a fail the task, run the
reason + context Failure branch
│ │
▼ ▼
Count rejects; if Quarantine input, record
the RATIO crosses a the failure, alert,
threshold, fail the run leave target unchanged
│ │
└──────────┬───────────┘
▼
Target is in a known state, and
re-running produces the same result
(idempotence)

Notice what is not in that diagram: checkpoints. They are useful, but they are the last resort, not the foundation. A load you can simply re-run beats any restart configuration.

The smallest meaningful improvement over the default: stop one bad row killing a load.

Flat File Source error output → Redirect Row
│ (good rows) │
▼ ▼
OLE DB Destination (fast load) Flat File Destination
→ stg.SalesLine → rejects_yyyymmdd.csv

That is a two-minute change with a large operational payoff. Everything below makes it diagnosable and safe.

Every component that can fail exposes an error output, configured per column for Error (a conversion or expression failure) and Truncation (a value too long for the target).

Disposition Behaviour When it is right
Fail Component Component errors, data flow stops The default. Correct when any bad row means the batch is untrustworthy
Ignore Failure Value becomes NULL, row continues Almost never — silently corrupts data with no record
Redirect Row Row goes to the error output The right answer for most file-based and external-source loads

Redirect Row is the pattern to reach for. A load that processes 999,000 of a million rows and quarantines 1,000 with reasons is far more useful than one that dies at row 500,001.

The error output adds two columns, and neither is human-readable:

  • ErrorCode — the numeric SSIS error.
  • ErrorColumn — the lineage ID of the offending column, an internal design-time identifier, not a name.

This is why so many reject files are useless. Translate them with a Script Component on the error path:

// Script Component (Transformation) on the error path.
// Inputs: ErrorCode, ErrorColumn
// Outputs: ErrorDescription (DT_WSTR 1000), ErrorColumnName (DT_WSTR 128)
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
IDTSComponentMetaData130 meta = this.ComponentMetaData as IDTSComponentMetaData130;
Row.ErrorColumnName = meta != null
? meta.GetIdentificationStringByID(Row.ErrorColumn)
: Row.ErrorColumn.ToString();
}

Then add operational context with a Derived Column, so a reject row can be traced back to its run and its file:

(DT_WSTR, 128) @[System::PackageName]
(DT_WSTR, 38) @[System::ExecutionInstanceGUID]
(DT_WSTR, 260) @[User::CurrentFilePath]
(DT_I4) @[User::BatchId]
(DT_DBTIMESTAMP) @[System::StartTime]
CREATE TABLE dbo.EtlReject
(
RejectId BIGINT IDENTITY(1,1) PRIMARY KEY,
BatchId INT NOT NULL,
ExecutionId UNIQUEIDENTIFIER NOT NULL,
PackageName SYSNAME NOT NULL,
SourceFile NVARCHAR(260) NULL,
RejectReason VARCHAR(50) NOT NULL, -- FailedValidation, UnknownStore, ConversionError…
ErrorColumnName NVARCHAR(128) NULL,
ErrorDescription NVARCHAR(1000) NULL,
RawRow NVARCHAR(4000) NULL, -- the original row, for replay
RejectedOn DATETIME2(3) NOT NULL CONSTRAINT DF_EtlReject_On DEFAULT SYSUTCDATETIME()
);
CREATE INDEX IX_EtlReject_Batch ON dbo.EtlReject (BatchId) INCLUDE (RejectReason);

A table makes rejects queryable: how many, which columns, which reason, trending over time, which source system is degrading. A CSV in a folder makes them archaeology.

-- Which failure modes dominate this week?
SELECT RejectReason,
ErrorColumnName,
COUNT(*) AS rows_rejected
FROM dbo.EtlReject
WHERE RejectedOn > DATEADD(DAY, -7, SYSUTCDATETIME())
GROUP BY RejectReason, ErrorColumnName
ORDER BY rows_rejected DESC;

Closing the “succeeded while discarding data” gap

Section titled “Closing the “succeeded while discarding data” gap”

Redirecting rows means a package can report success while silently dropping data. Close that gap explicitly.

Data Flow Task
good rows → destination → Row Count → User::RowsStaged
error rows → reject table → Row Count → User::RowsRejected
│
▼
Precedence constraint, Evaluation operation = Expression and Constraint:
((DT_R8)@[User::RowsRejected] / ((DT_R8)@[User::RowsStaged] + 1.0)) * 100.0
> (DT_R8)@[$Project::RejectThresholdPct]
│
▼
[Execute SQL Task] raise the alert / mark the batch as failed

Use a ratio, not an absolute count. Three rejects in a hundred rows is a broken feed; three in ten million is noise. The + 1.0 avoids divide-by-zero when nothing was staged.

Four mechanisms, in order of how often they are misused:

Mechanism Effect
Failure precedence constraint Runs compensating work: quarantine the file, record the failure, notify
FailPackageOnFailure Does this item’s failure fail the package? Also required for checkpoint restart points
FailParentOnFailure Does the failure propagate to the containing container?
MaximumErrorCount How many errors the item tolerates before it is considered failed. Default 1

The most common misunderstanding: a Completion (blue) constraint does not swallow a failure. The downstream task runs, but the failed task still errored, and the package still fails unless MaximumErrorCount says otherwise. Making a step genuinely non-fatal is a deliberate property change, not an arrow colour.

Event handlers are control flows that run when an event fires on a task, container, or package. They live on the Event Handlers tab and have their own design surface.

Event Fires when Typical use
OnError An error occurs — once per error Record every error to a log table
OnTaskFailed A task fails — once per failed task Notify; close the batch as failed
OnWarning A warning is raised Capture truncation and metadata warnings that predict future failures
OnPreExecute / OnPostExecute Immediately before/after an executable Timing, audit trail of which steps ran
OnVariableValueChanged A variable with RaiseChangedEvent = True changes Debugging; rarely in production
OnProgress A task reports progress Long-running task visibility
OnInformation An informational message Custom instrumentation from Script Tasks
OnQueryCancel A cancellation is requested Graceful shutdown

OnError vs OnTaskFailed — the distinction that prevents pager storms

Section titled “OnError vs OnTaskFailed — the distinction that prevents pager storms”

A single failing task frequently raises several errors. An OnError handler that sends email produces a burst of messages for one incident. OnTaskFailed fires once per failed task.

Use OnError to record. Use OnTaskFailed to react.

Package-level OnError handler:
[Execute SQL Task]
INSERT INTO dbo.EtlErrorLog
(ExecutionId, PackageName, SourceName, ErrorCode, ErrorDescription, LoggedOn)
VALUES (?, ?, ?, ?, ?, SYSUTCDATETIME());
Parameter mapping (OLE DB, ordinal):
0 → System::ExecutionInstanceGUID
1 → System::PackageName
2 → System::SourceName -- the task that raised it
3 → System::ErrorCode
4 → System::ErrorDescription
Package-level OnTaskFailed handler:
[Execute SQL Task] EXEC dbo.usp_EtlBatch_Close @BatchId = ?, @Status = 'Failed'
[Send Mail Task] one message, with the batch id and a link to the log
  • Events bubble up the container hierarchy. A package-level handler fires for events raised anywhere beneath it, unless a child sets DisableEventHandlers = True.
  • Handlers can fail. An OnError handler that itself errors makes diagnosis dramatically worse — and can recurse. Keep handlers trivial, defensive, and free of anything that can throw.
  • Handlers inherit variable scope from the executable that raised the event, which is why System::SourceName correctly names the failing task.
  • A handler is a full control flow, so it can contain loops and data flows. Resist. Complexity in a handler runs at exactly the moment things are already going wrong.

Packages run from the catalog are logged automatically — no log provider to configure. The logging level is set per execution.

Level Captures Cost
None Nothing —
Basic Most events except custom and diagnostic Low. The sensible default
Performance Basic plus component phase timings Moderate. Use when tuning
Verbose Everything: row counts per component, variable values High. Troubleshooting only
RuntimeLineage Lineage data for downstream analysis Specialised

You can also define customized logging levels in SSISDB to capture a specific event set.

The data lands in catalog views worth knowing by name:

View Contains
catalog.executions One row per execution: status, times, project, package, environment
catalog.event_messages Individual messages, including error text
catalog.event_message_context The context (variable values, properties) around a message
catalog.executable_statistics Per-executable results and durations
catalog.execution_component_phases Time per component phase (Performance/Verbose)
catalog.execution_data_statistics Rows sent between components (Verbose)
catalog.execution_parameter_values What parameter values the execution actually used
catalog.operation_messages Messages for all operations, including deployments
-- Yesterday's failures with their first error
SELECT e.execution_id,
e.folder_name,
e.project_name,
e.package_name,
e.start_time,
MIN(m.message) AS first_error
FROM catalog.executions AS e
JOIN catalog.event_messages AS m
ON m.operation_id = e.execution_id
AND m.message_type = 120 -- error
WHERE e.status = 4 -- failed
AND e.start_time > DATEADD(DAY, -1, SYSDATETIMEOFFSET())
GROUP BY e.execution_id, e.folder_name, e.project_name, e.package_name, e.start_time
ORDER BY e.start_time DESC;

Status values worth memorising: 2 running, 3 cancelled, 4 failed, 7 succeeded, 9 stopping.

SSMS also ships standard reports — right-click SSISDB, a folder, project, or package → Reports → Standard Reports → All Executions or Integration Services Dashboard.

Retention matters. The catalog properties RETENTION_WINDOW (days of history) and OPERATION_CLEANUP_ENABLED govern growth. Verbose logging on a busy server grows SSISDB fast; check both before enabling it, and remember the cleanup job’s deletes are themselves expensive on a large backlog.

Log providers (package deployment model, or file-based needs)

Section titled “Log providers (package deployment model, or file-based needs)”

Packages can use log providers instead: SQL Server (the sysssislog table), text file, Windows Event Log, XML file, or SQL Profiler. Configured per package, with events selected individually.

Under the project model these are largely redundant. They remain relevant for legacy packages and when a log must exist outside SQL Server — for example on a machine that cannot reach the catalog.

Catalog logging tells you about packages. It does not tell you the batch loaded 1.2 million rows for the wrong business date. Production ETL needs its own audit schema.

CREATE TABLE dbo.EtlBatch
(
BatchId INT IDENTITY(1,1) PRIMARY KEY,
PackageName SYSNAME NOT NULL,
ExecutionId UNIQUEIDENTIFIER NOT NULL,
EnvironmentNm VARCHAR(30) NULL, -- assert which config was used
BusinessDate DATE NULL,
StartedOn DATETIME2(3) NOT NULL CONSTRAINT DF_EtlBatch_Started DEFAULT SYSUTCDATETIME(),
CompletedOn DATETIME2(3) NULL,
Status VARCHAR(20) NOT NULL CONSTRAINT DF_EtlBatch_Status DEFAULT 'Running',
RowsRead BIGINT NULL,
RowsLoaded BIGINT NULL,
RowsRejected BIGINT NULL
);

An Execute SQL Task opens the batch and captures the id; Row Counts collect the numbers; a final task closes it; the OnTaskFailed handler marks it failed. This table is what operations actually reads.

A fictional nightly load with the full error-handling design assembled.

LoadSalesFact.dtsx
[Execute SQL Task] "Open batch"
EXEC dbo.usp_EtlBatch_Open @PackageName=?, @ExecutionId=?, @EnvironmentNm=?, @BatchId=? OUTPUT
│ Success
▼
[Foreach Loop] over *.csv in @[$Project::InboundFolder]
│
├── [Data Flow Task] "Stage sales lines" DelayValidation = True
│ Flat File Source ──(error output: Redirect Row)──┐
│ │ │
│ ▼ ▼
│ Conditional Split "Validate" Script Component
│ ├─ Invalid ──> reason="FailedValidation" ──┐ "Describe error"
│ └─ default │ │
│ ▼ │ ▼
│ Lookup "Store" (Full Cache) │ reason="ConversionError"
│ ├─ no-match ─> reason="UnknownStore" ──────┤ │
│ └─ match │ │
│ ▼ ▼ ▼
│ Multicast ──> Row Count (User::RowsStaged) Union All ──> Derived Column
│ └────> OLE DB Dest (fast load) (add batch/exec context)
│ → stg.SalesLine │
│ ▼
│ Row Count (User::RowsRejected)
│ │
│ OLE DB Dest → dbo.EtlReject
│ │
│ ├── [Success] [File System Task] move file → Archive\yyyyMMdd\
│ └── [Failure] [File System Task] move file → Quarantine\
│ │
│ ▼
│ [Execute SQL Task] record the quarantined file
│
│ Success
▼
[Execute SQL Task] "Apply batch" -- idempotent, set-based, one transaction
EXEC dbo.usp_FactSales_ApplyBatch @BatchId = ?
│
├─[Expression and Constraint]─> [Execute SQL Task] "Raise reject alert"
│ ((DT_R8)@[User::RowsRejected] /
│ ((DT_R8)@[User::RowsStaged] + 1.0)) * 100.0 > 2.0
│
│ Success
▼
[Execute SQL Task] "Close batch"
EXEC dbo.usp_EtlBatch_Close @BatchId=?, @Status='Success',
@RowsLoaded=?, @RowsRejected=?
EVENT HANDLERS (package scope)
OnError → INSERT dbo.EtlErrorLog (every error; no logic, no branching)
OnTaskFailed → EXEC dbo.usp_EtlBatch_Close @Status='Failed'
→ Send Mail (one message, includes @BatchId)
OnWarning → INSERT dbo.EtlWarningLog (truncation warnings predict tomorrow's failures)

And the apply procedure, which is where restartability actually comes from:

CREATE OR ALTER PROCEDURE dbo.usp_FactSales_ApplyBatch
@BatchId INT
AS
BEGIN
SET NOCOUNT, XACT_ABORT ON;
BEGIN TRAN;
-- Idempotent: remove anything this batch previously wrote
DELETE f
FROM dbo.FactSales AS f
WHERE f.BatchId = @BatchId;
INSERT INTO dbo.FactSales
(BatchId, StoreKey, ProductKey, SaleDate, Quantity, NetAmount)
SELECT s.BatchId, s.StoreKey, s.ProductKey, s.SaleDate, s.Quantity,
s.Quantity * s.UnitPrice * (1 - s.DiscountRate)
FROM stg.SalesLine AS s
WHERE s.BatchId = @BatchId;
COMMIT;
END;

Re-running the package with the same batch produces the same result. That single property removes the need for checkpoints, distributed transactions, and most of the recovery runbook.

TransactionOption on packages, containers, and tasks:

Value Meaning
Required Join the parent transaction if one exists; otherwise start a new one
Supported Join a parent transaction if present; never start one. The default
NotSupported Never participate

Distributed transactions require MSDTC running and reachable on every participating machine, with matching firewall rules. This is the single most common reason SSIS transactions work on a developer’s machine and fail in production.

Additional properties: IsolationLevel on the container, which defaults to Serializable — frequently stronger than intended and a real source of blocking.

Single-connection transactions. Set RetainSameConnection = True on the OLE DB connection manager, then issue BEGIN TRANSACTION, COMMIT, and ROLLBACK in Execute SQL Tasks. All work must go through that one connection, but there is no distributed coordinator.

[Execute SQL Task] BEGIN TRANSACTION; -- connection manager: RetainSameConnection = True
│ Success
▼
[Data Flow Task] load, using the SAME connection manager
│
├── [Success] [Execute SQL Task] COMMIT TRANSACTION;
└── [Failure] [Execute SQL Task] ROLLBACK TRANSACTION;

Design for restartability instead of atomicity. Load into staging (no transaction needed), then apply in one short set-based transaction. This is where most large-volume ETL ends up, and it is the better answer at scale.

Long transactions across a multi-million-row load are expensive regardless of mechanism: the log cannot truncate, locks are held, and a rollback can take as long as the load did.

Checkpoints let a failed package restart from the point of failure rather than the beginning.

Three package properties:

Property Set to
SaveCheckpoints True
CheckpointFileName A path the executing account can write to
CheckpointUsage IfExists (restart if the file is present) or Always (fail if missing)

Then FailPackageOnFailure = True on each task or container you want as a restart point. The file records completed containers, variable values, and configuration, and is deleted on success.

The limitations that decide whether checkpoints are worth it

Section titled “The limitations that decide whether checkpoints are worth it”
  • Control flow only. A package cannot restart mid-data-flow. A failure at row 4,000,000 of 5,000,000 re-runs the whole data flow. Mitigate by splitting work across several Data Flow Tasks so each is a restart point.
  • Loop containers are not checkpointed. Checkpoint data is not saved for For Loop and Foreach Loop containers, so a restart re-runs the entire loop from the first iteration. Microsoft’s documented workaround is wrapping the loop in a Sequence Container — but the body still re-runs, so the work inside must be idempotent anyway.
  • Object variables are not saved. A recordset held in an Object variable is gone after restart.
  • Transactions and checkpoints interact badly. A transacted container that committed before the failure may be re-run after restart, repeating committed work. Microsoft explicitly warns against combining them casually.
  • Package identity must match. A checkpoint written by a different version of the package will not restart it.
  • Configurations are not reloaded on restart — the package uses the values stored in the checkpoint file, which is deliberate (consistency) but surprising if you “fixed” a parameter and re-ran.
  • Checkpoint files are not covered by the package protection level. They contain variable values; secure the folder with an ACL.

The honest summary: checkpoints help most when a package runs several long, expensive, independent steps — several large file downloads, or several separate dimension loads. They help least in the case people hope for, a single huge data flow.

SSIS has no built-in retry. The patterns that work:

Bounded retry with a For Loop.

[For Loop Container]
InitExpression: @Attempt = 1
EvalExpression: @Attempt <= @MaxAttempts && @Succeeded == False
AssignExpression: @Attempt = @Attempt + 1
│
├── [Task] MaximumErrorCount raised so a failure does not fail the loop
│ ├── [Success] [Expression Task] @Succeeded = True
│ └── [Failure] [Script Task] sleep with backoff, log the attempt

Retry only what is genuinely transient — a network blip reaching a file share, a deadlock victim, a momentarily unavailable source. Never retry a data error; it will fail identically and hide the real problem.

Compensating actions on the Failure branch — quarantine the input, write an audit row, remove partial output, notify. This is what makes a failure recoverable by a human at 07:00 rather than a forensic exercise.

Recovery hierarchy, best to worst:

  1. Idempotent re-run. Just run it again.
  2. Batch-scoped cleanup then re-run. Delete this batch’s rows, run again.
  3. Checkpoint restart. Resume from the failed task.
  4. Manual repair. Someone reasons about partial state at 03:00.

Aim for 1. Design for 2. Configure 3 only where it genuinely helps. Never rely on 4.

The most reliable answer to “can we re-run it?” is a design where re-running changes nothing extra.

  • Delete-then-insert by scope. Before loading a partition, business date, or batch, delete exactly that scope, then insert.
  • Stage first, apply once. Load raw data into staging keyed by batch id, then apply with one set-based statement. Truncating and reloading staging is always safe.
  • Deterministic keys. Natural or hash keys, so the same source row maps to the same target row every time.
  • Advance state only after commit. A watermark advanced before the apply succeeds means permanently skipped data.
  • Mark partial state. A run that failed midway should leave either nothing, or a clearly marked incomplete batch that the next run cleans up.

More on this, and how it interacts with change detection, in Incremental Loads.

Errors in event handlers. An OnError handler that fails raises its own errors, which can re-enter the handler. Keep them to a single, defensive Execute SQL Task with MaximumErrorCount raised so a logging failure cannot cascade.

Propagate. Inside an event handler, the system variable System::Propagate controls whether the event continues bubbling up. Setting it False stops propagation — occasionally the right tool to prevent a handled, expected error from failing the package, and a good way to hide real failures if used carelessly.

Warnings that predict failures. Truncation warnings and “the external columns are out of sync” warnings are tomorrow’s errors. An OnWarning handler writing to a table gives you a lead indicator.

Fast-load batch failures. Under fast load, a constraint violation fails the entire batch defined by the commit size, not just the bad row — and those rows do not appear on an error output as individual failures. If per-row diagnosis matters, either validate before the destination or use a smaller commit size, accepting the throughput cost.

Data flow errors that are not row errors. A destination that cannot connect, or a Lookup whose reference query fails, fails the component regardless of error output configuration. Error outputs handle row problems, not component problems.

Cancelled executions. Status 3 (cancelled) is not 4 (failed). Monitoring that only alerts on status 4 misses a job someone stopped and forgot to restart.

BAD:
OnError handler → Send Mail Task
One failing task raises 4 errors → 4 emails.
A loop over 50 files with a systemic problem → 200 emails.
The team creates an inbox rule. The next real failure is missed.
BETTER:
OnError → INSERT into dbo.EtlErrorLog (record everything)
OnTaskFailed → Send Mail (once per failed task)
and consider notifying only from the package's final failure path.

Anti-pattern: Ignore Failure as a quick fix

Section titled “Anti-pattern: Ignore Failure as a quick fix”
BAD:
A truncation error is annoying, so set the column to Ignore Failure.
The column becomes NULL for the affected rows.
Nothing records that it happened. Six months later, a report is wrong
and there is no evidence of when it started.
BETTER:
Redirect Row → reject table with reason "Truncation" and the column name.
Now it is measurable, alertable, and fixable at the source.
  • Leaving every error output at Fail Component for file loads.
  • Redirecting rows and never counting them.
  • Logging ErrorColumn without translating it.
  • Assuming Completion constraints absorb failures.
  • Verbose logging left on permanently, growing SSISDB until it becomes the incident.
  • Transactions without MSDTC — works in dev, fails in prod.
  • Checkpoints inside loops, where they do not apply.
  • Retrying data errors in a loop that can never succeed.
  • Monitoring only for failures, so a job that never ran is invisible.

Failure modes, recovery, and troubleshooting

Section titled “Failure modes, recovery, and troubleshooting”
Symptom Likely cause Action
One bad row kills a million-row load Error output at Fail Component Redirect Row + reject table
Package “succeeded”, data missing Rows redirected and never counted Row Count + ratio threshold
Reject data is unusable ErrorColumn not translated Script Component with GetIdentificationStringByID
Email storm on one failure Notifying from OnError Notify from OnTaskFailed
Package fails despite “optional” step Completion ≠ suppression MaximumErrorCount, FailPackageOnFailure
Transaction errors only in production MSDTC missing or blocked Configure MSDTC, or switch to RetainSameConnection
Blocking during loads with transactions IsolationLevel defaults to Serializable Set an appropriate isolation level, or stage instead
Restart re-runs the whole loop Loops are not checkpointed Redesign for idempotence
Restart used stale parameter values Checkpoint stores configuration Delete the checkpoint file and start clean
SSISDB is now the largest database Verbose + long retention Lower the level; check RETENTION_WINDOW and cleanup

A repeatable production triage:

  1. Get the execution_id from catalog.executions.
  2. Read catalog.event_messages for that execution ordered by time — the first error names the real component; later ones are cascade.
  3. Check catalog.execution_parameter_values to confirm which configuration was used.
  4. Check the batch audit table: did it open, how far did it get, what counts were recorded?
  5. Query the reject table for that batch — often the “failure” is a data quality event that crossed a threshold.
  6. Only if still unclear, re-run one execution at Verbose to get per-component row counts.
  • Verbose logging is expensive, both in execution overhead and SSISDB growth. Use it for one diagnostic run, not as a default.
  • Reject tables need indexes and their own retention. An unmanaged reject table becomes a large, slow table that makes every load slower.
  • Long transactions block log truncation and hold locks for the duration of the load.
  • Serializable isolation (the transaction default) escalates locking well beyond what most loads need.
  • Event handlers run inline. A heavy OnError handler firing hundreds of times materially slows a failing package — exactly when you want it to finish and report.
  • Reject tables and files contain the data that failed — potentially the most sensitive rows. Secure them, set retention, and consider masking before writing.
  • Checkpoint and log files are not covered by the package protection level. They hold variable values; protect the folders with ACLs.
  • Verbose logging records variable values, which can include things you would not want in a log.
  • Error messages leak schema and sometimes data. Notifications that paste raw error text into email are a quiet information-disclosure path.
  • The account writing reject data needs write access to that table only — not broad rights on the warehouse.

Testing, validation, and operational monitoring

Section titled “Testing, validation, and operational monitoring”

Test the error paths deliberately. A seeded input file with a known mix of good rows, type failures, truncations, and unknown dimension members proves that error handling exists. ForceExecutionResult = Failure on a task proves the quarantine branch works — then remove it.

Assert after every run:

-- Reconciliation: everything read is accounted for
SELECT b.BatchId,
b.RowsRead,
b.RowsLoaded,
b.RowsRejected,
CASE WHEN b.RowsRead = b.RowsLoaded + b.RowsRejected
THEN 'OK' ELSE 'UNRECONCILED' END AS reconciliation
FROM dbo.EtlBatch AS b
WHERE b.BatchId = @BatchId;

Monitor these, in priority order:

  1. Failed executions (status 4) — obvious.
  2. Cancelled executions (status 3) — frequently missed.
  3. Packages that did not run. No failure alert will ever tell you this. Compare expected schedule to catalog.executions.
  4. Reject ratio crossing a threshold.
  5. Duration trending upward — a failure that has not happened yet.
  6. Unreconciled batches, per the query above.
Decision Option A Option B Choose A when
Bad rows Redirect Row + reject table Fail Component The source is external and imperfect by nature
Atomicity TransactionOption = Required (MSDTC) Staging + short set-based apply Multiple heterogeneous systems must commit together
Atomicity Distributed transaction RetainSameConnection single-connection transaction More than one connection is genuinely involved
Restart Checkpoints Idempotent re-run Steps are long, independent, and expensive to repeat
Notification From OnTaskFailed From a final failure path in the control flow You want per-task detail
Logging SSISDB catalog logging Log providers You are on the project deployment model (nearly always)

Q: Difference between OnError and OnTaskFailed, and why does it matter operationally? OnError fires once per error and errors bubble up the container hierarchy, so one failing task commonly raises several. OnTaskFailed fires once per failed task. Operationally: record from OnError (you want every detail) and notify from OnTaskFailed (you want one message). Getting this backwards produces alert storms, which trains people to ignore alerts.

Q: Why can’t checkpoints restart a failed data flow partway? Checkpoints operate at the control flow level — the task host container is the smallest atomic unit. The data flow’s internal state (buffers in flight, partially committed batches at the destination) is not captured, so restart re-executes the whole Data Flow Task. The mitigation is structural: split work across multiple Data Flow Tasks so each becomes a restart point, and make the load idempotent so re-running is safe.

Q: A package uses TransactionOption = Required and works in development but fails in production. First hypothesis? MSDTC. Distributed transactions need the coordinator running and reachable on every participating machine, with firewall rules and network DTC access configured. Development often has everything on one box, so no distributed transaction is actually required. The fix is either to configure MSDTC properly or, usually better, to avoid it: RetainSameConnection = True with explicit BEGIN/COMMIT/ROLLBACK, or a staging-plus-short-apply design.

Q: What is ErrorColumn and why is a raw reject file usually useless? ErrorColumn is a lineage ID — an internal design-time identifier for a column on a path — not a column name, and ErrorCode is a numeric code. A reject file containing “-1071607685, 73” tells an operator nothing. Translating both with a Script Component (GetErrorDescription, GetIdentificationStringByID) and adding batch/execution/file context turns rejects into something actionable and trendable.

Q: How do you stop a package “succeeding” while silently discarding 40% of its rows? Count both branches with Row Count transformations, then evaluate the ratio in a precedence constraint expression against a threshold parameter, and fail or alert when it is exceeded. Absolute counts are the wrong test because the acceptable number scales with volume. Also reconcile in SQL afterwards: rows read must equal rows loaded plus rows rejected.

Q: Under fast load, a constraint violation occurs. What actually fails, and what does that mean for error handling? The entire batch defined by Maximum insert commit size fails, not the individual row — and those rows do not surface individually on an error output. So commit size is an error-handling decision as well as a performance one: large batches are faster but lose more work per failure and give coarser diagnostics. If per-row identification matters, validate before the destination (Conditional Split, Lookup) rather than relying on the database to reject.

Q: You are asked to add retry to every task “for resilience”. Response? Push back on the blanket approach. Retry is correct for genuinely transient failures — a network blip to a file share, a deadlock victim, a briefly unavailable source — and harmful for data errors, which will fail identically while obscuring the real problem and multiplying the time to detection. Implement bounded retry with backoff on the specific tasks whose failure modes are transient, and make everything else fail fast into a recorded, alertable state.

Q: What is the single highest-value error-handling investment in a new SSIS estate? Idempotent loads with batch-scoped staging. It makes the recovery procedure “run it again”, which removes the need for checkpoints, distributed transactions, and most of the runbook — and it is the one property that makes every other mechanism optional rather than load-bearing.