Troubleshooting SSIS Package Failures in SQL Server Agent Jobs

SSIS packages that run fine interactively will sometimes fail when called from a SQL Server Agent job. The failure mode is almost always one of three things: a permissions mismatch between the interactive user and the Agent service account or proxy, a configuration problem that only surfaces in the Agent execution context, or a resource or environment issue on the server. This guide walks through a structured diagnostic sequence, starting with the fastest checks and working toward deeper investigation.

When an SSIS package runs interactively in Visual Studio or SSMS, it runs under the current Windows user’s security context with access to that user’s registry hive, network shares, and credentials. When the same package runs under SQL Server Agent, the execution context changes to either the Agent service account or a configured proxy account. This difference is the root cause of most “works interactively, fails in Agent” problems.

The troubleshooting path depends on where the package is deployed. Modern deployments use the SSISDB catalog introduced with SQL Server 2012. Legacy deployments store packages in the msdb database or the file system via the Integration Services Service.

The Agent job history is the starting point for any failure. In SSMS, expand SQL Server Agent, right-click the job, and select View History. The step detail shows the execution context (the “Executed as user” line), the error code, and the error message.

For SSISDB-deployed packages, the step message will resemble:

Executed as user: DOMAIN\sqlagentsvc. 
The package execution failed. 
The step did not generate any output. 
Process Exit Code 1. 
The step failed.

This is expected and intentional: the full detail is in SSISDB. Note the “Executed as user” identity. If it shows the Agent service account rather than a proxy account, that is the first thing to evaluate.

For legacy file-system packages run via dtexec, the Agent job history captures more output directly, including SSIS error codes such as 0xC0202009 (OLE DB error on a data flow destination) or 0x80040E21 (OLE DB provider-specific error).

For SSISDB catalog deployments, the execution detail lives in catalog views. The query below joins catalog.executions, catalog.operation_messages, and catalog.event_messages to return error messages with full context: folder, project, package name, task name, and message source.

-- Recent SSIS errors from SSISDB -- last 7 days
USE SSISDB;
GO

SELECT
    e.server_name,
    e.folder_name,
    e.project_name,
    e.package_name,
    e.executed_as_name,
    e.execution_id,
    e.status,
    om.message_time,
    CASE om.message_source_type
        WHEN 10 THEN 'Entry API (T-SQL/CLR stored proc)'
        WHEN 20 THEN 'ISServerExec.exe (external process)'
        WHEN 30 THEN 'Package-level object'
        WHEN 40 THEN 'Control Flow task'
        WHEN 50 THEN 'Control Flow container'
        WHEN 60 THEN 'Data Flow task'
    END                          AS message_source_type,
    em.message_source_name       AS task_or_component,
    em.package_path,
    om.message
FROM catalog.operation_messages  AS om
LEFT JOIN catalog.executions     AS e
    ON om.operation_id = e.execution_id
LEFT JOIN catalog.event_messages AS em
    ON  om.operation_id       = em.operation_id
    AND om.operation_message_id = em.event_message_id
WHERE
    om.message_type  = 120                            -- errors only
    AND om.message_time >= DATEADD(DAY, -7, GETDATE())
ORDER BY
    om.message_time DESC;
GO

-- Find recent failed executions
USE SSISDB;
GO

SELECT TOP 20
    execution_id,
    folder_name,
    project_name,
    package_name,
    executed_as_name,
    start_time,
    end_time,
    status   -- 4 = Failed, 7 = Cancelled, 9 = Stopping
FROM catalog.executions
WHERE status IN (4, 7, 9)
ORDER BY start_time DESC;
GO

Then filter the error query with AND om.operation_id = <execution_id> to isolate one run.

SSISDB catalog executions support four logging levels, configured on the job step or via T-SQL. The default is Basic, which captures errors, warnings, and task-level information. If the error query in Step 2 does not return enough context, increase the logging level to Verbose temporarily.

To set the logging level for a specific execution via T-SQL before it runs (useful for scheduled jobs where the Agent job step cannot be easily modified temporarily):

-- Set logging level to Verbose for the next execution
-- Run this in SSISDB before the job fires, or add as a preceding job step

DECLARE @execution_id BIGINT;

EXEC catalog.create_execution
    @folder_name      = N'YourFolder',
    @project_name     = N'YourProject',
    @package_name     = N'YourPackage.dtsx',
    @execution_id     = @execution_id OUTPUT;

EXEC catalog.set_execution_parameter_value
    @execution_id  = @execution_id,
    @object_type   = 50,           -- execution parameter
    @parameter_name = N'LOGGING_LEVEL',
    @parameter_value = 3;          -- 3 = Verbose

EXEC catalog.start_execution
    @execution_id = @execution_id;
GO

For legacy packages that do not use SSISDB, logging is configured directly in the package using the SSIS log provider. Open the package in the Integration Services Projects extension for Visual Studio 2022 or 2026, right-click the Control Flow surface, select Logging, choose a log provider (SQL Server, flat file, or Windows Event Log), and select the events to capture: OnError, OnTaskFailed, and OnWarning are the minimum for failure diagnosis.

Permissions problems are the single most common cause of the “works interactively, fails in Agent” pattern. There are three layers to check.

SSISDB catalog roles

The account executing the package needs at minimum ssis_admin or db_ssisoperator in SSISDB, plus explicit permission to execute the specific folder and project. Check the current role membership:

-- Check SSISDB role membership for the Agent service account or proxy credential
USE SSISDB;
GO

SELECT
    dp.name        AS principal_name,
    dp.type_desc   AS principal_type,
    dr.name        AS role_name
FROM sys.database_role_members drm
JOIN sys.database_principals   dr ON drm.role_principal_id  = dr.principal_id
JOIN sys.database_principals   dp ON drm.member_principal_id = dp.principal_id
WHERE dr.name IN ('ssis_admin', 'db_ssisoperator')
ORDER BY dp.name;
GO

Proxy accounts

If the SSIS package accesses Windows resources (file shares, UNC paths, registry keys, or external services using Windows authentication), the Agent service account typically lacks those permissions. The correct resolution is to create a SQL Server Agent proxy account linked to a Windows credential that has the required access.

• In SSMS, expand SQL Server Agent, right-click Credentials under Security, and create a credential mapped to the Windows account with the required permissions.
• Under SQL Server Agent, expand Proxies, right-click SSIS Package Execution, and create a proxy using that credential.
• On the Agent job step, set the Run As value to the new proxy account.
• Verify the proxy account has access to the SSISDB folder/project and to any external resources the package touches.

File system access

Flat file sources, destinations, log files, and archive paths all require the executing account to have read or write access to the relevant directories. Mapped drive letters are not available to service accounts. Use UNC paths (\\server\share\file.csv) instead of drive letters, and verify the proxy account has the required share and NTFS permissions.

The ProtectionLevel property of an SSIS package controls how sensitive data (connection string passwords, credentials) is stored. An incorrectly set ProtectionLevel is a frequent cause of Agent job failures because the encryption key is tied to the user who saved the package.

Three job step configuration errors account for most setup-related failures.

Wrong job step type

For SSISDB catalog packages, the job step type must be set to SQL Server Integration Services Package. Using Operating System (CmdExec) with dtexec is valid for legacy file-system packages but is not the correct type for catalog deployments. Mixing these causes the step to look for a package in the wrong location.

Package source mismatch

Within the SSIS job step, the Package Source must match where the package lives: SSIS Catalog for SSISDB deployments, SQL Server for msdb-stored packages, or File System for .dtsx files. Verify the folder, project, and package name are correct and have not changed since the job was created.

Job owner permissions

The job owner must have permission to execute the package. If the job owner is a login that no longer has SSISDB access (for example, after a staff change), the job will fail even if a proxy is configured for the step. Check the job owner and update if necessary:

-- Check and update job owner
USE msdb;
GO

SELECT
    j.name        AS job_name,
    l.name        AS owner_login
FROM dbo.sysjobs j
JOIN sys.server_principals l ON j.owner_sid = l.sid
WHERE j.name = N'YourJobName';

-- Update owner if needed
EXEC dbo.sp_update_job
    @job_name  = N'YourJobName',
    @owner_login_name = N'sa';   -- or a service account login
GO

Large or complex SSIS packages can exhaust server resources, particularly when scheduled during peak load. Resource exhaustion manifests as timeout errors, out-of-memory errors, or unexpected cancellations without a clear SSIS error code.

Disk space

SSIS buffers spill to disk when memory is insufficient. Check free space on the drive configured as the BufferTempStoragePath (defaults to the system temp directory). Flat file destinations and archive tasks also fail silently when the target drive is full.

Memory

Check available memory during package execution using the DMV sys.dm_os_ring_buffers or by monitoring the Windows Performance Monitor counter for Available MBytes. SQL Server Error Log entry 701 (insufficient memory in resource pool) during a package execution confirms memory pressure.

Timeouts

OLE DB connection managers have a default connection timeout of 60 seconds and a command timeout of 0 (no limit) by default, but these can be overridden. Check the ConnectionTimeout and CommandTimeout properties on Execute SQL tasks and OLE DB sources if the failure message includes “timeout expired.”

When SSISDB query results and Agent job history do not identify the root cause, check the SQL Server Error Log and the Windows Application Event Log for server-level errors occurring at the same time as the package failure.

SQL Server Error Log

In SSMS, expand Management, right-click SQL Server Logs, and select View SQL Server Log. Filter the time window to the package execution period. Look for:

Windows Application Event Log

Open Event Viewer and filter the Application log for the time window of the failure. Sources to look for: MsDtsSrvr (legacy SSIS service), SQLISPackage (SSIS package execution), SQLAgent, and the operating system itself for disk or memory events. Out-of-memory conditions and network connectivity failures frequently surface here when they do not appear in SSISDB or Agent history.

Extended Events can capture SQL Server-level activity during SSIS package execution, including OLE DB calls, login events, query execution, and wait statistics. This is useful when the SSISDB logs point to a general OLE DB error but do not identify which query or object caused it.

The session below captures failed logins, OLE DB errors, and long-running queries (over 5 seconds) filtered to the SSIS execution window. Write output to an event_file target so it persists after the session ends.

-- Extended Events session for SSIS Agent job troubleshooting
-- Update the file path before running

CREATE EVENT SESSION [SSIS_Agent_Triage] ON SERVER
ADD EVENT sqlserver.error_reported (
    WHERE severity >= 14
),
ADD EVENT sqlserver.sql_batch_completed (
    WHERE duration > 5000000   -- over 5 seconds (microseconds)
),
ADD EVENT sqlserver.rpc_completed (
    WHERE duration > 5000000
),
ADD EVENT sqlserver.login_error (
    ACTION (sqlserver.server_principal_name, sqlserver.client_app_name)
)
ADD TARGET package0.event_file (
    SET filename = N'C:\XEvents\SSIS_Agent_Triage.xel',
        max_file_size = 50,        -- MB
        max_rollover_files = 5
)
WITH (
    MAX_DISPATCH_LATENCY = 5 SECONDS
);
GO

-- Start the session before the job fires
ALTER EVENT SESSION [SSIS_Agent_Triage] ON SERVER STATE = START;
GO

-- After the failure, stop and review the .xel file in SSMS
ALTER EVENT SESSION [SSIS_Agent_Triage] ON SERVER STATE = STOP;
GO

-- Drop when troubleshooting is complete
DROP EVENT SESSION [SSIS_Agent_Triage] ON SERVER;
GO

Open the resulting .xel file in SSMS via File > Open > File, then filter by the timestamp of the package execution. Sort by duration descending to identify slow queries that may have caused timeouts, or filter to error_reported events to find the root SQL Server error.