Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding a Sequence Number to the records from LKUP No match output Expand / Collapse
Author
Message
Posted Thursday, January 3, 2013 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 4:24 AM
Points: 121, Visits: 1,387
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
Post #1402376
Posted Thursday, January 3, 2013 7:38 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 689, Visits: 2,760
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
Post #1402382
Posted Thursday, January 3, 2013 7:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 4:24 AM
Points: 121, Visits: 1,387
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.
Post #1402383
Posted Thursday, January 3, 2013 7:43 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 689, Visits: 2,760
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!


==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Post #1402387
Posted Thursday, January 3, 2013 7:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 4:24 AM
Points: 121, Visits: 1,387
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
Post #1402395
Posted Thursday, January 3, 2013 8:09 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 689, Visits: 2,760
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
Post #1402404
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse