Sequential Row Number.

Question

Sequential Row Number.

VEER-430410

SSC Enthusiast

Points: 110
More actions
June 19, 2007 at 12:59 pm

#177071

Hi
I am trying to transfer my Informatica/Oracle skills to the SSIS world for a quick effort. I can accomplish this in Informtica by altering my source Sql and adding Oracle ROWNUM to the selection list.
I need to populate a column in the table with the value of the sequential row number. This column does not exist in the Source table but a column exists in the target table. How do I generate this column with values 1,2,3...n for each of the row being inserted into the target table using SSIS transformations.
Thanks
Krishna

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Crispin Proctor SSCoach Points: 18161 More actions · Answer 1

You could either use an Identity column in the SQL table, or use the Row Number component in SSIS. Both will give you a sequential number which you can seed.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Tim Goldstein-441496 SSC Journeyman Points: 81 More actions · Answer 2

What Row Number component? All I am aware of is the Row Count task and it only give the total row count after the task completes AFAIK. The only way I have been able to add a row count in a data flow is to use a script task and increment a script variable and assign that value to an output.

Tim

Denver, CO

Crispin Proctor SSCoach Points: 18161 More actions · Answer 3

Sorry.

Go to http://www.SQLIS.com and download it free.

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

VEER-430410 SSC Enthusiast Points: 110 More actions · Answer 4

Thanks for the replies.

Since I cannot alter either the source table or the target table to add/change column/properties, I ended up creating a temporary table with Identity column and used that generated value to populate the target table. Unnecessarily complex but works.

Crispin Proctor SSCoach Points: 18161 More actions · Answer 5

I would not use that. Creating a temp table for the sake of a a SK it serious overkill and will kill performance.

Simply install the row number component and use that. So much easier and quicker

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

VEER-430410 SSC Enthusiast Points: 110 More actions · Answer 6

Thanks again for the response.

But would like to stick with Identity column on the temp table for now. Downloading and installing Misc components would create more need for migration requests/justifications/approvals for Production and would like to stay away from all that for this quick effort.

Crispin Proctor SSCoach Points: 18161 More actions · Answer 7

There are many ways to do something, and then the right way.

Your way works but will not scale very well. The overhead it causes is unnessasary. To be frank, it's not an elgent solution

That's my ten cents worth

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!