The EPS VFPConversion Data Conversion “tool” is a suite of tools that copy your data in VFP DBF files into a SQL Server database.

1. Create the tables in the SQL Server database
2. Create a set of SQL Server Integration Service (SSIS) data conversion packages
3. Execute the SSIS data conversion packages
4. Verify the data upload was complete and accurate

We admit that this is the same sequence of steps you would perform to upload VFP data without our tools. Or you could run the VFP database Upsizing Wizard. However…

• The process of manually creating SQL scripts and SSIS packages can be time - consuming and mind-numbingly tedious. We know… we’ve been there. That’s why we created the data conversion tools. We use these tools in our VFP conversion projects.
• The VFP Upsizing Wizard requires your DBF tables be contained in a VFP database container (DBC). If any of your tables are free tables, they will not be converted. In addition, you’re pretty well stuck with the field mappings and indexes created by the wizard. The range of functionality available to you in SQL Server is much greater than the options in the VFP Upsizing Wizard.
• The last step, verifying the data, does not have a corollary with the VFP upsizing wizard. And it can be the most significant pain point in the upsizing process.

The EPS Data Conversion tool suite is built around Microsoft’s SQL Server Integration Service (SSIS) technology for two reasons:

• Extensibility – SSIS packages (the processing unit that defines data conversion and transport) can be created with a GUI interface in the Business Intelligence Development Studio component of Visual Studio. SSIS packages can also be created and modified with .Net languages (e.g., C#) which is how EPS created the VFPConversion Data Conversion tool suite.
• Performance – EPS has observed SSIS packages to run more than three times faster than the VFP Upsizing Wizard.

The Dbf2SQL wizard does one job: It creates SQL scripts that are run in SQL Server Management Studio to create data tables and indexes. Simple as this may sound, if your database has any complexity this tool can save you no end to grief and aggravation. The VFP Upsizing Wizard allows you to save the conversion as a script but we’ve still found the VFP wizard to be lacking so we added some functionality:

• Add a GUID prime key to all tables
• Create SQL extended properties
• Allow NULLs in all columns
• Date values default to NULL or GETDATE()
• Adjust the data type to match content (e.g. Convert a NUMERIC(1) to a tinyInt)

Once you’ve recreated your DBF database in SQL Server, you still have to get the data into the SQL tables. SSIS is the fastest way we’ve found to upload DBF data but the process of creating the SSIS packages in Visual Studio leaves a lot to be desired and we’ve had the bags under our eyes to prove it.

And since you won’t be doing this more than once, the learning curve is steep and painful. The “one-time” data upload process is almost guaranteed to require several iterations as you find unexpected surprises in the data conversion. Neither SSIS or the VFP Upsizing Wizard easily support a re-entrant process.

We developed the SSIS Package Creation Wizard to ease this pain:

• Select DBF tables (free or contained) for upsizing in subdirectories
• Optionally
• Define the VFP SQL SELECT for the source
• Define the SQL Server SQL SELECT for the target
• Execute T-SQL commands prior to uploading the data. Something as simple as clearing out old data with a TRUNCATE TABLE command allows the upload process to be truly automated and re-entrant.
• Package execution can be logged and the log file stored, allowing you to quickly identify and correct upload issues.
• Save your selections, so you don’t have to remember everything.

Once the SSIS packages have been created, running them is simple… the first time. Rerunning the packages is a bit more challenging just because you have to keep track of what to run. No big deal really, but we created an interface to make the job just a bit easier:

But once the data’s been uploaded, how do you know it’s right?

The data comparison component of the SSIS Package Execution and Data Comparison tool automates the data validation process with:

• Multiple types of comparisons give you fast or thorough data validation options:
• Row count – Fastest validation
• Table structures – A little slower, a little more detail
• Compare the actual data – Most detail, but slowest to perform
• Pick and choose the tables to validate
• When data inconsistencies are found you have the option to:
• Stop the comparison – Prevent a long-running data comparison on a large table
• Keep running – Finds all data comparison failures
• Save log results for each table, per table

Simple as it may sound, moving a legacy DBF database into SQL Server should be a major evolution.
There are numerous advantages to upsizing including:

• Security
• Higher performance
• Scalability
• Automated backup

But many of these advantages come with costs which usually must be paid up front.
You have many challenges to address when upsizing your VFP database, let EPS take some of the load. The EPS VFPConversion Data Conversion tool suite was created in response to real issues EPS has encountered in the process of numerous VFP to .Net conversions.