July 12, 2004 at 1:13 pm
I have just about completed designing a new sql server 2000 database, to track transactional data, based upon a group of existing FoxPro 5.0 free tables. I must now find a way to migrate all of the existing data in the VFP free tables into my new sql server database. The upsizing wizards can't be used, I don't think, because the databases are not the same structurally, not the same # of tables nor the same table names and fields.
I am hoping someone can point me in the right direction as to how to best approach this issue. I have not performed a data migration before and am open to suggestions!!
The sql database will serve as the backend to a .NET application that I have yet to create.
Thanks,
David Mixon
July 13, 2004 at 7:49 am
You should be able to use DTS. Put the free VFP 5.0 files on a folder that can be accessed by the SQLServer server. Then on the SQL Server PC do Start-Programs-Microsoft SQL server-Import and Export data. This starts DTS.
For your Datasource choose one of the Microsoft visual foxpro drivers and see if they will open the VFP 5.0 table files (they were probably designed for VFP 7.0 but should be backward compatible). If not, just export your data into flat files (fixed record length or comma separated values) and use the Text File data source. DTS allows you do some transformation of data between source and destination. If you can't do all the transformations you want then just load your tables into a staging table (a copy of your VFP table) in SQL server then work on it there. Though, if you know VFP well then it is easier to move your data into free VFP tables (provided that none would be bigger than the 2G limit) that mimic the new SQL server structure and then use DTS to do a straight copy.
My 2 cents is that VFP is a better environment for doing data migration than SQL server as it has many more transformation functions.
Data migration is usually an iterative process so it is better to work on the transformation process in a separate working area from your original source and final destination tables. In this 'working area' have a copy of the source and destination tables in same database environment (VFP or SQL server). This means you can easily clear down your destination tables, refine your transformations and populate your destination tables again. You can also regularly update your source tables from the original VFP 5.0 tables, if they are still being updated, and rerun the transformations again to check that there are no new transformation rules required. You can copy the data from the working area destination tables into your SQL server test environement for use in writing the application. Once you are happy that the transformations are correct and your system is live you can do a final conversion to load the VFP data into the live SQL server tables.
Hope this helps
Peter
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply