need sequence number

Question

Post reply

need sequence number

Charmer

SSChampion

Points: 14903
More actions
October 4, 2011 at 9:19 am

#258482

Hi,
my process is OLEDB to OLEDB....
i have an additional column in the destination when compared to the source....i need that column must be the sequence of number like 1,2,3,4,5,6............etc..and so on....based on total count of the source data...
how can i do that?
Thanks,
Charmer

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

ROW_NUMBER()

http://msdn.microsoft.com/en-us/library/ms186734%28v=SQL.90%29.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Charmer SSChampion Points: 14903 More actions · Answer 2

its solved...

i used row_number() in query...

but how can we do this through SSIS

Thanks,
Charmer

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

You can use a script component as a transformation that will increment a counter for every row and puts the result in the output buffer.

You can find an example here:

http://weblogs.sqlteam.com/jamesn/archive/2008/02/13/60509.aspx

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Charmer SSChampion Points: 14903 More actions · Answer 4

Charmer

SSChampion

Points: 14903

October 5, 2011 at 1:04 pm

#1390894

thanks a lot....its very helpful...

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 5

in the same way, how to increment the datetime value using Script component?

Thanks,
Charmer

steveb. SSC-Forever Points: 46733 More actions · Answer 6

Follow the example in the link but for dates use the AddDays method of the DateTime classs.

Charmer SSChampion Points: 14903 More actions · Answer 7

i tried, but im bad at coding part....could you help me by showing some sample codings

Thanks,
Charmer

steveb. SSC-Forever Points: 46733 More actions · Answer 8

Charmer (10/6/2011)
i tried, but im bad at coding part....could you help me by showing some sample codings

haev you got the other increment working?

I can put something together, but wont be until later on as I do not have access to a dev machine at the moment.

Charmer SSChampion Points: 14903 More actions · Answer 9

yes that is working on, and in this case i used adddays(1) but i get the same value for all the records rather than incrementing..so i don't know where my mistake is ?

Thanks,
Charmer