?
Solved

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

Posted on 2009-02-16
5
Medium Priority
?
1,845 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:Jim Horn
5 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 23649910
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
 
LVL 19

Expert Comment

by:daveamour
ID: 23649913
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 2000 total points
ID: 23649916
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 23649953
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
 
LVL 66

Author Comment

by:Jim Horn
ID: 23650231
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question