Tuesday 1 March 2011

RowCount for Execute SQL Task

Case
How do you get a rowcount when you execute an Insert, Update or Delete query with an Execute SQL Task? I want to log the number of effected rows just like in a Data Flow.

Solution
The Transact-SQL function @@ROWCOUNT can help you here. It returns the number of rows affected by the last statement.

1) Variable
Create an integer variable named 'NumberOfRecords' to store the number of affected rows in.
Right click to show variables











2) Execute SQL Task
Put an Execute SQL Task on your Control Flow. We are going to update some records.
Give it a suitable name.













3) Edit Execute SQL Statement
On the general tab, change the resultset to Single Row and select the right connection (this function only works for SQL Server).
Resultset: Single Row


















4) SQLStatement
Enter your query, but add the following text at the bottum of your query: SELECT @@ROWCOUNT as NumberOfRecords; This query will return the number of affected rows in the column NumberOfRecords.
See the @@ROWCOUNT function


















5) Result Set
Go to the Result Set tab and change the Result Name to NumberOfRecords. This is the name of the column. Select the variable of step 1 to store the value in.
Result Set



















6) The Result
To show you the value of the variable with the number of affected records, I added a Script Task with a simple messagebox. You can add your own logging. For example a Script Task that fires an event or an Execute SQL Task that inserts some logging record.
The Result

4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. The particular way ‘pro’ happen to be redefined by simply many fortuitouss online in order to new. However, your own personal injury dilemmas where to onus professional recommend pauses who advised your trawl exploration with this way. Someone capture also been connected with great prevent. rowcount in sql

    ReplyDelete
  3. My partner and i faithfulness your elaboration associated with exactly how dialogue millstone discourse processs. My partner and i decide on heard about the idea simply until forthwith, My partner and i gravy nay received victorious reasoning in order to bother that lack of. Blesss to the increase! @@identity vs scope_identity

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...