使用TransactionScope结合start_execution执行SSIS包时，如何在包失败时回滚SQL事务

嗨，我来帮你梳理下这个问题的解决思路！首先得明确核心痛点：SSIS的start_execution默认是异步执行的，要是直接把它套在TransactionScope里，事务可能在包还没跑完就自动提交了，完全达不到“失败回滚”的预期。所以咱们得先把SSIS改成同步执行，等它跑完再决定事务的走向。

先回顾下你的需求：

• 跑SSIS包前执行一些SQL查询
• 包失败时回滚这些SQL和前置逻辑
• 等待包执行完成

接下来一步步说具体的实现方案：

1. 把SSIS包改成同步执行 #

要让start_execution阻塞到包执行完成，得通过catalog.set_execution_parameter_value设置SYNCHRONIZED参数为1（也就是true）。这个参数会强制SSIS在执行完成后才返回结果，这样咱们就能在代码里等它跑完再处理事务。

2. 在TransactionScope里编排完整逻辑 #

这里的关键是把所有需要回滚的操作（包括前置SQL）都放在TransactionScope内部，然后根据包的执行结果决定是否提交事务：

• 首先开启TransactionScope（建议用默认的Required事务级别，确保所有操作都纳入同一事务）
• 执行你的前置SQL查询：注意要在TransactionScope开启后创建数据库连接，这样连接才会自动加入当前事务
• 调用SSIS的执行API：先创建执行实例，设置同步参数，再启动执行
• 执行完成后，查询SSIS目录的catalog.executions视图获取执行状态（状态码7代表成功，4代表失败）
• 如果包成功，就调用scope.Complete()提交事务；如果失败，直接抛出异常或者让TransactionScope自然结束，它会自动回滚所有操作

3. 关键注意事项 #

• 分布式事务问题：如果你的前置SQL和SSIS包操作的是不同数据库，可能需要开启MSDTC（分布式事务协调器），否则事务可能无法正常回滚
• 异常处理：一定要捕获执行过程中的所有异常（比如SSIS调用失败、包执行出错），确保异常能触发TransactionScope的回滚逻辑
• 连接生命周期：所有涉及事务的数据库连接，都要在TransactionScope内部创建和打开，不然不会被纳入事务

给你一段简化的C#代码示例，你可以参考着调整：

using (var scope = new TransactionScope(TransactionScopeOption.Required))
{
    try
    {
        // 执行前置SQL操作
        using (var sqlConn = new SqlConnection("你的业务数据库连接串"))
        {
            sqlConn.Open();
            var preCmd = new SqlCommand("INSERT/UPDATE/DELETE 你的前置SQL语句", sqlConn);
            preCmd.ExecuteNonQuery();
        }

        // 连接SSIS目录数据库
        using (var ssisConn = new SqlConnection("SSIS目录的连接串"))
        {
            ssisConn.Open();
            long executionId;

            // 创建SSIS执行实例
            var createExecCmd = new SqlCommand(
                "EXEC catalog.create_execution @folder_name = N'你的SSIS文件夹名', " +
                "@project_name = N'你的SSIS项目名', @package_name = N'你的SSIS包名', @execution_id OUTPUT",
                ssisConn);
            createExecCmd.Parameters.Add("@execution_id", SqlDbType.BigInt).Direction = ParameterDirection.Output;
            createExecCmd.ExecuteNonQuery();
            executionId = (long)createExecCmd.Parameters["@execution_id"].Value;

            // 设置同步执行参数
            var setSyncCmd = new SqlCommand(
                "EXEC catalog.set_execution_parameter_value @execution_id = @execId, " +
                "@object_type = 50, @parameter_name = N'SYNCHRONIZED', @parameter_value = 1",
                ssisConn);
            setSyncCmd.Parameters.Add("@execId", SqlDbType.BigInt).Value = executionId;
            setSyncCmd.ExecuteNonQuery();

            // 启动执行（同步模式，阻塞到完成）
            var startExecCmd = new SqlCommand(
                "EXEC catalog.start_execution @execution_id = @execId",
                ssisConn);
            startExecCmd.Parameters.Add("@execId", SqlDbType.BigInt).Value = executionId;
            startExecCmd.ExecuteNonQuery();

            // 检查执行状态
            var checkStatusCmd = new SqlCommand(
                "SELECT status FROM catalog.executions WHERE execution_id = @execId",
                ssisConn);
            checkStatusCmd.Parameters.Add("@execId", SqlDbType.BigInt).Value = executionId;
            int executionStatus = (int)checkStatusCmd.ExecuteScalar();

            // 状态码说明：7=成功，4=失败
            if (executionStatus == 7)
            {
                // 提交事务
                scope.Complete();
                Console.WriteLine("SSIS包执行成功，事务已提交");
            }
            else
            {
                throw new Exception($"SSIS包执行失败，状态码：{executionStatus}");
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"执行出错：{ex.Message}，所有操作将回滚");
        // TransactionScope会自动处理回滚，无需手动操作
    }
}

备注：内容来源于stack exchange，提问作者KA-Yasso