Search results matching tags 'SQL Server Integration Services', 'repost', 'ssis', and 'OnPipelineRowsSent'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+Integration+Services,repost,ssis,OnPipelineRowsSent&orTags=0Search results matching tags 'SQL Server Integration Services', 'repost', 'ssis', and 'OnPipelineRowsSent'en-USCommunityServer 2.1 SP2 (Build: 61129.1)[SSIS] OnPipelineRowsSent eventhttp://sqlblog.com/blogs/jamie_thomson/archive/2011/08/19/ssis-onpipelinerowssent-event.aspxFri, 19 Aug 2011 11:48:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:37927jamiet<blockquote>
<p><i>Once upon a time I blogged at <a href="http://consultingblogs.emc.com/jamiethomson" target="_blank">http://consultingblogs.emc.com/jamiethomson</a> but that ended in August 2009 when I left EMC. There is a lot of valuable content over there however certain events in the past leave me concerned that that content is not well cared for and I don't have any confidence that it will still exist in the long term. Hence, I have taken the decision to re-publish some of that content here at SQLBlog so over the coming weeks and months you may find re-published content popping up here from time-to-time.</i></p><p><i>This is the first such blog post in <a href="http://sqlblog.com/blogs/jamie_thomson/archive/tags/repost/default.aspx" target="_blank">this series</a> in which I talk about the little-known OnPipelineRowsSent event.</i></p>
<hr>
</blockquote>
<p>An <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1317964&amp;SiteID=1">interesting discussion</a> today on the <a href="http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&amp;SiteID=1">SSIS Forum</a>
made me realise that perhaps not&nbsp;all SSIS developers&nbsp;are aware of the
OnPipelineRowsSent event in SSIS and how valuable it can be, certainly
when debugging data-flows. This isn't all that surprising because <a href="http://msdn2.microsoft.com/en-us/library/ms141026.aspx">Books Online</a>&nbsp;is very sparse in this area. Hence I thought it would make a useful blog topic.</p>
<p>OnPipelineRowsSent
is a type of&nbsp;event in SSIS which means the&nbsp;information that it provides
can be logged&nbsp;by whatever log provider you happen to be using.&nbsp;In
order&nbsp;to understand the information that OnPipelineRowsSent provides it
is critical to understand the buffer architecture of the <a href="http://msdn2.microsoft.com/en-us/library/ms141122.aspx">SSIS data-flow</a>.</p>
<p>To
explain&nbsp;that&nbsp;VERY simply, a buffer is an area of memory that contains
rows of data as they pass through the pipeline. Each data-path in the
data-flow&nbsp;will contain one of more buffers and each of those buffers
(except for the last one) will contain the same number of rows. If you
want to understand more about the buffer architecture of the data-flow
then <a href="http://www.amazon.com/Microsoft-Server-2005-Integration-Services/dp/0672327813">Kirk Haselden's book</a> has a chapter devoted wholly to it.</p>
<p>Here is what Books Online <a href="http://msdn2.microsoft.com/en-us/library/ms141122.aspx">DOES say</a> about OnPipelineRowsSent:</p>
<table>
<tr>
<td>
<p><b>OnPipelineRowsSent</b> </p>
</td>
<td>
<p>Reports the number of rows provided to a component input by a call to the <b>ProcessInput</b> method. The log entry includes the component name. </p>
</td>
</tr>
</table>
<p>In
slightly plainer english that means that for every buffer of data that
is consumed by a component, you get a log entry. So, if you have&nbsp;(e.g.) a
<a href="http://msdn2.microsoft.com/en-us/library/ms141069.aspx">Derived Column Component</a>&nbsp;that
receives 105000&nbsp;rows and the size of the buffer is 10000 rows, you'll
get 11 OnPipelineRowsSent log entries for that&nbsp;component&nbsp;(there will
only be 5000 rows in the last buffer).&nbsp;Basically it enables you to know
how many rows are passing through each data-path in the data-flow.</p>
<p>The available information is the same as you get for any eventhandler. &nbsp;i.e. </p>
<ul>
<li>event</li>
<li>computer</li>
<li>operator</li>
<li>source</li>
<li>sourceid</li>
<li>executionid</li>
<li>starttime</li>
<li>endtime</li>
<li>datacode</li>
<li>databytes</li>
<li>message</li>
</ul>
<p>and here is a portion of a logfile containing those records:</p>
<p><font face="courier new"><font color="#ff0000">OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data
Flow
Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007
20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow
component as input. :&nbsp; : 1228 : Merge Join Output : 1225 : Union All 1 :
1226 : Union All Input 1 : 9936<br></font><font color="#0000ff">OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data
Flow
Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007
20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow
component as input. :&nbsp; : 1475 : Union All Output 1 : 1470 : Sort 3 :
1471 : Sort Input : 9936<br></font><font color="#ff0000">OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data
Flow
Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007
20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow
component as input. :&nbsp; : 1228 : Merge Join Output : 1225 : Union All 1 :
1226 : Union All Input 1 : 2085<br></font><font color="#0000ff">OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data
Flow
Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007
20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow
component as input. :&nbsp; : 1475 : Union All Output 1 : 1470 : Sort 3 :
1471 : Sort Input : 9936<br></font><font color="#ff0000">OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data
Flow
Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007
20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow
component as input. :&nbsp; : 1475 : Union All Output 1 : 1470 : Sort 3 :
1471 : Sort Input : 2085<br></font><font color="#0000ff">OnPipelineRowsSent,CGOJATHOMSON,INT\Jamie.Thomson,Data
Flow
Task,{EB25C41A-EFE2-4902-9610-99C0309A3B59},{33B4926F-54A9-4E00-9B74-92720A2786E0},08/03/2007
20:49:56,08/03/2007 20:49:56,0,0x,Rows were provided to a data flow
component as input. :&nbsp; : 2121 : Sort Output : 2113 : Merge Join 2 : 2114
: Merge Join Left Input : 9936 </font></font></p>
<p>The important stuff is what comes in the last field, the message so&nbsp;let's break that down. Here is an example message:</p>
<p><font color="#0000ff" face="Courier New">Rows
were provided to a data flow component as input. :&nbsp; : 1030 : OLE DB
Source Output : 1025 : Sort 2 : 1026 : Sort Input : 9972</font></p>
<p>And here's what it is constituted of:&nbsp;</p>
<ul>
<li>Rows
were provided to a data flow component as input. - That's consistent in
each message, so to be honest they could have left it out</li>
<li><div>1030&nbsp;- ID of the data-path providing the buffer</div></li>
<li><div>OLE DB Source Output - Name of the&nbsp;data-path providing the buffer</div></li>
<li><div>1025 - ID of the component receiving the buffer</div></li>
<li><div>Sort 2 - Name of the component receiving the buffer</div></li>
<li><div>1026 - ID of the input receiving the buffer</div></li>
<li><div>Sort Input - Name of the input receiving the buffer</div></li>
<li><div>9972&nbsp;- Number of rows in the buffer</div></li>
</ul>
<p>As
you can see, this information will enable you to determine exactly how
many rows are output from each component. Thus, if you are not receiving
the expected number of rows at a destination this event will help you
to deduce where you are losing them from.</p>
<p>You may not know this
but&nbsp;you actually encounter OnPipelineRowsSent every time you run a
data-flow within the SSIS Designer in&nbsp;BIDS. Notice how the designer
shows the number of rows passing through the pipeline and through each
component:</p>
<img src="http://i1177.photobucket.com/albums/x349/jamiekth/df.jpg" alt="Photobucket" border="0">
<p>&nbsp;</p>
<p>How&nbsp;does the designer know how many rows are passing through the
data-flow? Simple! Its because the SSIS Designer consumes the
OnPipelineRowsSent event that the executing package "throws" up and
then&nbsp;presents that information visually&nbsp;as the numbers that you see
flashing in front of your eyes as the data-flow executes.</p>
<p>I think
that just about covers the simple stuff around OnPipelineRowsSent. If
you have any questions then post them here as a comment.</p>
<p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a> <br></p>
<p>&nbsp;</p>
<p>&nbsp;</p>