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("GetCatego ryID").Execute(DTSSour
ce("CategoryName"))

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

Regards

RJN




*** Sent via Developersdex http://www.developersdex.com ***

Could you not set indentity insert on for the initial migration and then turn it off afterwards?

What about adding to the new schema tables a new column called OldKey INT. This way you can load the Parent table only. This will
give you a table filled with new idents and the old keys. When you bring across the child table you can simply join to the header
table on the OldKey attribute and retrieve the new key value.

Lookups are slow because they have to do something row * row. They are useful as well but they can hurt you.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - SQL Server 2005 Integration Services.
www.Konesans.com


"RJN" <rjn (AT) yahoo (DOT) com> wrote

Hi Allan

Thanks for your reply. Identity insert will be on when the new tables
are being created. Not sure if I was clear.

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("Categor yName"))

Is this OK? or is there a better way

Regards

RJN



*** Sent via Developersdex http://www.developersdex.com ***

Oh I see.

You had a Char(100) as a Primary key. Well, say hello to faster times I
think.

OK So you do not need IDENTITY INSERT on whilst doing this.

What I would do is load the Categories table.

I would then join back to the Source Products table on the CategoryName
attribute and do the insert into the Products table. (linked server?)

Whilst your plan works and if it is a relatively small dataset it is
viable, you have to remember that you lookup executes on every row
making it possibly slow.


Another solution is after inserting into the categories table you DTS
that back to the source if it is on a different server. Your Query then
to select the products can use that table now to do the join at the
source. This would probably be quicker.


Thanks

Allan


"RJN" <rjn (AT) yahoo (DOT) com> wrote