There are two kinds of ETL failure, and only one of them wakes you up. The first is the honest failure: something breaks, the package stops, an alert fires, someone groans and fixes it in the morning. Irritating, but survivable. The second is the quiet one — the package that hits a bad row, shrugs, keeps going, and loads most of your data. No alert. No error. Just a report on Monday where the totals are subtly, unaccountably wrong, and a week of everyone's trust gone before anyone works out why.

The whole craft of error handling in SSIS is about converting the second kind of failure into the first. Fail loudly, fail safely, and leave a trail. Here's how I've learned to build packages that do that.

Decide what "an error" even means — per component

The first thing that surprised me: in SSIS, error behaviour isn't one global setting, it's a decision you make in lots of small places, and the defaults aren't always what you want.

Take a data flow. On components that can reject rows — a lookup, a data conversion, a destination — you get three choices for what happens when a row misbehaves: fail the component, ignore the failure, or redirect the row. "Ignore failure" is the setting that quietly loads half your data. It has its uses, but choosing it should be a deliberate act, not something you left on by accident.

My default is: fail on anything I haven't explicitly decided to tolerate, and redirect the rows I want to inspect rather than silently dropping them.

Redirect bad rows somewhere you'll actually look

Redirecting is the move that turns a mystery into a diagnosis. Instead of failing the whole load or swallowing the problem, you send the offending rows down an error output into a dedicated table — an error/quarantine table — along with the columns SSIS gives you: the error code and the error column ID.

CREATE TABLE etl.RejectedRows (
    LoadRunId     int           NOT NULL,
    SourcePackage nvarchar(200) NOT NULL,
    ErrorCode     int           NULL,
    ErrorColumn   int           NULL,
    RawData       nvarchar(max) NULL,
    RejectedAtUtc datetime2     NOT NULL DEFAULT sysutcdatetime()
);

Now a bad row doesn't stop the world and doesn't vanish. It lands somewhere you can query on Monday and say "ah — forty-two rows failed because a supposedly-numeric field had a stray comma in it." That's a five-minute fix instead of a five-day investigation. The error code and column look cryptic at first; SSIS ships a way to translate them to readable descriptions, and it's worth wiring that in so your quarantine table reads in English.

Wrap the control flow so failure is caught, logged, and shouted about

Row-level handling deals with bad data. You also need to handle the package-level catastrophe — the connection that's down, the file that never arrived. That's what the OnError event handler is for. I put a standard one on every package that does three things, in order:

  1. Record it. Write a row to an ETL log table — package name, error message, timestamp, the run it belonged to.
  2. Make it visible. Trigger an actual alert. A failure nobody's told about is barely better than no logging at all.
  3. Leave the data in a known state. Which brings us to the most important part.

Make the whole load atomic: all or nothing

Here's the rule that prevents the 3am disaster more than anything else: a load should either fully succeed or fully roll back. Never half.

The half-loaded table is the root of almost every "why don't the numbers match" mystery. You avoid it by wrapping the meaningful unit of work in a transaction, so that if step four fails, steps one through three undo themselves and the table is left exactly as it was before the run started. SSIS gives you TransactionOption on containers and tasks for this; the pattern I reach for is a sequence container set to Required, with the tasks inside it set to Supported, so they enlist in one shared transaction.

A load that fails and rolls back cleanly is a good night's sleep. A load that fails halfway is a week of forensic accounting.

Test the failure on purpose

Here's the step almost everyone skips, and it's the one that actually proves your error handling exists: deliberately break something and watch what happens. Untested error handling isn't error handling — it's a comforting story you tell yourself.

So before I ship a package, I feed it a failure on purpose. I point it at a row I know violates something — a NULL in a NOT NULL column, a text value where a date belongs, a duplicate key — run it, and then confirm all four things behaved:

  • the bad row landed in the quarantine table, with a readable reason;
  • the OnError handler fired and wrote to the log;
  • the transaction rolled back, leaving the target exactly as it was;
  • the alert actually reached me.

If any one of those didn't happen, my handling has a hole — and far better I find it now, with a row I broke on purpose, than at 3am with a row production broke for me. A five-minute deliberate failure buys a lot of untroubled nights.

A short checklist I now run before shipping any package

  • Every rejectable component has a deliberate error setting — never a default I didn't choose.
  • Bad rows are redirected to a quarantine table, with error code and column captured, not silently ignored.
  • Every package has an OnError handler that logs, alerts, and leaves data consistent.
  • The real work runs in a transaction so a partial failure rolls back to a clean state.
  • I've actually tested a failure — fed it a broken row on purpose and confirmed it did the right thing. Untested error handling is just optimism in a fancy costume.

None of this is glamorous, and none of it shows up in a demo, where everything is clean and nothing goes wrong. It shows up at 3am, months later, when something upstream changes and your package has to decide whether to be a minor annoyance or a genuine disaster. Build it so it chooses "minor annoyance" every time.