DTS package issue with identity destination column

Question

DTS package issue with identity destination column

EJ-128112

SSC Journeyman

Points: 92
More actions
July 24, 2007 at 12:03 pm

#68866

I have an identity column PK in a source and destination database table for which I need to copy rows. There are rows present in the destination table that have the same identity values as ones I am trying to move from the source table to the destination table. I knoe that I could just let the destination database generate the new identity values but the problem is that these identity values are used as a FK to another table for which I am moving data. Let me see if I can give an example and pose the question(s).
SourceTable1                                          DestinationTable1
pkIdentityCol    Col2                                 pkIdentityCol    Col2
1                    valueST1                          1                    valueDT1
SourceTable2                                          DestinationTable2
pkCol    fkCol    Col2                                 pkCol    fkCol    Col2
50        1         value ST2                         100       1        valueDT2
I can not use DTS to copy ST1 row above to DT1 unless I let the DT1 database generate the new identity value for me. If I do that how can I "store" the new identity value in order to use it in my next DTS package that copies the ST2 row to DT2? Is there a way to configure/script this with a DTS package or any reference scripts and/or urls anyone can provide? Thanks.

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

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

philcart SSC-Forever Points: 47797 More actions · Answer 1

Do you have to generate a new identity value? Can you keep the identity value that's been generated in the source table?

--------------------
Colt 45 - the original point and click interface

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

If you want the new identity to be generated in the destination table then don't move data from source to destination for that table. If you wat to move the data use set identity_insert on or the equivalent option in dts so that the idebtity columns values are also transferred.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

EJ-128112 SSC Journeyman Points: 92 More actions · Answer 3

Basically, where I'm at is that I have to move data from the source table to the destination table and I have to generate new identities for the rows being moved when they are inserted into the destination table. If I did this with straight TSQL, I could use the @@IDENTITY value to store into a variable and then use that for my FK value when I inserted (source-->destination) into the child table. I need some way to simulate this behavior via DTS, is there a way to potentially do this?