May 15, 2003 at 1:31 am
I am creating a new database for a company. They have an existing database in FoxPro, and the data will need to be migrated across. The existing table structure is not normalized at all.
I have the table structure set up for the new database, and when creating the DTS package, I have one table that needs to pump into several tables. I have a source and a destination, and for each destination table I have a 'Transform Data Task'. So I then have e.g 5 or more 'Transform Data Tasks'. For every row in the source table I want the data to go to the correct table, but it looks to me like at the moment I am not doing it in the optimum way. For each 'Transform Data Task' can there only be one destination table?
I also have lookups for things like 'Title' and 'country'. the source table has 205,000 rows in it. Would it be better to transform the data before doing the DTS, as if I have to do lookups on each row, it will slow the data transfer down.
I want to write a DTS package as I am just testing migrating the data at the moment, but the real migration will happen in October, and I want to have something set up ready for then, to be able to pull it across with the minimum amount of duplication of effort.
May 15, 2003 at 5:50 am
Hi Taffy - common problem this. Firstly the transorm pump can only take one destination in the actual destination tab. However there ways of sending the data to other destinations. You can use lookups to insert, update and delete data as well as lookup data. You can use an activeX script to call the lookup or write an activeX script to insert the data using ADODB Recordset.
I have a question logged on this site regarding relational data imports.
Lastly splitting the data into related tables in an empty dB is fairly simple. It's similar to the flatfile solutions posted previously.
Set up a pump task. Set up lookups to insert the fields into the appropriate tables (bookups on line has some examples). Set an activeX script to call the lookups depending on the DTSSource Field. If this is a one off import then performance shouldn't be as important.
This is one suggestion but I'd like to see what others have to say?
Keith Davies
Regards
Keith Davies
IT Consultant
Regards
Keith Davies
IT Consultant
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply