CODEWITHSUNDEEP
ssis
user2815533
user2815533

Reputation: 11

Capturing Impacted Row Counts after OLE DB Command (SSIS)

I have an OLE DB Command in a Data Flow that deletes records based on results from a Lookup Task. I want to get a count of the number of records deleted. I set up a RowCount task after the OLE DB Command but the result I receive is the number of records that match the Lookup, not the number of records that match the criteria in the delete statement(OLE DB Command).

Any assistance would be appreciated.

-Craig

Upvotes: 1

Views: 1727

Answers (2)

Kyle Hale
Kyle Hale

Reputation: 8120

  1. Create a null placeholder column for your row count in your data flow. You can add it as a derived column or in your source query. Let's call it rowsDeleted.

  2. Create a stored procedure to process your deletion. I've created a simplified version for a made-up table:

    create procedure deleteTT @id int, @rowcount int OUTPUT as begin delete from dbo.tt where a = @id; SELECT @rowcount = @@ROWCOUNT; end

  3. Set the SqlCommand of your OLE DB Command to the following: exec deleteTT ?, ? output;

  4. In the Column Mappings tab, map your id column from your lookup to the @id parameter, and map rowsDeleted to the @rowcount OUTPUT parameter.

  5. Now rowsDeleted is part of your data flow for that row and you can do with it as you please.

Upvotes: 1

billinkc
billinkc

Reputation: 61269

You'd probably need to try to get the Command object to emit the count of what is deleted and then wire up an aggregate to it to sum all those values.

I've never had much success getting output out of the Command component so I'd cheat and either compute the aggregate in the preceding lookup and add that value into the stream or just use a Script Component to fire the delete statement and capture the @@rowcount value and push it into the Data flow.

Upvotes: 0

Related Questions