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.
The Transact-SQL function @@ROWCOUNT can help you here. It returns the number of rows affected by the last statement.
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|
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.
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.