
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_logreaderdatabase 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
2 thoughts on “SSIS Error Report: Finding the Package That Failed”
Comments are closed.