如何优化SSIS执行与SQL Server Agent作业及步骤的关联方式？

DevOps 小满

2026-6-13

优化SSIS执行与SQL Server Agent作业的关联查询

当SSIS包在SQL Server Agent作业中失败时，常规排查流程特别繁琐：得右键点开作业的日志文件查看器，先确认失败的步骤和对应包，再进到Integration Services Catalogs里找到目标包，还要等“所有执行”报告加载完成，效率很低。

你之前写了一条查询语句，能直接获取错误消息、项目、包和作业步骤信息，确实比手动排查方便不少：

select job.name, js.step_id, js.step_name, e.project_name, e.package_name, m.message_time, m.message, js.command
from SSISDB.internal.operation_messages m
join SSISDB.internal.executions e
    on m.operation_id = e.execution_id
left join msdb.dbo.sysjobhistory jh
    on jh.run_date = replace(convert(nvarchar(10), cast(m.message_time as date), 23),'-','')
    and jh.message like '%Execution ID: ' + upper(e.execution_id) + '%'
left join msdb.dbo.sysjobs job
    on job.job_id = jh.job_id
left join msdb.dbo.sysjobsteps js
    on js.job_id = job.job_id
    and js.step_id = jh.step_id
where m.message_type = 120
    and m.message_time > dateadd(day,-4,getdate())
order by m.message_time desc

但用通配符匹配sysjobhistory.message来关联Execution ID的方式，确实不够优雅，而且性能提升空间不大。下面给几个更优的关联方案：

方案1：自定义日志表捕获Execution ID关联（最推荐）

直接在SQL Server Agent的作业步骤里，执行SSIS包时捕获Execution ID，并存到自己建的日志表中，后续查询直接关联这个表，彻底避免模糊匹配。

步骤1：创建自定义日志表

CREATE TABLE dbo.SSISJobExecutionLog (
    log_id INT IDENTITY(1,1) PRIMARY KEY,
    job_id UNIQUEIDENTIFIER NOT NULL,
    step_id INT NOT NULL,
    execution_id BIGINT NOT NULL,
    start_time DATETIME NOT NULL,
    -- 加外键约束保证关联有效性
    FOREIGN KEY (execution_id) REFERENCES SSISDB.internal.executions(execution_id)
);

步骤2：修改作业步骤的执行命令

把原来调用SSIS包的命令改成带捕获Execution ID并写入日志的脚本（以T-SQL执行SSIS包为例）：

DECLARE @execution_id BIGINT;
-- 创建SSIS执行实例并获取Execution ID
EXEC [SSISDB].[catalog].[create_execution]
    @package_name = N'YourPackage.dtsx', -- 替换成你的包名
    @project_name = N'YourProject', -- 替换成你的项目名
    @folder_name = N'YourFolder', -- 替换成你的文件夹名
    @execution_id = @execution_id OUTPUT;

-- 把作业ID、步骤ID和Execution ID写入自定义日志表
INSERT INTO dbo.SSISJobExecutionLog (job_id, step_id, execution_id, start_time)
SELECT $(ESCAPE_SQUOTE(JOBID)), $(ESCAPE_SQUOTE(STEPID)), @execution_id, GETDATE();

-- 启动SSIS包执行
EXEC [SSISDB].[catalog].[start_execution] @execution_id;

注：$(ESCAPE_SQUOTE(JOBID))和$(ESCAPE_SQUOTE(STEPID))是SQL Server Agent的内置变量，会自动替换成当前作业和步骤的ID。

步骤3：优化后的查询语句

SELECT 
    job.name AS job_name,
    js.step_id,
    js.step_name,
    e.project_name,
    e.package_name,
    m.message_time,
    m.message AS error_message,
    js.command
FROM SSISDB.internal.operation_messages m
JOIN SSISDB.internal.executions e 
    ON m.operation_id = e.execution_id
JOIN dbo.SSISJobExecutionLog log 
    ON e.execution_id = log.execution_id
JOIN msdb.dbo.sysjobs job 
    ON log.job_id = job.job_id
JOIN msdb.dbo.sysjobsteps js 
    ON log.job_id = js.job_id 
    AND log.step_id = js.step_id
WHERE m.message_type = 120 -- 只筛选错误消息
    AND m.message_time > DATEADD(DAY, -4, GETDATE())
ORDER BY m.message_time DESC;

这个方案性能最优，关联逻辑清晰，完全避免了模糊匹配带来的性能损耗。

方案2：通过SSIS包传递作业元数据（需修改包）

如果你能修改SSIS包，可以在作业步骤执行时，把作业ID、步骤ID作为参数传递给包，然后在包的日志组件（比如“日志提供程序”）中记录这些信息，后续从SSISDB.internal.event_messages表中提取这些元数据，关联到作业信息。

这种方案不用建额外表，但需要调整SSIS包的配置，适合本身就需要在包中记录上下文信息的场景。

方案3：用扩展事件捕获关联信息（无侵入）

创建扩展事件会话，同时捕获SQL Server Agent的job_execution_start、job_step_start事件，以及SSIS的package_start事件，通过事件的时间戳和执行上下文来关联作业步骤和SSIS执行。

配置示例（简化版）：

CREATE EVENT SESSION [SSISJobCorrelation] ON SERVER 
ADD EVENT sqlserver.job_execution_start(
    ACTION(sqlserver.session_id)),
ADD EVENT sqlserver.job_step_start(
    ACTION(sqlserver.session_id)),
ADD EVENT ssis.package_start(
    ACTION(sqlserver.session_id))
ADD TARGET package0.event_file(SET filename=N'SSISJobCorrelation.xel')
WITH (STARTUP_STATE=ON);

后续通过解析扩展事件文件来关联数据，这种方案不用修改作业或包，但配置和解析成本较高，适合需要无侵入监控的场景。

内容的提问来源于stack exchange，提问作者StrangerToKindness

如何优化SSIS执行与SQL Server Agent作业及步骤的关联方式？

方案1：自定义日志表捕获Execution ID关联（最推荐） #

步骤1：创建自定义日志表 #

步骤2：修改作业步骤的执行命令 #

步骤3：优化后的查询语句 #

方案2：通过SSIS包传递作业元数据（需修改包） #