通过xp_cmdshell调用SSIS包失败时，如何在存储过程中获取错误信息？

好问题！我之前处理过不少类似的场景，要在SSIS包执行失败时拿到具体的错误信息，核心是要捕获xp_cmdshell返回的完整输出内容，而不是只依赖返回码。下面给你详细的实现方案和代码示例：

核心思路 #

xp_cmdshell执行命令时会返回命令的所有输出内容（包括错误信息），我们可以把这些输出捕获到一个表变量或临时表里，当返回码不为0时，再从这个表中提取并拼接错误信息。

具体实现代码 #

-- 声明所需变量
DECLARE @ReturnCode INT
DECLARE @cmd NVARCHAR(MAX) = 'DTEXEC /FILE "C:\YourSSISPackage.dtsx"' -- 替换为你的DTEXEC命令
DECLARE @FullErrorMessage NVARCHAR(MAX)

-- 声明表变量存储xp_cmdshell的输出内容
DECLARE @CommandOutput TABLE (OutputLine NVARCHAR(MAX))

-- 执行xp_cmdshell，将输出插入表变量，同时捕获返回码
INSERT INTO @CommandOutput
EXEC @ReturnCode = xp_cmdshell @cmd

-- 判断SSIS包是否执行失败
IF @ReturnCode <> 0
BEGIN
    -- 拼接所有输出行，生成完整的错误信息
    -- SQL Server 2017+ 可以用STRING_AGG
    SELECT @FullErrorMessage = STRING_AGG(OutputLine, CHAR(13) + CHAR(10))
    FROM @CommandOutput
    WHERE OutputLine IS NOT NULL -- 过滤空行

    -- 如果是SQL Server 2016及以下版本，用FOR XML PATH拼接
    -- SELECT @FullErrorMessage = COALESCE(@FullErrorMessage + CHAR(13) + CHAR(10), '') + OutputLine
    -- FROM @CommandOutput
    -- WHERE OutputLine IS NOT NULL

    -- 这里可以根据需求处理错误：比如抛出自定义错误
    THROW 50000, @FullErrorMessage, 1;

    -- 或者将错误信息写入日志表
    -- INSERT INTO YourErrorLogTable (LogTime, ErrorDetails)
    -- VALUES (GETDATE(), @FullErrorMessage)
END

关键注意事项 #

• 权限问题：确保xp_cmdshell的执行账户有足够权限访问SSIS包文件、数据源等资源，否则可能会出现权限相关的错误输出。
• 精简输出：可以在DTEXEC命令中添加/REPORTING E参数，让它只输出错误信息，减少不必要的内容，比如：

  DTEXEC /FILE "C:\YourSSISPackage.dtsx" /REPORTING E
  

• 空行处理：过滤OutputLine IS NOT NULL是因为xp_cmdshell可能会返回空行，避免错误信息里出现多余的换行。

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