Welcome to the world (new face) of Knowledge Sharing Network.
To track the older blog posts refer to our historical knowledge sharing site  and you will find this as your destination for SQL Server knowledge pool.

Follow SQLMaster on Twitter SqlServer-QA.net - Knowledge Sharing Network (@sqlmaster)

How to get rowcount efficiently within a SSIS package? - SQL Server Knowledge Sharing Network (@sqlmaster)

How to get rowcount efficiently within a SSIS package?

I would like to call this as how efficiently you can obtain counting rows in SSIS package. Usual thought would come is @@rowcount or  COUNT_BIG functions, but this is not that efficient way to obtain the results.

SSIS provides the snippet within its components such as using Row Count component, for this you have to create a variable of integer typeat a scope where you can see it from your Data Flow task. Then add this RowCount component to the Data Flow task place where you want to get results. Not yet finished once it is specified in that task you have to edit the Row Count component and set its VariableName property to the name of the variable you created.

The downside of this method is this component is not updated quickly unless the complete DataFlow task is completed, may be we can use VBScript component to get upto date count values and again this is not possible always to embed within the package.

So going deep into the requirement such getting rowcount values on all sorts of level such as, how many rows were inserted to a destination? As usual you can achieve this by using RowCount component just before the destination component. A little glitch here is missing of failed rows during that insert operation, because it would not take into account rows which failed to be inserted. So there are two patterns you could use for OLEDB destinations:

  • Use an ExecuteSQL task to count rows before and after the Data Flow has executed, and compare;
  • Add a RowCount component before the destination, and one on the error output of the destination, and compare the values of the two variables after the Data Flow has completed.

 

Published Wednesday, March 26, 2008 7:51 AM by ssqa.net

Comments

No Comments