通过SQL查询执行SSIS包：如何捕获包失败错误并获取日志

原脚本执行后始终显示成功，是因为start_execution默认是异步启动——SQL只确认包启动成功，不会等待包执行完成，自然也不会反馈执行失败的结果。要实现包失败时SQL抛出错误并获取日志，需按以下方式调整：

核心修改点 #

• 开启同步执行：让SQL等待SSIS包执行完成后再返回结果
• 检查执行状态：执行完成后查询包的最终运行状态
• 捕获错误日志：如果执行失败，提取对应的错误信息并抛出

修改后的完整SQL脚本 #

DECLARE @executionID BIGINT
DECLARE @executionStatus INT
DECLARE @errorMessage NVARCHAR(MAX)

-- 创建SSIS包执行实例
EXEC [SSISDB].[catalog].[create_execution] 
    @package_name=N'copy_file.dtsx',
    @execution_id=@executionID OUTPUT, 
    @folder_name=N'SSIS_COMMON',
    @project_name=N'Common', 
    @use32bitruntime=False, 
    @reference_id=Null

-- 设置源文件和目标文件参数
DECLARE @var0 SQL_VARIANT = N'D:\XXXX\XXX\XXX\target\to.xlsx'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @executionID,  @object_type=30, @parameter_name=N'target_file',@parameter_value=@var0

DECLARE @var1 SQL_VARIANT = N'D:\XXXX\XXX\XXX\from_.xlsx'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @executionID,  @object_type=30, @parameter_name=N'source_file',@parameter_value=@var1

-- 开启同步执行：让SQL等待包执行完成后再继续
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @executionID, 
    @object_type=50, -- 50代表执行级别的系统参数
    @parameter_name=N'SYNCHRONIZED',
    @parameter_value=1 -- 1表示启用同步模式

-- 启动包执行
EXEC [SSISDB].[catalog].[start_execution] @executionID

-- 查询包最终执行状态
SELECT @executionStatus = status 
FROM [SSISDB].[catalog].[executions] 
WHERE execution_id = @executionID

-- 状态码说明：1=已创建，2=运行中，3=失败，4=成功，5=已停止，6=已完成
IF @executionStatus = 3
BEGIN
    -- 提取错误日志信息，聚合所有错误消息
    SELECT @errorMessage = STRING_AGG(message, CHAR(13)+CHAR(10))
    FROM [SSISDB].[catalog].[event_messages]
    WHERE execution_id = @executionID
      AND event_name = 'OnError'
      ORDER BY message_time DESC

    -- 抛出包含错误详情的SQL错误
    RAISERROR(N'SSIS包执行失败: %s', 16, 1, ISNULL(@errorMessage, '未获取到详细错误信息'))
END
ELSE IF @executionStatus != 4
BEGIN
    RAISERROR(N'SSIS包执行状态异常，状态码: %d', 16, 1, @executionStatus)
END
GO

关键说明 #

• 同步执行参数：SYNCHRONIZED参数设置为1后，start_execution会阻塞直到包执行结束，确保能获取最终执行状态。
• 状态码判断：通过executions表的status字段判断结果，重点处理状态码3（失败）和4（成功），其他异常状态可按需扩展。
• 错误日志提取：从event_messages表筛选OnError事件，聚合错误信息后抛出，直接展示失败原因。

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