I have a legacy FoxPro database that's 4GB with around 200 tables. Some of the tables store old application data we don't need. I want to move the rest to a DB on our SQL server data warehouse. Everytime I start to troubleshoot some small issue, I end up reading a forum or blog post that says 'method x' is better than 'method y'.
I've toyed with a few different ways to attack this, and would like to hear what others have done/think:
-I don't have FoxPro installed, ruling out the upsizing wizard. No 3rd party software either
-I have successfully moved one table in a DTSX package in SSIS. Wonder if there is a way not to have to create a dtsx package for every table in the DB?
-Looked into BULK INSERT inside a FOREACH loop container. My data is in DBFs,not flat files though- would have to convert those first.
-I've toyed with using OPENROWSET and get some driver errors, but if this is a good way to go I can troubleshoot those.
Thanks for any input.