SSIS Error Report: Finding the Package That Failed

SQL Server Auditing: SSIS Error Report: Finding the Package That Failed

Applies to SQL Server 2019, 2022 and 2025 with SSIS packages deployed to the SSIS catalog (SSISDB). The script runs in Windows PowerShell 5.1 or PowerShell 7.

When an SSIS job fails, the Agent job history usually says something like “The package execution failed” and nothing else. The real error, and the package, task and component that raised it, is in the SSIS catalog. This report collects those errors from every server into one table, so the morning check is a single query rather than a tour of the Integration Services reports on each server.

What the report shows

  • Server, folder and project. Where the package lives in the catalog.
  • Entry package and failing package. The package that was started, and the one that actually failed. With a parent package that runs child packages, these are different, and the failing one is the one you need.
  • Failing task and component. For example a Data Flow Task, and the OLE DB Destination inside it.
  • Start and end time, and the environment the run used.
  • The error messages, in order, with the first one on the summary report.

Why it matters

SSIS failures are usually somebody else’s data problem: a file that didn’t arrive, a column that got longer, a password that expired on a source system. But they land with the DBA, because it’s a SQL Agent job that failed. The faster you can say “package X, task Y, error Z”, the faster it gets to the person who can fix it.

The first error is nearly always the cause. SSIS reports a cascade: the component fails, then the task, then the package, then the job, each with its own message. The summary shows the first one and a count, which is what you want at 8am.

The 30-day view finds the packages that fail every week and get rerun by hand. Those are worth fixing properly, and the count is the evidence you need to get the time.

The script

For each server, it checks whether SSISDB exists, reads failed and unexpectedly ended executions from catalog.executions, joins the error messages from catalog.event_messages and adds the ones it hasn’t already stored to dbo.SsisErrorHistory. It looks back 48 hours on each run, so a missed run doesn’t lose anything.

# Invoke-SsisErrorAudit.ps1
# Collects failed SSIS catalog executions from every server that has SSISDB,
# with the package, task and component that raised each error. Child packages
# are named properly, not just the parent that started them.
# Schedule it hourly; it only adds errors it hasn't seen before.

# ---- Settings ---------------------------------------------------------------
$ServerListPath    = 'C:\DBA\Servers.txt'
$InventoryServer   = 'SQLMON01'
$InventoryDatabase = 'DBA'
$HoursBack         = 48       # overlap is fine: duplicates are skipped
# -----------------------------------------------------------------------------

. "$PSScriptRoot\DbaReportHelpers.ps1"

# Runs on each server with an SSIS catalog.
# Status 4 = failed, 6 = ended unexpectedly. Message type 120 = error.
$SsisQuery = @"
SELECT
    e.execution_id                              AS ExecutionId,
    e.folder_name                               AS FolderName,
    e.project_name                              AS ProjectName,
    e.package_name                              AS EntryPackage,
    COALESCE(m.package_name, e.package_name)    AS FailingPackage,
    m.message_source_name                       AS FailingTask,
    m.subcomponent_name                         AS FailingComponent,
    CASE e.status WHEN 4 THEN 'Failed'
                  WHEN 6 THEN 'Ended unexpectedly' END AS ExecutionStatus,
    CAST(e.start_time AS DATETIME2(0))          AS StartTime,
    CAST(e.end_time   AS DATETIME2(0))          AS EndTime,
    e.environment_name                          AS EnvironmentName,
    m.event_message_id                          AS EventMessageId,
    CAST(m.message_time AS DATETIME2(0))        AS MessageTime,
    LEFT(m.message, 4000)                       AS ErrorMessage
FROM SSISDB.catalog.executions AS e
LEFT JOIN SSISDB.catalog.event_messages AS m
       ON m.operation_id = e.execution_id
      AND m.message_type = 120
WHERE e.status IN (4, 6)
  AND e.start_time >= DATEADD(HOUR, -@HoursBack, SYSDATETIMEOFFSET())
ORDER BY e.execution_id, m.event_message_id;
"@

$Servers = Get-ServerList -Path $ServerListPath
$Repo    = Open-SqlConnection -Server $InventoryServer -Database $InventoryDatabase

try {
    Invoke-SqlNonQuery $Repo @"
IF OBJECT_ID('dbo.SsisErrorHistory', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.SsisErrorHistory
    (
        CaptureDate      DATETIME2(0)   NOT NULL
                         CONSTRAINT DF_SsisErrorHistory_CaptureDate DEFAULT SYSDATETIME(),
        ServerName       NVARCHAR(128)  NOT NULL,
        ExecutionId      BIGINT         NOT NULL,
        FolderName       NVARCHAR(128)  NULL,
        ProjectName      NVARCHAR(128)  NULL,
        EntryPackage     NVARCHAR(260)  NULL,
        FailingPackage   NVARCHAR(260)  NULL,
        FailingTask      NVARCHAR(4000) NULL,
        FailingComponent NVARCHAR(4000) NULL,
        ExecutionStatus  NVARCHAR(30)   NULL,
        StartTime        DATETIME2(0)   NULL,
        EndTime          DATETIME2(0)   NULL,
        EnvironmentName  NVARCHAR(128)  NULL,
        EventMessageId   BIGINT         NOT NULL,   -- 0 when the run failed with no error message
        MessageTime      DATETIME2(0)   NULL,
        ErrorMessage     NVARCHAR(4000) NULL,
        CONSTRAINT PK_SsisErrorHistory PRIMARY KEY (ServerName, ExecutionId, EventMessageId)
    );
END;
"@

    foreach ($Server in $Servers) {
        try {
            $Source = Open-SqlConnection -Server $Server
            try {
                $HasCatalog = (Invoke-SqlQuery $Source "SELECT DB_ID('SSISDB') AS Id").Rows[0].Id
                if ($HasCatalog -is [System.DBNull]) {
                    Write-Verbose "$Server : no SSISDB"
                    continue
                }
                $Errors = Invoke-SqlQuery $Source $SsisQuery @{ HoursBack = $HoursBack }
            }
            finally {
                $Source.Close()
            }

            $Added = 0
            foreach ($E in $Errors.Rows) {
                $MessageId = if ($E.EventMessageId -is [System.DBNull]) { 0 } else { [long]$E.EventMessageId }

                $Known = (Invoke-SqlQuery $Repo @"
SELECT COUNT(*) AS N FROM dbo.SsisErrorHistory
WHERE ServerName = @ServerName AND ExecutionId = @ExecutionId AND EventMessageId = @EventMessageId;
"@ @{ ServerName = $Server; ExecutionId = [long]$E.ExecutionId; EventMessageId = $MessageId }).Rows[0].N

                if ($Known -gt 0) { continue }

                $Row = [ordered]@{ ServerName = $Server }
                foreach ($Column in $Errors.Columns.ColumnName) {
                    $Row[$Column] = $E[$Column]
                }
                $Row.EventMessageId = $MessageId

                Add-InventoryRow -Connection $Repo -Table 'dbo.SsisErrorHistory' -Row $Row
                $Added++
            }

            Write-Host "$Server : $($Errors.Rows.Count) error rows, $Added new"
        }
        catch {
            Write-Warning "$Server : $($_.Exception.Message)"
        }
    }
}
finally {
    $Repo.Close()
}
DbaReportHelpers.ps1: the shared helper file (save it in the same folder as the script)
# DbaReportHelpers.ps1
# Shared helpers for the auditing scripts. Save it next to them and dot-source it:
#     . "$PSScriptRoot\DbaReportHelpers.ps1"
# Windows Authentication throughout: run the scripts as a service account that
# can reach every server. No passwords live in any of these files.

function Get-ServerList {
    # One server per line. Blank lines and anything after # are ignored,
    # and duplicates (in any case) are removed.
    param([Parameter(Mandatory)][string]$Path)

    Get-Content -Path $Path |
        ForEach-Object { ($_ -split '#')[0].Trim() } |
        Where-Object { $_ } |
        Sort-Object -Unique
}

function Get-ComputerName {
    # SERVER\INSTANCE -> SERVER, for the Windows calls (CIM, ping, remoting)
    param([Parameter(Mandatory)][string]$Server)
    ($Server -split '\\')[0]
}

function Open-SqlConnection {
    param(
        [Parameter(Mandatory)][string]$Server,
        [string]$Database = 'master'
    )

    $Conn = New-Object System.Data.SqlClient.SqlConnection
    $Conn.ConnectionString = "Server=$Server;Database=$Database;Integrated Security=True;" +
                             "TrustServerCertificate=True;Connect Timeout=15;Application Name=DBA reports"
    $Conn.Open()
    $Conn
}

function Invoke-SqlQuery {
    # Runs a query and returns a DataTable (the leading comma stops PowerShell unrolling it).
    param($Connection, [string]$Sql, [hashtable]$Parameters = @{})

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    foreach ($Name in $Parameters.Keys) {
        $null = $Cmd.Parameters.AddWithValue("@$Name", $Parameters[$Name])
    }
    $Table = New-Object System.Data.DataTable
    $null = (New-Object System.Data.SqlClient.SqlDataAdapter $Cmd).Fill($Table)
    , $Table
}

function Invoke-SqlNonQuery {
    param($Connection, [string]$Sql)

    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = $Sql
    $Cmd.CommandTimeout = 300
    $null = $Cmd.ExecuteNonQuery()
}

function Add-InventoryRow {
    # Inserts one row from an ordered hashtable of column = value.
    # Every value goes in as a parameter, never pasted into the SQL text.
    param(
        $Connection,
        [Parameter(Mandatory)][string]$Table,
        [Parameter(Mandatory)][System.Collections.IDictionary]$Row
    )

    $Columns = @($Row.Keys)
    $Cmd = $Connection.CreateCommand()
    $Cmd.CommandText = "INSERT INTO $Table (" + ($Columns -join ', ') + ') VALUES (' +
                       (($Columns | ForEach-Object { "@$_" }) -join ', ') + ');'

    foreach ($Column in $Columns) {
        $Value = $Row[$Column]
        if ($null -eq $Value -or $Value -is [System.DBNull] -or
            ($Value -is [string] -and $Value.Trim() -eq '')) {
            $Value = [System.DBNull]::Value
        }
        $null = $Cmd.Parameters.AddWithValue("@$Column", $Value)
    }
    $null = $Cmd.ExecuteNonQuery()
}

The report queries

The first is the morning list. The second shows repeat offenders. The third runs on each instance and catches SSIS job steps that failed outside the catalog, for packages still run from the file system or msdb, where the step command shows the package path.

-- 1. Failures in the last 24 hours: one row per failed run, first error first
--    (run in the DBA database)
WITH Ranked AS
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ServerName, ExecutionId
                              ORDER BY MessageTime, EventMessageId) AS ErrorNo,
           COUNT(*)     OVER (PARTITION BY ServerName, ExecutionId) AS ErrorCount
    FROM dbo.SsisErrorHistory
    WHERE StartTime >= DATEADD(HOUR, -24, SYSDATETIME())
)
SELECT ServerName, FolderName, ProjectName, EntryPackage, FailingPackage,
       FailingTask, FailingComponent, StartTime, EndTime, ErrorCount,
       ErrorMessage AS FirstError
