How to Log Number of Rows affected by SSIS Execute SQL Task

Question

When I execute a sql statement like "Select ...", I can only see "...100%" completed...

I want to log the number of rows affected.

How can we do that?

Answer 1

run your SELECT from within a stored procedure, where you can log the rowcount into a table, or do anything else to record it...

CREATE PROCEDURE SSIS_TaskA
AS

DECLARE @Rows  int

SELECT ... --your select goes here


SELECT @Rows=@@ROWCOUNT

INSERT INTO YourLogTable
        (RunDate,Message)
    VALUES
        (GETDATE(),'Selected '+CONVERT(varchar(10),ISNULL(@Rows,0))+' rows in SSIS_TaskA')

GO

Answer 2

When you use a SQL Task for a select most of the time you give as destination a DataSet Object, you can count the number of ligne from the DataSet

Answer 3

what if I'm doing an update or an insert and not allowed to use stored procs or functions?

Answer 4

I believe you could leverage a t-sql output clause on your update or insert statement and capture that as an ssis variable....or just drop it into a sql table.

here is an example...its crappy, but it is an example

UPDATE TOP (10) HumanResources.Employee SET VacationHours = VacationHours * 1.25 OUTPUT INSERTED.EmployeeID, DELETED.VacationHours, INSERTED.VacationHours, INSERTED.ModifiedDate INTO @MyTableVar;

You could output @@ROWCOUNT anyplace you need it to be.

Here is output syntax

http://technet.microsoft.com/en-us/library/ms177564.aspx