Derived Column that is Count of each value

Brickwall
Brickwall used Ask the Experts™
on
I have a Data Source that i am working with in SSIS.  The data source has an id Column that contains duplicates,  but the way the data is it's actually not a duplicate value.  I know confusing right.

So the data looks like this:
ID         |       Data
20197  |....... more data
20197  |........more data
20197  |........more data
20198  |........more data
20198  |........more data
20199  |........more data

The way the Data is I am needing to  to add a count and grouping. to each values.

The end Result should look like
ID      |Port|    Data
20197  |1|....... more data
20197  |2|........more data
20197  |3|........more data
20198  |1|........more data
20198  |2|........more data
20199  |1|........more data

The data source is CSV so I am needing to do All of this in SSIS some how
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
BI Consultant
Most Valuable Expert 2011
Commented:
It will require some work such as sorting the data, adding a script component to generate the counter and all, but it is possible.

Have a look at the following article by Jason Strate: http://www.jasonstrate.com/2011/01/31-days-of-ssis-generating-row-numbers-2331/

Especially the RowNumberByEmail is similar to your situation.
AnujSQL Server DBA
Top Expert 2011

Commented:
Did you tried derived column tool in SSIS?
Jason YousefSr. BI  Developer

Commented:
Here's also a VB.net similar approach...

@Valentino, did you mean JASON YOUSEF :P

http://asqlb.blogspot.com/2012/04/ssis-rownumber-partioned-by.html
ValentinoVBI Consultant
Most Valuable Expert 2011

Commented:
@huslayer: dude, you've got too much spare time! ;)

BTW: you're assuming that the input file contains the data sorted on ID. If that's not the case then an additional Sort transform would be needed.
Jason YousefSr. BI  Developer

Commented:
Opps I forgot to mention that, thanks for you reminding me !!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial