SSIS - Custom Row count logging

UPDATE(3/15/2012): Last week I came to know that the approach i have put forth below is not reliable and not recommended to be used in Production environment. Microsoft confirmed a bug with the OnPipelineRowsSent event, so please use this approach only for exploring SSIS or debugging or dev purposes. Also, SQL Server 2012 has out of the box features which logs row counts and other audit information automatically if turned on.


I have been hoping that I would find online or elsewhere, an easy automated solution to log all the important row counts within a SSIS dataflow task and finally I think I cracked it. I would like to share it with you all and see for your suggestions to improvise on this.

Past several years whenever I needed to log row counts in a data flow task, I used Row Count Transformation to record the actual count of rows into a variable and then insert the value into a SQL table or any logging destination connection for that matter. I hope the approach I am proposing in this blog saves some development time for you and your team. Now let me get to the business…

I am going to put the solution in easy three steps,

Step 1) Logging option needs to be turned on for below event,
            a.  OnPipelineRowsSent
           (Note: You will see this option in details tab within logging dialog box only when you have at least one Dataflow task in your control flow)

Step 2) Data Flow Path Name: Change the desired data flow path name within the Data flow task to a standard format so that the row count corresponding to that path can be extracted from the SSIS logs table (discussed in the next step). The format I used is “RowsReadCnt_SchemaName_TableName”. 

     Step 3)      Query to extract the row counts from SSIS log table. Row counts that are audited by below query are RowsReadCnt, RowsIgnoredCnt, RowsInsertedCnt, RowsUpdatedCnt and RowsErroredCnt. Now you could use below query as a Stored Proc at the end of your package execution to insert into the Row Counts log table.

SELECT @PackageLogId AS PackageLogId
,TableName
,ISNULL([RowsReadCnt],0)RowsReadCnt
,ISNULL([RowsIgnoredCnt],0)RowsIgnoredCnt
,ISNULL([RowsInsertedCnt],0)RowsInsertedCnt
,ISNULL([RowsUpdatedCnt],0)RowsUpdatedCnt
,ISNULL([RowsErroredCnt],0)RowsErroredCnt
,GETDATE() LogDate
FROM (
SELECT LTRIM(RTRIM(SUBSTRING(A.RowCountString,CHARINDEX('_',A.RowCountString)+1,LEN(RowCountString)))) TableName
,LTRIM(RTRIM(SUBSTRING(A.RowCountString,1,CHARINDEX('_',A.RowCountString)-1))) CountDescription
,A.RowCounts
FROM (
SELECT
CASE WHEN message like '%RowsReadCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsReadCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsReadCnt',message),LEN(message)))-1) ))
WHEN message like '%RowsInsertedCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsInsertedCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsInsertedCnt',message),LEN(message)))-1) ))
WHEN message like '%RowsIgnoredCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsIgnoredCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsIgnoredCnt',message),LEN(message)))-1) ))
WHEN message like '%RowsUpdatedCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsUpdatedCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsUpdatedCnt',message),LEN(message)))-1) ))
WHEN message like '%RowsErroredCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsErroredCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsErroredCnt',message),LEN(message)))-1) ))
END AS RowCountString
,SUM(CONVERT(int,REVERSE(SUBSTRING(REVERSE(message),1,CHARINDEX(':',REVERSE(message))-2)))) AS RowCounts
--,message
FROM sysssislog
WHERE executionid = @Executionid
AND event = 'OnPipelineRowsSent'
--AND CONVERT(int,REVERSE(SUBSTRING(REVERSE(message),1,CHARINDEX(':',REVERSE(message))-2))) <> 0
AND ((message like '%RowsReadCnt%') OR (message like '%RowsInsertedCnt%')
OR (message like '%RowsIgnoredCnt%') OR (message like '%RowsUpdatedCnt%')
OR (message like '%RowsErroredCnt%'))
GROUP BY
CASE WHEN message like '%RowsReadCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsReadCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsReadCnt',message),LEN(message)))-1) ))
WHEN message like '%RowsInsertedCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsInsertedCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsInsertedCnt',message),LEN(message)))-1) ))
WHEN message like '%RowsIgnoredCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsIgnoredCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsIgnoredCnt',message),LEN(message)))-1) ))
WHEN message like '%RowsUpdatedCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsUpdatedCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsUpdatedCnt',message),LEN(message)))-1) ))
WHEN message like '%RowsErroredCnt%' THEN RTRIM(LTRIM(SUBSTRING(SUBSTRING(message,CHARINDEX('RowsErroredCnt',message),LEN(message)),1,CHARINDEX(':',SUBSTRING(message,CHARINDEX('RowsErroredCnt',message),LEN(message)))-1) ))
END
) A
) AS B
PIVOT
( MAX(B.RowCounts) FOR CountDescription IN ([RowsReadCnt],[RowsInsertedCnt],[RowsIgnoredCnt],[RowsUpdatedCnt],[RowsErroredCnt])) AS C

Please post your comments, suggestion or a different approach to achieve this... Thanks for looking at this.