SSIS Package Failures

Overview

The catalog holds all of it, in three places that nothing joins:

What Where
The error text catalog.event_messages, at message type 120.
The task that raised it The execution_path on the same row.
The values at that moment catalog.event_message_context underneath it.

Putting the three together is most of what this page is.

Where to find it

Expand a server in the tree, expand a database holding an Integration Services catalog, then SSIS → Package Failures.

Three decisions worth knowing about

Ended unexpectedly is on the page and is not called a failure

Status 6 means the ISServerExec process stopped without writing a result: a host reboot, a service restart, a kill. It leaves no error message at all.

Somebody hunting for the error message that explains it will not find one, so the page says so on the row instead of letting them look.

Errors are grouped with the digits flattened

The same broken connection manager produces the same sentence in five packages with a different row number in each, and grouping on the raw text turns one fix into five incidents.

Grouped, the By message view puts the one real cause at the top.

Sensitive context values are shown as “set”

The catalog masks them, and the mask is not the value. A row of asterisks reads like a password somebody could copy, so the cell says set instead.

The two views

View What each row is
By message One row per distinct error, digits flattened, with the packages it hit. The view that finds the single cause.
By package One row per failing execution, in time order. The view for “what happened last night”.

Reading the grid

Column What it is
When When the execution started.
Package / Project What failed.
Task The execution_path of the task that raised the error, which is the container tree without the package having to be opened.
Error The message text.
Errors How many error messages this execution produced.
Ran for How long it got before it stopped.
Status Failed, canceled, or ended unexpectedly.
Started by The caller.

Double-click a row for the full error and the context values captured underneath it.

The toolbar

Control Options Default
Window 24 hours, 7 days, 30 days 7 days
View By message, By package By message
Show everything Clears a drill through filter.

Where the data comes from

Source What it gives
catalog.event_messages The messages, at message type 120 for errors, with package_path and execution_path.
catalog.event_message_context The connection strings, variable values and property values as they stood when the error was raised.
catalog.executions The execution, its status, its caller and its timing.
catalog.operation_messages The operation level text for a run with no package message.
Report Why you would go there
SSIS Packages Run The whole night, not just the failures.
SSIS Inside the Package Where in the package the time went before it broke.
SSIS Reruns Whether this package only ever works on the second attempt.
SSIS Job Disagreement Whether the Agent job reported success over this failure.
SSIS Connection Reach The connection in the error text, and what else uses it.
SSIS Running Now A package stuck rather than failed.

Frequently asked questions

The status is “ended unexpectedly” and there is no error. There will not be one. The process that was running the package stopped without writing a result. Look at the SSIS host: a restart, a reboot, or something that killed the process.

Five packages failed and the By message view shows one row. Because it is one error in five places. That is the finding, and it is one fix.

The context values show “set” instead of the value. Those are sensitive values. The catalog returns them masked, and the mask carries no information, so the page says whether the value was supplied rather than showing asterisks.

Why is the task column a path with backslashes? Because execution_path is the container tree: \Package\Foreach Loop[3]\Data Flow Task. It tells you exactly where in the package the error came from without opening the package.