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 ?

You need to do a 'SELECT @DeletedRows' at the end of your SQL Script in Execute SQL Task — Faisal Mehboob, Nov 18 '21 at 22:09
@FaisalMehboob You have the correct solution here, you should add it as an answer — billinkc, Nov 19 '21 at 01:05

Faisal Mehboob · Answer 1 · 2021-11-23T00:08:52.070

0

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.

edited Nov 23 '21 at 00:08

answered Nov 19 '21 at 15:09

Faisal Mehboob

609
7
17

Do you mean at the end of try block ? or entire script after try/catch block ? I want to get the records in output parameter not in result sets. – Sql Programmer Nov 19 '21 at 20:17
You need to enter it at the end of your entire try/catch block and add an output variable in Parameter Mapping window. – Faisal Mehboob Nov 19 '21 at 22:48
Would be mind giving a sample of it ? I tried the same but still it's not working. – Sql Programmer Nov 20 '21 at 17:57
I have updated the answer. Hope that would fix your issue. – Faisal Mehboob Nov 23 '21 at 00:09
Thank you for the response. I am getting the deleted row count in output parameter but not in case of failure. In case of failure the value is always 0. – Sql Programmer Nov 24 '21 at 20:22

SSIS return total committed rows count in execute sql task

1 Answers1