DTS load to multiple tables

I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I’m not sure if I am using the right tool for the job.I have a somewhat denormalized Access database that has to be loaded into a normalized SQL Server database.Values from one row in any of the source tables generally need to be separated and inserted into several destination (SQL Server) tables.There are no unique ids in the source data since it is coming from a 3^rd party and the tables are not related to others.I’ve created a DTS Package and have the beginnings of several Transform Data Tasks.Each destination table has an Identity id column, which is calculated automatically.The roadblock I’ve run into is that I can’t figure out how to take each newly created ID and insert it into a new row in another table as a foreign key.Basically, I have to move data from a single input row to new rows in multiple destination tables and create ids (PK, FK) that tie these tables together.The Transform Data Task only allows me to reference one source and one destination, not multiple destinations.I hope this makes sense.Any suggestions would be appreciated.

Here’s a simple example that may help illustrate the problem.My database is much more complex.

Input table is called parcels and each row has three columns:Address, Owner, and Legal_Description.

Output database has two tables that will receive this data:Parcel table will have the Address, Owner and Parcel_ID (auto calculated).Legal table will have Legal_Description, Legal_Desc_ID (auto calculated), and Parcel_ID.

When the row is inserted into the Parcel table, the newly auto calculated Parcel_ID has to be captured.Next create a row into the Legal table and insert the Parcel_ID so the two rows are related.

How can I do this through DTS?Thanks for any suggestions, code snippets, or references.

Due to the complexity of this task, I would suggest maybe doing this in a .NET winforms application. Set up ODBC connections to the two databases. Now write some queries in the Access database to divide the data up appropriately. Next, write insert procedures in the SQL Server database, which return an outparameter which is the id field. To get this, in the insert proc, use the @.@.IDENTITY or the SCOPE_IDENTITY calls to get the id value of the inserted row. Capture this in the .NET application, and pass this in to the insert proc in the related table. Alternatively, keep a cache of the data mappings, maybe in a temporary table or a dataset, and do the inserts as bulk inserts. Then run an update procedure which sets the foreign key based on the database mappings in the original database. So, for example, based on the Legal_Description in the legal table, update the Parcel_Id in the legal table using the owner and address values that used to share a row with the Legal_Description. Either approach should work.

let me know if you need more guidance here. Some of the SQL database engine people, or the SSIS people may be able to point you to a DTS solution that can do this. Alternatively, you could create a DTS solution that inserts into one table, inserts into the other, then calls the update procedure as described above. There is a separate SSIS forum (the new DTS), you may be better posting the question there.

HTH

For more T-SQL tips, check out my blog: