Running SSIS catalog packages from SQL Server Agent: environments, proxies, 32-bit and failure diagnosis

Packages deployed to the SSIS catalog (SSISDB) are scheduled with a SQL Agent job step of type SQL Server Integration Services Package. The job step only starts the execution. The catalog does the logging, so troubleshooting means reading SSISDB, not just job history.

#Before you schedule

• Project deployed to the catalog: Integration Services Catalogs → SSISDB → folder → project.
• An environment per target (for example PROD) with variables for connection strings, paths and flags. Mark secrets as sensitive.
• The project references the environment, and project or package parameters are mapped to its variables.
• Decide the identity: the Agent service account, or a proxy (recommended when the package reads files or remote servers).

-- environment and variable, scripted
EXEC SSISDB.catalog.create_environment @folder_name = N'Finance', @environment_name = N'PROD';
EXEC SSISDB.catalog.create_environment_variable @folder_name = N'Finance', @environment_name = N'PROD',
     @variable_name = N'SourceConn', @data_type = N'String', @sensitive = 0,
     @value = N'Data Source=sql01;Initial Catalog=FinanceStage;Integrated Security=SSPI;', @description = N'Staging DB';

DECLARE @ref bigint;
EXEC SSISDB.catalog.create_environment_reference @folder_name = N'Finance', @project_name = N'GL_Load',
     @environment_name = N'PROD', @reference_type = 'R', @reference_id = @ref OUTPUT;
EXEC SSISDB.catalog.set_object_parameter_value @object_type = 20, @folder_name = N'Finance', @project_name = N'GL_Load',
     @parameter_name = N'SourceConn', @parameter_value = N'SourceConn', @value_type = 'R';   -- R = referenced variable

#The job step

In SSMS: New Job Step → Type: SQL Server Integration Services Package → Package source: SSIS Catalog, then choose the server and the package.

The script SSMS generates uses the SSIS subsystem with a /ISSERVER command line:

EXEC msdb.dbo.sp_add_jobstep @job_name = N'Finance - ETL - GL load', @step_name = N'Run GL_Load', @subsystem = N'SSIS',
     @proxy_name = N'Finance file proxy',
     @command = N'/ISSERVER "\"\SSISDB\Finance\GL_Load\Load_GL.dtsx\"" /SERVER "\"sql01\"" /ENVREFERENCE 1 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E';

Create the step in SSMS and script it out rather than writing the /ISSERVER line by hand. The environment reference ID differs between servers.

SYNCHRONIZED = True (set by the SSIS job step) makes the step wait for the package to finish, so the job outcome reflects the package outcome.

#Alternative: start the execution from a T-SQL step

Useful when the job needs logic around the run, or you want the execution ID:

DECLARE @exec_id bigint, @ref bigint =
  (SELECT reference_id FROM SSISDB.catalog.environment_references r
   JOIN SSISDB.catalog.projects p ON p.project_id = r.project_id
   WHERE p.name = N'GL_Load' AND r.environment_name = N'PROD');

EXEC SSISDB.catalog.create_execution @folder_name = N'Finance', @project_name = N'GL_Load', @package_name = N'Load_GL.dtsx',
     @reference_id = @ref, @use32bitruntime = 0, @execution_id = @exec_id OUTPUT;
EXEC SSISDB.catalog.set_execution_parameter_value @exec_id, @object_type = 50, @parameter_name = N'SYNCHRONIZED', @parameter_value = 1;
EXEC SSISDB.catalog.set_execution_parameter_value @exec_id, @object_type = 50, @parameter_name = N'LOGGING_LEVEL', @parameter_value = 1;
EXEC SSISDB.catalog.start_execution @exec_id;

-- fail the job step if the package didn't succeed (status 7 = succeeded)
IF (SELECT status FROM SSISDB.catalog.executions WHERE execution_id = @exec_id) <> 7
    THROW 50020, 'SSIS package failed - see SSISDB.catalog.event_messages for this execution.', 1;

A T-SQL step runs as the job owner or Agent service account, and SSISDB requires Windows authentication for executions. Run it under an identity that is a Windows login with rights in the SSISDB folder.

#Why did it fail?

Job history usually says only "The package execution failed". The detail is in SSISDB:

-- recent executions and outcome (status: 1 created, 2 running, 3 canceled, 4 failed, 5 pending, 6 ended unexpectedly, 7 succeeded, 9 completed)
SELECT TOP 20 execution_id, folder_name, project_name, package_name, status, start_time, end_time, executed_as_name, use32bitruntime
FROM SSISDB.catalog.executions ORDER BY execution_id DESC;

-- error messages for one execution
SELECT message_time, message_source_name AS component, LEFT(message, 1000) AS message
FROM SSISDB.catalog.event_messages
WHERE operation_id = 123456 AND event_name = 'OnError'
ORDER BY message_time;

The built-in All Executions report (right-click SSISDB → Reports) shows the same thing with drill-down.

#Catalog maintenance

SELECT property_name, property_value FROM SSISDB.catalog.catalog_properties;   -- RETENTION_WINDOW, OPERATION_CLEANUP_ENABLED, MAX_PROJECT_VERSIONS
EXEC SSISDB.catalog.configure_catalog @property_name = N'RETENTION_WINDOW', @property_value = 60;   -- days

The SSIS Server Maintenance Job (created with the catalog) applies the retention. Make sure it is enabled and succeeding.

Tip

If SSISDB is in an availability group, enable SSIS support for Always On. Create the SSIS jobs on every replica, and make each check that it's on the primary before it runs.