Imprting to a parent and child tables from two csv files using dts package

[kunju]

Hi,
I want to import data to a parent and child table from two different csv files.
Csv file for the parent table have got the primary key(integer) field and child have the forien key . My problem is that the last primary key value in the parent table may not match with the new ones . So i need to create ID's on import . IS there any way to relate these two tables in this manner using dts package.

Thanks
Anuja

[almaz]

You cannot "automagically" link the tables, as DTS inserts data from different sources using different tasks. So you'll have to:
1. Insert both source tables into temporary tables with parent temp table containing additional identity field (let's name it new_id). (using 2 tasks)
2. In a single SQL task:

1. Open transaction.
2. Find the largest identity value in the target parent table (Let's name it @MaxID).
3. Insert data from parent temp table into target parent table, using new_id + @MaxID as a primary key (you'll have to execute SET IDENTITY_INSERT ... ON first).
4. Insert into target child table data from temp data table, joined on temp parent table, using resolved new_id + @MaxID as a reference to parent table.
5. Close transaction.