SSIS, need to get number of rows deleted from SQL Task

Hi guys

I have an Execute SQL Task which is a simple DELETE query..   DELETE FROM SomeTable

What is the easiest way to extract the number of rows deleted into an SSIS variable?
We have a custom error handling / dynamic connections / logging tool for SSIS, and it uses variables to store records inserted/written for specific containers/tasks, but I haven't figured out delete yet.

Thanks in advance.
Jim
LVL 66
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAsked:
Who is Participating?
 
chapmandewCommented:
declare an ado.net connection, and a variable named @rows.  at the end of your delete statement, assign the @@ROWCOUNT variable to your @rows SSIS variable....thats all there is to it.
0
 
daveamourCommented:
Have your stored procedure output a value either by an output parameter or a scalar value.
I think there is a server variable for this - might be something like @@RowCount
0
 
PedroCGDCommented:
If you use SQL Task of SSIS, I suggest you to use an output parameter inside Stored Procedure ti send to the SSIS Package the number of rows deleted.
Helped? Regards,
Pedro
www.pedrocgd.blogspot.com
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
Used this, and replaced the SCOPE_IDENTITY of the insert with @@ROWCOUNT
http://blogs.techrepublic.com.com/datacenter/?p=237

SELECT CAST(@@ROWCOUNT as INT) as foo
Then set the 1st result name to my variable.

Thanks.
Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.