The Row Count Transformation

b
Fig 1: The Row Count Transformation

In this post I will be covering the Row Count Transformation. The sample package can be found here for 2005 and guidelines on use are here.

What does the Row Count Transformation do?

The Row Count Transformation counts the number of rows that have passed through a Data Flow and puts that count into a variable. Configuration is simple – all you need to do to is specify the variable name that will hold the row count on the first page of the editor (down at the bottom under “Custom Properties”). There’s a little gotcha here – whilst the tab for ‘Input Columns” is active, if you try to select any columns it will return an error and not allow you to continue.

It is worth noting that the variable is only updated once all rows of data have passed through the data flow – i’ve demonstrated this in the sample package by adding the variable to a column in a Derived Column – it returns zero all the way through, so you cannot use the Row Count as a row number generator.

Where would you use the Row Count Transformation?

The most obvious use is in logging processes – for example counting input rows versus outputs rows or counting failed rows. Anywhere you need to track the number of rows being passed through a given data flow.

MSDN Documentation for the Row Count Transformation can be found here for 2008 and here for 2005.

9 thoughts on “The Row Count Transformation

  1. Some of the transformatons you just gave only theoritical explanation ,i think it is better to explain all transformatons through an example with snap shots.I hope u will do this in soon.

  2. Hi,

    Through the above theory part , i can understand the usage of rowcount transformation in SSIS.
    But now i am facing a problem while using the above transformation.(i.e)after entering the variable name in the editor am navigating the input column tab ,here if i select any one columnname and click the OK button its showing the error…can u help me in this….

  3. Hi Mahesh – as I note in my post, just because you can select input columns, you shouldn’t as it will throw an error. You don’t need to pick any columns to get the count to work.

  4. hi,
    u said that the var will return zero when used with derived column, then how can we use the var value in case i want to store it in a flat file or some table. And why does it returns 0 every time??

  5. The variable returns 0 in the derived column because it has no value until the data flow is complete and every row has been counted.

    By scoping the variable at the package level, you can use it for logging in a subsequent task on the Control flow, e.g. an Execute SQL task or you can use it in an Event Handler.

  6. Thanks a lot :)
    Especially for the info: ‘whilst the tab for ‘Input Columns” is active, if you try to select any columns it will return an error and not allow you to continue’

    Earlier when I saw Row Count Transformation in the existing Packages, I was wondering if the column names are visible then why no column has been selected.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>