SSIS - RowNumber() Partioned By

Today I saw an interesting question in EE about how to create a RowNumber in the data flow task but partitioned by an ID column.

Row Number is easy and could be done using a script task in the control flow or inside a script component in the data flow task, there is also a Row Number transformation, a free community extension.


But the partition by is the problem here, which just need a custom script to do it, and the answer to that is by using a synchronous script component as follows:


Our Input:

Add your flat file source, I'm not going to go into details about the flat file source, you could use any source and configure it the way you want, the point here is our input is 2 columns and we need to create the row number column partitioned by the ID column.

I'm assuming that the input is sorted on the ID< if not, so you need to add a sort transformation to the flow before the script component. and thanks to Valentino ( B | T ) for reminding me!

 Then add a script component and configure it as follows...First let's grab out input columns.

second let us add our Output row, I called it here PORT

Now let's configure out script using the VB.net language

Of course you could get fancy, add a try and catch, give meaningful names to your variables and such...

Our output:

Hope that helps someone....

 http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27682431.html?cid=1752