Monitoring SQL Agent job and SSIS package failures: a practical guide

Most overnight ETL failures are discovered the next morning — by someone looking at a report. SQL Server already records everything needed to find failures, slow runs and stale data sooner. This guide shows where that information lives and the queries to get it out.

1. Failed SQL Agent jobs

Job history is stored in msdb.dbo.sysjobhistory. A run_status of 0 means failed (1 succeeded, 2 retry, 3 cancelled, 4 in progress). Dates and times are stored as integers; the msdb.dbo.agent_datetime helper converts them:

SELECT j.name                                           AS job_name,
       h.step_id,
       h.step_name,
       msdb.dbo.agent_datetime(h.run_date, h.run_time)  AS run_started,
       h.run_duration,                                   -- HHMMSS as an integer
       h.message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs       AS j ON j.job_id = h.job_id
WHERE h.run_status = 0
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(day, -1, GETDATE())
ORDER BY run_started DESC;

Step 0 rows describe the job outcome as a whole; other step IDs show which step failed and its message.

2. Failed SSIS executions in the SSIS catalog

For packages deployed to SSISDB, catalog.executions holds one row per execution. A status of 4 means failed (7 succeeded, 2 running, 3 cancelled, 6 ended unexpectedly):

SELECT e.execution_id,
       e.folder_name,
       e.project_name,
       e.package_name,
       e.start_time,
       e.end_time
FROM SSISDB.catalog.executions AS e
WHERE e.status IN (4, 6)
  AND e.start_time >= DATEADD(day, -1, SYSDATETIMEOFFSET())
ORDER BY e.start_time DESC;

The actual error text is in catalog.event_messages, where message_type = 120 is an error (110 is a warning):

SELECT m.message_time,
       m.package_name,
       m.message_source_name,   -- the task or component that raised it
       m.message
FROM SSISDB.catalog.event_messages AS m
WHERE m.operation_id = @execution_id
  AND m.message_type = 120
ORDER BY m.message_time;

3. Runs that succeeded — but too slowly

A job that succeeds in twice its usual time is often the first sign of a problem. Compare each run against its own history. run_duration is HHMMSS, so convert it to seconds first:

WITH runs AS (
    SELECT j.name AS job_name,
           msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_started,
           (h.run_duration / 10000) * 3600
         + (h.run_duration / 100 % 100) * 60
         +  h.run_duration % 100                           AS duration_s
    FROM msdb.dbo.sysjobhistory AS h
    JOIN msdb.dbo.sysjobs AS j ON j.job_id = h.job_id
    WHERE h.step_id = 0 AND h.run_status = 1
)
SELECT r.job_name, r.run_started, r.duration_s, b.avg_s,
       CAST(r.duration_s * 1.0 / NULLIF(b.avg_s, 0) AS decimal(5,2)) AS ratio
FROM runs AS r
CROSS APPLY (
    SELECT AVG(p.duration_s) AS avg_s
    FROM (SELECT TOP (30) duration_s
          FROM runs AS x
          WHERE x.job_name = r.job_name AND x.run_started < r.run_started
          ORDER BY x.run_started DESC) AS p
) AS b
WHERE r.run_started >= DATEADD(day, -1, GETDATE())
  AND r.duration_s > 1.5 * b.avg_s;

By default SQL Agent keeps limited history, so check the retention settings (job history log size) or copy history into your own table if you want long-term trends.

4. Row counts and silent data loss

A job can succeed while loading far fewer rows than usual — for example, when a source extract is empty. Agent history doesn't record row counts, so log them yourself at the end of each load (a small audit table with load name, run time and rows inserted/updated works well) and compare against recent runs in the same way as runtime.

5. Data freshness and SLAs

The business question is rarely “did the job succeed?” but “is today's data there by 7 a.m.?”. Record a load timestamp on key tables (or in the audit table) and check it against an agreed deadline:

SELECT 'dw.FactSales' AS table_name,
       MAX(LoadDateTime)  AS last_loaded,
       CASE WHEN MAX(LoadDateTime) < CAST(CAST(GETDATE() AS date) AS datetime2)
            THEN 'STALE' ELSE 'FRESH' END AS freshness
FROM dw.FactSales;

6. From queries to monitoring

Scheduled as a job that emails or posts results, these queries are a workable first monitor. As the estate grows, the gaps show: no single view across servers, no history beyond retention, no link between a failed job and the reports it feeds, and alerts that nobody owns.

Dataventra Monitor brings SQL Agent jobs, SSIS executions, SLAs, runtimes, row counts and freshness into one operational view — and Automated Incident Management turns failures into Jira tickets with the diagnostics attached.