SSIS return total committed rows count in execute sql task

Question

I have an Executed SQL Task with SQL query to delete the data from table. I am using while loop to delete the data in batch and try/catch to handle the failure.

In Execute SQL Task I want to return the deleted record, so I added output parameters which is capturing the deleted record. But the problem I am having is, I am not able to capture/return the deleted record (which is already committed) in case of failure.

I am using below SQL logic

Declare @DeletedRows INT = 0
BEGIN TRY
    BEGIN TRANSACTION 
        While (@deletedrows < @rowstodelete) 
        BEGIN 
          Delete records where condition is match 
          SET @deletedrows = @deletedrows + @@rowcount  
        END
COMMIT TRANSACTION 
SET ? = @deletedrows   ---- returning deleted rows to output parameter of execute sql task
    END TRY
    BEGIN CATCH
      IF @@TRANCOUNT > 0
         ROLLBACK
    RAISERROR (CAPTURE ERRORS);
    END CATCH

Any idea how can I return already deleted or committed rows to output parameter of Execute SQL Task ?

Answer 1

First, you need to update the spellings of COMMIT in your SQL Script. And then you need to do 'SELECT ? = @DeletedRows' at the end of your SQL Script in Execute SQL Task and change the Parameter Name value to 0 in Parameter Mapping screen. This should give you correct result.