Adding a Sequence Number to the records from LKUP No match output

Question

Adding a Sequence Number to the records from LKUP No match output

MuraliKrishnan1980

SSCrazy

Points: 2497
More actions
January 3, 2013 at 7:35 am

#272421

Hi Friends,
I am migrating data from one table to another and I am doing a lookup to find if the record already exists or not. I would insert the records in the destination if it is not present there.
The Destination table does not have an identity field. Hence, i would have to increment the PK value myself before inserting. I stored the max of the PK in a variable. I am trying to add a sequence number to the LKUP no match output so that i can create the PK for the new records with the logic max(PK) + Sequence number. However, i am not able to add a sequence number to no match output.
Is it possible to do the way i wish or is there any other better solutions.
Any help would be appreciated.
Thanks
Murali

Viewing 6 posts - 1 through 5 (of 5 total)

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

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

The Destination table does not have an identity field. Hence, i would have to increment the PK value myself before inserting

This sounds like a nightmare waiting to happen for you!

Is there any reason why you can't add an auto inc onto your destination table as the current structure will be horrible to maintain..

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

MuraliKrishnan1980 SSCrazy Points: 2497 More actions · Answer 2

You are absolutely correct Andy and we have already informed the same to our client. But for some reason they have gone by this design.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Any reason why you can't use the PK from the source table as the PK in the destination?

But for some reason they have gone by this design.

Crazy! :crazy:

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

MuraliKrishnan1980 SSCrazy Points: 2497 More actions · Answer 4

Well. The destination would be having data from multiple sources.. so i cant go with the PK from source...I found a way to achieve this in script task. But wanted to know if there is a way to do this using ssis itself

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 5

Because I don't know your structure it's a little difficult, but...

I have had to do similar things in the past (although I insisted on design changes) however a concatenation of Source + Source PK could be used?

If source is not an integer I would be tempted to maintain a lookup table for sources with a key for source and just append that to the source table(s) PK and do the

insert into the destination..

Others may have other ideas (or I may just be shot down in flames for mine 😉 )

Edit: sorry I didn't mean append a composite PK would be a better choice..

Andy:-)

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe