SEQUENTIAL NUMBERING IN SSIS

Question

SEQUENTIAL NUMBERING IN SSIS

rob1.smith

Valued Member

Points: 51
More actions
April 14, 2011 at 3:42 am

#237609

Hi all, I’m after some advice for best strategy for creating some sequential fields as I load data into the database
Fields
DATA, JOURNAL, JOURNAL SEQUENCE, VOUCHER
So i have a data fields 50,000 rows
I need the Journal Field to SAY JOUNAL01 for increment every 1000 rows to journal02 etc
The JOURNAL SEQUENCE number needs to be 1.00,2.00,3.00.. for each 1000 lines then reset to 1.00,2.00,3.00.. for journal02 etc
The VOUCHER field needs to say voucher01, 02 etc continuously until voucher50000
I have to apply at the point of inserting to the database from within an SSIS package.
Any help and advise appriciated.
Regards
Rob

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

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

What's the source of the data? If it's SQL Server, you can solve this using appropriate T-SQL when you select the source data.

rob1.smith Valued Member Points: 51 More actions · Answer 2

HI Phil,

Thanks for the fast response,

when I said from within the SSIS package I meant the datasource is a dozen transormations later within the SSIS package - i.e. datasource is the output of another transformation - I have converted fields, added fields, at the piont that all my transaformation of the data fields are done this is the last task I need to do before inserting the transformed data into a table the fields being of any datatype I choose, but unicode string for jounal and voucher would be preferable.

regards

Rob

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

You could use a script component to do this in your dataflow. Keep some variables in the script that check the conditions and update the sequence accordingly.

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

Phil Parkin

SSC Guru

Points: 247180

April 15, 2011 at 2:24 am

#1311978

That's also the only idea I had for this one.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

Phil Parkin (4/15/2011)
That's also the only idea I had for this one.

That's because great minds think alike 😀

(Right now, I'm thinking of a combination of Friday and beer. You?)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

Oh yeah! My weekend started early when I couldn't get to work today (not my fault) 🙂 Haven't quite made it to the pub yet, but the beer is getting closer by the minute :smooooth:

rob1.smith Valued Member Points: 51 More actions · Answer 7

Phil Parkin (4/15/2011)
Oh yeah! My weekend started early when I couldn't get to work today (not my fault) 🙂 Haven't quite made it to the pub yet, but the beer is getting closer by the minute :smooooth:

Beer is on my mind too, 4 hour train journey back home, so a few expensive beers on the train,

The good news is my project is closing down for a week so im off to Thailand next saturday

yeaaa me 😀

Thanks for the pointer on the script component guys, will have a bash at this next week.

I'm usually working in informix so SQL server is a bit of a wake up call to the brain cells.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 8

Four hours, that's harsh. Post back if you have any questions about coding in the script component. As an Informix guy, this is a bit of a change for you, Good luck & watch out for women with adam's apples in Thailand:-)