SSIS Error Handling, Logging, Event Handlers, Transactions, and Checkpoints
Why this topic matters
Section titled “Why this topic matters”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.
Fundamentals and terminology
Section titled “Fundamentals and terminology”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.
Minimal working example
Section titled “Minimal working example”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.csvThat is a two-minute change with a large operational payoff. Everything below makes it diagnosable and safe.
Error handling in the data flow
Section titled “Error handling in the data flow”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.
Making error metadata readable
Section titled “Making error metadata readable”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]Reject tables beat reject files
Section titled “Reject tables beat reject files”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_rejectedFROM dbo.EtlRejectWHERE RejectedOn > DATEADD(DAY, -7, SYSUTCDATETIME())GROUP BY RejectReason, ErrorColumnNameORDER 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 failedUse 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.
Error handling in the control flow
Section titled “Error handling in the control flow”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
Section titled “Event handlers”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 logEvent handler semantics you must know
Section titled “Event handler semantics you must know”- 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
OnErrorhandler 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::SourceNamecorrectly 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.
Logging and diagnostics
Section titled “Logging and diagnostics”SSISDB logging (project deployment model)
Section titled “SSISDB logging (project deployment model)”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 errorSELECT e.execution_id, e.folder_name, e.project_name, e.package_name, e.start_time, MIN(m.message) AS first_errorFROM catalog.executions AS eJOIN catalog.event_messages AS m ON m.operation_id = e.execution_id AND m.message_type = 120 -- errorWHERE 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_timeORDER 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.
Custom audit logging
Section titled “Custom audit logging”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 realistic end-to-end example
Section titled “A realistic end-to-end example”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 INTASBEGIN 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.
Transactions
Section titled “Transactions”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.
Alternatives that avoid MSDTC
Section titled “Alternatives that avoid MSDTC”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 and restartability
Section titled “Checkpoints and restartability”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.
Objectvariables are not saved. A recordset held in anObjectvariable 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.
Retry and recovery strategies
Section titled “Retry and recovery strategies”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 attemptRetry 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:
- Idempotent re-run. Just run it again.
- Batch-scoped cleanup then re-run. Delete this batch’s rows, run again.
- Checkpoint restart. Resume from the failed task.
- 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.
Idempotent ETL design
Section titled “Idempotent ETL design”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.
Advanced scenarios and edge cases
Section titled “Advanced scenarios and edge cases”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.
Common mistakes and anti-patterns
Section titled “Common mistakes and anti-patterns”Anti-pattern: emailing from OnError
Section titled “Anti-pattern: emailing from OnError”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.Other frequent mistakes
Section titled “Other frequent mistakes”- Leaving every error output at Fail Component for file loads.
- Redirecting rows and never counting them.
- Logging
ErrorColumnwithout translating it. - Assuming Completion constraints absorb failures.
Verboselogging 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:
- Get the
execution_idfromcatalog.executions. - Read
catalog.event_messagesfor that execution ordered by time — the first error names the real component; later ones are cascade. - Check
catalog.execution_parameter_valuesto confirm which configuration was used. - Check the batch audit table: did it open, how far did it get, what counts were recorded?
- Query the reject table for that batch — often the “failure” is a data quality event that crossed a threshold.
- Only if still unclear, re-run one execution at
Verboseto get per-component row counts.
Performance and resource considerations
Section titled “Performance and resource considerations”Verboselogging 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.
Serializableisolation (the transaction default) escalates locking well beyond what most loads need.- Event handlers run inline. A heavy
OnErrorhandler firing hundreds of times materially slows a failing package — exactly when you want it to finish and report.
Security and configuration considerations
Section titled “Security and configuration considerations”- 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.
Verboselogging 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 forSELECT b.BatchId, b.RowsRead, b.RowsLoaded, b.RowsRejected, CASE WHEN b.RowsRead = b.RowsLoaded + b.RowsRejected THEN 'OK' ELSE 'UNRECONCILED' END AS reconciliationFROM dbo.EtlBatch AS bWHERE b.BatchId = @BatchId;Monitor these, in priority order:
- Failed executions (status
4) — obvious. - Cancelled executions (status
3) — frequently missed. - Packages that did not run. No failure alert will ever tell you this. Compare expected schedule to
catalog.executions. - Reject ratio crossing a threshold.
- Duration trending upward — a failure that has not happened yet.
- Unreconciled batches, per the query above.
Trade-offs and alternatives
Section titled “Trade-offs and alternatives”| 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) |
Senior interview and scenario questions
Section titled “Senior interview and scenario questions”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.
Related BankyBlueprints pages
Section titled “Related BankyBlueprints pages”- SSIS Control Flow: Tasks, Containers, and Precedence Constraints
- SSIS Data Flow: Sources, Destinations, Transformations, and Pipeline Internals
- SSIS Incremental Loads: Watermarks, CDC, Change Tracking, and Staging
- SSIS Deployment, SSISDB, Environments, and Operations
- SSIS Large-Volume ETL and Performance Engineering
Official Microsoft references
Section titled “Official Microsoft references”- Integration Services (SSIS) Event Handlers
- Error Handling in Data
- Integration Services (SSIS) Logging
- Restart Packages by Using Checkpoints
- SSIS Checkpoints are not honored for For Loop or Foreach Loop container items
- Integration Services Transactions
- catalog.executions (SSISDB Database)
- catalog.event_messages
- Monitor Running Packages and Other Operations