Suggestion on DTS look up -- sql server programming

RJN
5/7/2005 11:41:46 PM

Hi

I'm migrating data from the old schema to the new schema using DTS. The
old and new schema are different. In the new schema we have identity
columns as Primary keys and referred as foreign keys in the child
tables. In the old schema Primary keys are not identity columns. So when
I migrate old data, identity columns are newly generated for parent
tables. Now in order to establish the foreign key relationship in the
child table i.e, to map the identity value generated, I'm planning to
make use of lookups and active scripts something like this.

Say CategoryID is the Primary key in Categories table and an identity
column in the new schema and a foreign key in Products table.

Say CategoryName is the Primary key in the old schema and referred in
the child table Products as foreign key.

In the lookup I'll get the identity column value of new Categories table
using this query

SELECT CategoryID FROM Categories
WHERE (CategoryName = ?)

In the active-x script, I'll pass the old CategoryName value

DTSDestination("CategoryID")=DTSLookups("GetCategoryID").Execute(DTSSour
ce("CategoryName"))

Is this ok or is there a better way to do this

Regards

RJN

Uri Dimant
5/8/2005 12:00:00 AM

RJN
Let me understand you. You siad that CategoryName in the old schema is
referred in the child table Products.
I assume the column is defined as VARCHAR(n) datatype as well as the same in
the ProductTable

When you generate a new identity (CategoryId) it should be an INTEGER
datatype. I think create a FK column in the Products Table and map the PK
from Categories.

SELECT <column list> From Categories
SELECT @@scope_identity()

[quoted text, click to view]

sql server programming : Suggestion on DTS look up