Turning SSISDB Execution Logs into Failure Alerts Someone Will Read

The default SQL Agent failure email says one thing: step 3 of the nightly job failed. It does not say which package, which task, or why. Someone then opens SSMS at 7 a.m. and digs.

If your packages run from the SSISDB catalog, the answer is already in the database. This tutorial turns it into one useful alert per failed run.

Where the data lives

Three catalog views do most of the work:

  • catalog.executions has one row per run: folder, project, package, status, start and end time.
  • catalog.event_messages has the messages for each run. message_type = 120 is an error. message_type = 110 is a warning.
  • catalog.executable_statistics links executables (tasks) to their duration and result.

Execution status 4 means failed. 7 means succeeded. 3 means cancelled. Check the values against your own server before you rely on them.

One caveat: this only works for packages deployed to the catalog and run with the project deployment model. Packages run from the file system or msdb with dtexec log elsewhere, and need a different approach (a log provider, or dtexec output captured by the job).

The query

This returns the first error for each failed execution in the last day.

WITH failed AS (
    SELECT execution_id, folder_name, project_name, package_name,
           start_time, end_time
    FROM SSISDB.catalog.executions
    WHERE status = 4
      AND end_time >= DATEADD(HOUR, -24, SYSDATETIMEOFFSET())
), first_error AS (
    SELECT em.operation_id AS execution_id,
           em.message_source_name,
           em.message,
           em.message_code,
           ROW_NUMBER() OVER (PARTITION BY em.operation_id
                              ORDER BY em.event_message_id) AS rn
    FROM SSISDB.catalog.event_messages AS em
    JOIN failed AS f ON f.execution_id = em.operation_id
    WHERE em.message_type = 120
)
SELECT f.execution_id, f.folder_name, f.project_name, f.package_name,
       f.start_time, e.message_source_name AS failing_task,
       e.message_code, e.message
FROM failed AS f
JOIN first_error AS e ON e.execution_id = f.execution_id AND e.rn = 1;

We take the first error, not the last. A failed Data Flow Task usually produces a chain: the real cause, then a series of downstream messages such as "the component returned a failure code" and "Task failed". The first message is the cause. The rest is noise.

Send one alert per run

A failure in a parent package that calls ten children can raise errors in all of them. Two rules keep the volume down:

  1. Alert on the top-level execution only. Child runs started by an Execute Package Task share the parent's execution_id when the project is the same, so you mostly get this for free. Where a child runs from another project, group by parent job instead.
  2. Record what you have already sent. Keep a small table, ops.AlertSent(execution_id int primary key, sent_at datetimeoffset), and exclude those ids in the query. Without it, a job that polls every five minutes will resend the same failure all day.

Add context that cuts triage time

Two cheap additions help the person reading:

  • Duration against the usual. Compare end_time - start_time with the average for the same package over the last 14 successful runs. A run that failed after 4 minutes when it normally takes 90 is usually a connection or a permission. One that failed after 95 minutes is usually data.
  • A known-error lookup. Keep a table of message_code values and a one-line action: a login failure means check the service account password, a truncation error means check for schema drift upstream. It is a short table. It pays back in the first month.

Delivery

Do not build a mail client in T-SQL if you can avoid it. Two workable options:

  • Database Mail called from a SQL Agent job step running every few minutes. Simple, and fine for a small team.
  • A webhook. An Azure Logic App, a Teams channel, or your paging tool, called from a script or an Azure Function that runs the query. Better when the alert must reach an on-call person.

Route by project or folder. Finance loads go to the finance data owner. Everything else goes to the BI channel.

What this does not catch

  • Runs that never started. If the SQL Agent job is disabled or the server is down, no row appears in catalog.executions. Add a second check: alert if a package expected nightly has no execution after its usual start time plus a margin.
  • Runs that succeed with wrong data. A package that loads zero rows reports success. Row-count checks belong in the package or in a reconciliation harness.
  • Catalog retention. The Retention Period (days) setting and the cleanup job delete old history. Alert history you want to keep should be copied to your own table.

On Azure-SSIS

The same views exist when the SSISDB catalog is hosted on SQL Managed Instance or Azure SQL Database, so the query carries over unchanged. Pipeline-level failures in Data Factory are separate: those show in Azure Monitor, and need their own alert rule on the Execute SSIS Package activity.

If you would rather not own this

This is a few hours of work for an engineer who knows the catalog, and it removes most of the morning guesswork. It is also a standard part of SSIS Run and Maintain. If you are not sure what your nightly estate looks like when it fails, send us the number of packages, your SQL Server version, and what is prompting the question. A senior engineer will reply within a day, and an estate assessment is the usual first step.