Transform data to multiple destinations? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Transform data to multiple destinations?

I have SQL Server 2003 Standard and am attempting to use DTS for a data load/transformation and I#%92m 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 split and inserted into several destination (SQL Server) tables. There are no unique ids in the source tables because all relevant data is stored in a single table. I#%92ve 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#%92ve run into is that I can#%92t 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 have to be able to fire off a SQL statement or other method that allows me to insert or update into another table during the transform data task. I hope this makes sense. Any suggestions would be appreciated. Here#%92s 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.
You need a staging table where you insert the key columns from the Access data that identify the Parcel, and where you have a nullable Parcel_ID column. First you insert those key columns from the Access data into the staging table. Then you insert the actual parcel data into SQL Server. Finally, you update the Parcel_ID column on the staging table with a lookup value from the SQL Server table, like this (assuming you have two key columns for the parcel in the Access data) … UPDATE tStaging SET Parcel_ID = tSQL.Parcel_ID
ON tStaging.AccessKeyCol1 = tSQL.KeyCol1
AND tStaging.AccessKeyCol2 = tSQL.KeyCol2 etc. And finally you insert the legal details from Access into SQL Server, joining the staging table on the "legal" table from Access, taking the Parcel_ID from the staging table, again linking on those key columns.