SSIS 2012 OLE DB命令错误输出问题：无法获取存储过程输出参数

这个问题我之前帮同事排查过，核心原因其实是当存储过程抛出异常时，SQL Server会中断批处理的输出参数返回逻辑，SSIS的OLE DB命令组件会直接捕获到异常，根本没机会读取你在CATCH块里赋值的@ErrorMessage参数。

你看你的CATCH块，先给@ErrorMessage赋值，紧接着又用RAISERROR抛出了异常——一旦抛出异常，整个存储过程的执行就进入了错误终止状态，输出参数的返回通道就被切断了，SSIS自然拿不到值，只能得到NULL。

下面给你几个可行的解决思路，按推荐程度排序：

方案1：移除存储过程的异常抛出，用输出参数返回状态与错误信息 #

这是最直接且适配SSIS数据流场景的方案，核心是让存储过程不抛出异常，而是通过输出参数告诉SSIS执行结果和错误详情，再在SSIS里通过条件拆分处理失败记录。

修改后的存储过程示例： #

CREATE PROCEDURE dbo.UpdateRecord( 
    @NewValue1 INT, 
    @IdValue INT, 
    @ErrorMessage NVARCHAR(4000) OUTPUT,
    @IsSuccess BIT OUTPUT -- 新增：标记执行是否成功
) AS 
BEGIN 
    -- 初始化参数
    SET @IsSuccess = 1;
    SET @ErrorMessage = '';

    BEGIN TRY 
        MERGE TableA as Tgt 
        USING ( VALUES(@IdValue, @NewValue1) ) AS src(IdValue, MyName) 
        ON Tgt.Id = src.IdValue 
        WHEN NOT MATCHED THEN INSERT (Id, MyName) VALUES(Src.IdValue, Src.MyName) 
        WHEN MATCHED THEN UPDATE SET MyName = Src.MyName; 
    END TRY 
    BEGIN CATCH 
        -- 标记执行失败，并赋值详细错误信息
        SET @IsSuccess = 0;
        SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5));
        -- 移除RAISERROR，不再抛出异常
    END CATCH 
END

SSIS配置步骤： #

1. 在OLE DB命令的参数映射里，新增@IsSuccess的映射，方向选OUTPUT，数据类型选布尔型；
2. 确保@ErrorMessage的映射方向是OUTPUT，且数据流里对应的SaveError列是Unicode字符串（DT_WSTR），长度设为4000；
3. 在OLE DB命令组件之后添加条件拆分，拆分条件设为[IsSuccess] == False；
4. 把条件拆分出来的失败分支（即IsSuccess为False的记录）导向错误日志表，同时写入SaveError里的详细错误信息。

方案2：保留异常抛出，通过错误输出+日志表获取详细错误 #

如果这个存储过程还要被其他系统调用，必须保留异常抛出逻辑，可以用以下方式：

1. 修改存储过程的CATCH块，在抛出异常前，把错误信息写入一个错误日志表（比如dbo.SSIS_ErrorLog），同时写入当前处理的@IdValue作为关联标识：

   BEGIN CATCH 
       DECLARE @ErrorMsg NVARCHAR(4000);
       SELECT @ErrorMsg = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5));
       
       -- 写入错误日志表
       INSERT INTO dbo.SSIS_ErrorLog (RecordId, ErrorMessage, ErrorTime)
       VALUES (@IdValue, @ErrorMsg, GETDATE());
       
       -- 继续抛出异常
       RAISERROR (@ErrorMsg, ERROR_SEVERITY(), ERROR_STATE());
   END CATCH
   

2. 在SSIS的OLE DB命令组件上启用错误输出，把错误记录导向一个分支；
3. 在错误分支里添加查找组件，通过IdValue关联刚才的SSIS_ErrorLog表，读取详细的错误信息，再写入最终的错误日志。

注意：如果是高并发的数据流处理，建议给错误日志表加索引，或者用会话级临时表（比如#ErrorLog）避免冲突。

方案3：改用控制流Execute SQL Task批量处理（适合非逐行场景） #

如果你的数据可以批量处理，而非必须逐行在数据流里操作，可以把数据先写入临时表，然后在控制流里调用存储过程处理整个临时表，同时通过输出参数获取批量处理的错误信息。不过这个方案不适合需要逐行处理的场景。

额外注意事项 #

• 确保OLE DB命令的参数映射方向正确：输出参数一定要选OUTPUT，不要误选成INPUT；
• 数据类型必须严格匹配：@ErrorMessage是NVARCHAR(4000)，SSIS里对应的列必须是DT_WSTR（Unicode字符串），长度设为4000，不能用DT_STR（非Unicode）。

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