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 |