SSIS中Execute SQL Task如何返回已提交的删除记录行数

你当前的问题根源在于将所有删除操作放在单个全局事务中，一旦报错触发回滚，所有已执行的删除操作都会被撤销，同时输出参数的赋值语句放在COMMIT之后，异常场景下根本不会执行到该行，因此无法拿到返回值。可根据你的业务需求选择以下两种方案：

方案1：批次独立提交（批量删除推荐方案） #

适合允许分批提交、不需要全量删除成功才生效的场景，同时可避免长事务锁表、日志暴涨的问题。修改后的SQL逻辑如下：

DECLARE @DeletedRows INT = 0, @BatchSize INT = 1000 -- 可自行调整批次大小
BEGIN TRY
    WHILE (@DeletedRows < @rowstodelete) 
    BEGIN 
        -- 每个删除批次单独开启事务
        BEGIN TRANSACTION
            DELETE TOP (@BatchSize) 你的表名 WHERE 匹配条件;
            SET @DeletedRows = @DeletedRows + @@ROWCOUNT;
        COMMIT TRANSACTION
    END
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    -- 异常场景也给输出参数赋值，返回已经成功提交的行数
    SET ? = @DeletedRows;
    -- 抛出错误让SSIS感知任务失败
    THROW; -- SQL Server 2012及以上版本推荐使用，低版本可替换为你原有的RAISERROR逻辑
END CATCH
-- 正常执行完成也赋值输出参数
SET ? = @DeletedRows;

该方案特点：

• 每批删除后立即提交，不会因为后续批次失败回滚已经完成的删除
• 无论执行成功还是失败，都会给输出参数赋值，SSIS端可正常拿到已提交的删除行数
• 无长事务，对业务运行影响更小

方案2：保留全局事务的计数方案 #

如果你要求所有删除必须全部成功才提交、失败就全量回滚，可使用该方案。注意此场景下实际已提交的行数为0，你可以拿到的是执行失败前已删除的未提交行数：

DECLARE @DeletedRows INT = 0
BEGIN TRY
    BEGIN TRANSACTION 
        WHILE (@DeletedRows < @rowstodelete) 
        BEGIN 
            DELETE 你的表名 WHERE 匹配条件;
            SET @DeletedRows = @DeletedRows + @@ROWCOUNT;
        END
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    -- 事务回滚不会撤销局部变量的赋值，依然保留失败前的删除计数
    SET ? = @DeletedRows;
    THROW;
END CATCH
SET ? = @DeletedRows;

SSIS端配套配置 #

• 输出参数方向需设置为Output，数据类型对应INT，按SQL中?的出现顺序匹配参数即可
• 若需要任务失败时依然能读取到输出参数值，需要将Execute SQL Task的FailTaskOnFailure属性设置为False，或者在上层容器添加失败事件处理程序，在事件中读取参数值，否则SSIS任务失败后默认会丢弃参数输出

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