FROM Ranked
WHERE ErrorNo = 1
ORDER BY StartTime DESC;

-- 2. Repeat offenders over 30 days
SELECT ServerName, ProjectName, FailingPackage,
       COUNT(DISTINCT ExecutionId) AS FailedRuns,
       MAX(StartTime)              AS LastFailure
FROM dbo.SsisErrorHistory
WHERE StartTime >= DATEADD(DAY, -30, SYSDATETIME())
GROUP BY ServerName, ProjectName, FailingPackage
ORDER BY FailedRuns DESC;

-- 3. SSIS job steps that failed outside the catalog (file system or msdb packages),
--    run on each instance. The command shows which package the step runs.
SELECT j.name                                          AS JobName,
       s.step_name                                     AS StepName,
       msdb.dbo.agent_datetime(h.run_date, h.run_time) AS RunTime,
       s.command                                       AS StepCommand,
       LEFT(h.message, 2000)                           AS Message
FROM msdb.dbo.sysjobhistory AS h
JOIN msdb.dbo.sysjobs       AS j ON j.job_id = h.job_id
JOIN msdb.dbo.sysjobsteps   AS s ON s.job_id = h.job_id AND s.step_id = h.step_id
WHERE s.subsystem = 'SSIS'
  AND h.run_status = 0
  AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(DAY, -1, GETDATE())
ORDER BY RunTime DESC;

Watch out for

  • Permissions on the catalog. The catalog views only show executions the caller has permission to see. Add the service account to the ssis_logreader database role in SSISDB. It reads all operations without being able to change anything.
  • Logging level. Errors are recorded at the Basic logging level and above. At None you get the failure but no messages. Performance level keeps errors and warnings only, which is fine for this report.
  • Retention. SSISDB keeps operation history for 365 days by default, and the cleanup job can be slow on a big catalog. Many people cut it to 30 or 60 days. Because this report copies the errors out, you can keep SSISDB small and the error history long.
  • Script Task errors. A Script Task that throws an exception often logs a generic message. The useful text is sometimes in a second or third error row, so look at the whole run, not just the first error, before blaming the wrong thing.
  • Time zones. Catalog times are datetimeoffset. They’re stored here in the server’s local time, so a server in another time zone will look an hour or more out.

Where it fits

This is part of my Auditing section, with the licensing, availability, disk space, inventory, database and backup reports. Troubleshooting failed jobs in general is covered in the SQL Server DBA Handbook.

Microsoft’s page on catalog.event_messages lists every column and message type.

The other Auditing reports: Licensing | Availability: ping and response time | Disk space alerts | Server inventory | Database inventory | Backups

Clive Roberts DBA