DTS help - using look ups

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.
Old Schema:
Categories table:
Category name varchar(100) Primary key
Products table:
ProductID int PK
Category Name varchar(100)FK
New Schema:
Categories table:
CategoryID int identity Primary key,
Category Name varchar(100)
Products table:
ProductID int PK
CategoryID int FK
When migrating Categories there is no issue as the identity column
CategoryID is newly generated. When migrating Products table, I should
get the CategoryID value for corresponding Category Name. Shall I use
look ups for this?
SELECT CategoryID FROM Categories
WHERE (CategoryName = ?)
In the active-x script, I'll pass the old CategoryName value
DTSDestination("CategoryID")
=DTSLookups("GetCatID").Execute(DTSSource("CategoryName"))
Is this OK? or is there a better way
Regards
RJN
*** Sent via Developersdex http://www.examnotes.net ***Hi
You could allow identity inserts and keep the old primary keys? This may
help with the products table. If you have gaps in the old Primary keys you
can remove then by allowing cascading updates on the FKs you can then re-ran
k
them.
If you don't want to use lookups, then you could store the transfer the
category name into the products table and then do your own updates to get th
e
categoryid once all the data is imported. The categoryname can then be
dropped from the products table using an ALTER TABLE statement. If the FKS
are in place during the data import you may have to default the Categoryid t
o
a known value that will not violate the FK. This may be quicker than using
lookups.
A tutorial on lookups can be found at:
http://www.sqldts.com/default.aspx?277,1
John
"RJN" wrote:

> 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.
> Old Schema:
> Categories table:
> Category name varchar(100) Primary key
> Products table:
> ProductID int PK
> Category Name varchar(100)FK
> New Schema:
> Categories table:
> CategoryID int identity Primary key,
> Category Name varchar(100)
> Products table:
> ProductID int PK
> CategoryID int FK
> When migrating Categories there is no issue as the identity column
> CategoryID is newly generated. When migrating Products table, I should
> get the CategoryID value for corresponding Category Name. Shall I use
> look ups for this?
> SELECT CategoryID FROM Categories
> WHERE (CategoryName = ?)
> In the active-x script, I'll pass the old CategoryName value
> DTSDestination("CategoryID")
> =DTSLookups("GetCatID").Execute(DTSSource("CategoryName"))
> Is this OK? or is there a better way
> Regards
> RJN
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>