Brian Knight, SQL Server MVP

Parsing the OnPipelineRowsSent

The OnPipelineRowsSent event functions in the data flow to log how many rows go between step to step in the pipeline (or from input to output). Once you have a data flow task, you can right-click in the design pane and select Logging. Configure the logging provider and now notice in detail you have OnPipelineRowsSent. This will now create a record for each transform (source or destination) that the data goes through to show how many rows were sent through the pipeline. It writes this though in a single message column pipe delimitted. So, you'll need to write some sort of view or routine to parse out the critical data you need. Here's the query that's from one of the SSIS report packs to parsse this into a readable format that I use:

SELECT     source, sourceid, executionid, ssrs.ParsePipeline(message, 1) AS PathID, ssrs.ParsePipeline(message, 2) AS PathIDName,
                      ssrs.ParsePipeline(message, 3) AS ComponentID, ssrs.ParsePipeline(message, 4) AS ComponentIDName, ssrs.ParsePipeline(message, 5) AS InputID,
                      ssrs.ParsePipeline(message, 6) AS InputIDName, CONVERT(int, ssrs.ParsePipeline(message, 7)) AS RowsSent, starttime
FROM         dbo.sysdtslog90
WHERE     (event LIKE 'onpipelinerowssent%')

Here's the function needed to do the parsing:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------
--! CREATE FUNCTION (for OnPipeLineRowsSent report)
-- function to parse OnpipelineRowsSent log entry from SSIS sysdtslog90 table
-- Function parses one log entry at a time so a view is also needed (below)
-- to call\use the function for each log entry.
----------------------------------------------------------------------------

CREATE function [ssrs].[ParsePipeline] (@message varchar(8000), @which int)

returns varchar(200)

as begin

--@which defines which value is desired

-- 1= PathID
-- 2= declare PathIDName
-- 3= declare ComponentID
-- 4= declare ComponentIDName
-- 5= declare InputID
-- 6= declare InputIDName
-- 7= declare rowssent
declare @sourcemessage varchar(600)
declare @where as integer
declare @mycounter integer
If @which < 1 or @which > 7 return null
set @mycounter=0

--catch older versions of the messages that lacked the extra parameters

if patindex('%: :%', @message) = 0 return null

--chop the initial wordy stuff out
set @sourcemessage = right(@message, len(@message) - patindex('%: :%', @message) - 3)

--loop through occurances of : until we get to the desired one
set @where = 99
while @where <> 0 begin
set @mycounter = @mycounter+1
set @where = patindex('%:%',@sourcemessage)
If @mycounter = 7 return @sourcemessage
if @mycounter = @which return(left(@sourcemessage, @where - 1))
set @sourcemessage = right(@sourcemessage, (len(@sourcemessage) - @where))

end --while

--should not execute this but a return is required as the last statement
return @sourcemessage
end --function

-- Brian Knight