Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

Once upon a time this blog was a hive of activity. Now however its pretty lifeless as you can probably tell so if are pining for more of the same you can find me over at http://sqlblog.com/blogs/jamie_thomson. I look forward to seeing you there!

SSIS: Using the Rowcount component

A question was posted on a beta newsgroup recently asking for a demo of how to use the SSIS Rowcount component in a data flow. So, here's an example.

The only requirement is that you have a variable in which to store the rowcount. To run the package simply edit the "AdventureWorks" connection manager to point at your AdventureWorks database installation.

Note that the Rowcount component is a synchronous component and this coupled with the fact that it doesn't actually do anything particularly resource intensive means the performance degredation of your package should be negligible.

Very easy to do and very useful. I personally am using the Rowcount component to capture the number of inserts, updates and deletes done by each of my data-flows and then using the OnPostExecute event handler to put this information into a custom logging table. If you want more info on this, let me know.

As an aside, it is useful to use the Rowcount component as a destination during debugging when you don't actually want to place the rows that are flowing through your data-flow to a physical data sink.

-Jamie

 

Published 29 May 2005 17:11 by jamie.thomson

Comments

 

Don W. said:

I have a question regarding row count. Apparently, the variable value does not change until the Data Flow has completed. Does this include abnormal terminations?

Thank You,

Don W.

February 21, 2007 20:33
 

jamie.thomson said:

Don,

I don't know to be honest. perhaps you can tell me?

-Jamie

February 21, 2007 20:42
 

Don W. said:

Jamie,

 It looks like the answer is "no". I set up a simple data flow to read a DB2 table in, and write a DB2 table out. It was set to abend after 34 rows. I had my row count variable mapped to an output field. I put a data grid just before my output, and the variable remained at zero for all 34 rows.

February 21, 2007 21:37
 

jamie.thomson said:

Don,

If you are using the Rowcount component to populate a variable then you cannot reference that variable in the same data-flow and get expected results. You cannot reference that variable until after the data-flow is complete, regardless of whether it succeeds or not.

-Jamie

February 21, 2007 21:56
 

Don W. said:

Thanks Jamie.

I'm fairly new to SSIS. In my workflow, I'm populating several DB2 tables. I'm looking for a way to easily restart if it abnormally abends. I thought if I needed to restart, I could just bypass the rows already processed.

February 22, 2007 14:40
 

Antonio G. said:

Hi Jamie,

I would like to save the rowcount result in a file, how can I do it?

For rowcount result I mean the number of rows passed to the Row Count component.

The operation is very simple but using SSIS seems too complex.

I think that I have to put the result in a variable  (and it is ok). Then for saving the variable in a file I have to write a VB .NET script....

Thanks in advance

Antonio

February 27, 2007 13:35
 

jamie.thomson said:

Antonio,

Any reason you can't use the Flat File Destination adapter?

-Jamie

February 27, 2007 14:45
 

Antonio said:

thanks for your quick answer!

If I use the Flat File Destination adapter I write the rows and not their number.

Is possible to write a variable or the Row Count value in a flat file directly?

thanks

Antonio

February 27, 2007 14:55
 

jamie.thomson said:

Antonio,

Yes, but you'll need to do it in another data-flow!

-Jamie

February 28, 2007 05:10
 

SSIS Junkie said:

I haven't published a nugget for a while but today a guy called Antonio was asking me how one might

February 28, 2007 06:44
 

jamie.thomson said:

Antonio,

I've written up a demo of this. You can get it from here: http://blogs.conchango.com/jamiethomson/archive/2007/02/28/SSIS-Nugget_3A00_-Output-the-rowcount.aspx

-Jamie

February 28, 2007 06:46
 

Ryan Kenyon said:

You don't need another data flow -- you can use the Aggregate data flow transformation (specify count as the aggregation) then write that to your flat file dest and map all your columns.

May 23, 2007 19:57
 

Balachandar Ganesan said:

Very easy to do and very useful. I personally am using the Rowcount component to capture the number of inserts, updates and deletes done by each of my data-flows and then using the OnPostExecute event handler to put this information into a custom logging table. If you want more info on this, let me know.

Can you please post a sample mapping with the above mentioned features. I am badly looking forward to one such sample.

Thanks and Regards

Balachandar Ganesan.

August 6, 2007 14:45
 

ns said:

I have a unsual situation , i need to do rowcount and put the number of row count number in text file , i have two output file one basically converts the tab delimited to comma delimited and the other text file is suppose to have row count numbers

April 21, 2008 17:28
 

Jehuty said:

Hi I have a problem I can't catch the variable who count the number of row.. when I launch the package the varaible still set at 0... what can I do?

April 28, 2008 10:37
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems