SQL Blog - Pieter van Maasdam, Macaw

SSIS, SSAS, SSRS & other SQL-things I come across...
SSIS 2008 - Rowcounts using the script component

I have been using the Rowcount Component for some time now. The thing I didn't like about it, is that I had to create an SSIS variable for every flow in the Data Flow Task. For example: when extracting data we sometimes have more than 30 tables in a data flow. So, I have been trying to find a more flexible way to add rowcounts to my packages, without having to create a lot of new variables.

Here's an example of what I came up with (It's not ready to be used in production environments by the way):

Rowcount controlflow 

I created an SSIS package variable of datatype Object named RowcountList. In the first Script Task, i initialize it by assigning an ArrayList to it. Next step is in the data flow task:

Rowcount dataflow

In the Script component I use an integer to count the rows. Then, in the PostExecute method I create an ArrayList based on the RowcountList variable. Then, I add the name of the script component combined with the rowcount to the ArrayList and store that in the SSIS variable RowcountList.

Finally, in the last Script Task I iterate through the ArrayList and store the rowcounts in a custom logging table. Now, it seems to me this is a good way to do the rowcounts, but I'm very curious if other people have tried to do this and maybe have found a better way.

Published Monday, January 25, 2010 11:18 AM by Repieter

Comments

No Comments