We recently had a client who needed to query data from a legacy system built on FoxPro. What is FoxPro you ask? Fox Software was a company Microsoft purchased back in the early 90s, and they developed a system that accelerated the development of business / database applications. You can think of FoxPro as a technology in between Access and VB. It is more complex and powerful than Microsoft Access, but requires less code to write than a VB.net application.
The problem now is that Microsoft has stopped the development of FoxPro for the last 10-15 years, and the supporting tools have been slowly decaying. There is no longer an ODBC drive for FoxPro. So if you want to query a FoxPro database, you’ll need to use the OLE DB drivers. The latest drivers can be downloaded here: Microsoft OLE DB Provider for Visual FoxPro 9.0 SP2
And for any of the latest Microsoft downloads for FoxPro, go to: https://msdn.microsoft.com/en-us/library/mt490121
So what if you want to import data into SQL Server using the Data Import Wizard, or add the FoxPro database as a linked server? Well, one issue you might run into is that the FoxPro OLEDB drivers are only 32 bit – sorry no 64 bit versions. So if you’re running a 64 bit version of SQL Server, then you won’t be able to do it. So what can you do? Install a 32 bit version of Sql Server. As of this article, SQL Server 2016 has no support for 32 bit, so you need to download an earlier version. SQL Server 2014 has a 32 bit version.
So how are we going to get data out of FoxPro? The basic flow is going to look like this:
FoxPro Db -> Linked Server -> 32 bit Sql Server Instance -> Composable -> 64 bit SQL Server
We’ll link the FoxPro db to the 32 bit version which will allow us to write SQL queries against the FoxPro db. We’ll then write a syncing dataflow in Composable to query the 32 bit SQL Server instance and insert the records in another SQL Server instance.
First, create a Linked Server in SQL Server:
- Download and install an x86 version of SQL Server 2014.
- Download and install the OLE DB FoxPro drivers.
- Add a FoxPro Linked Server to the x86 SQL Server instance
Set up the Linked Server as follows:
- Server Objects -> Add Linked Server …
- Provider: Microsoft OLE DB Provider for Visual FoxPro
- Product Name: VFPOLEDB
- Data Source: Set the path to the folder containing the FPT and DBF files.
- Note that a file share may cause permission errors, so start out with the local folder that the SQL Server engine users have permission to access.
- Provider String: VFPOLEDB
- In order for the Linked Server to work properly under a 32-bit process and with the necessary permissions, the FoxPro OLE DB Provider needs to be configured with
Allow inprocess, which will execute the provider within the SQL Server process.- Linked Servers -> Providers -> VFPOLEDB
With the Linked Server configured, you can simply write a query such as:
|
1 |
select * from [LinkedServerName]…[FoxProTableName] |
Now, all that’s left is to create a DataFlow that runs the above query and inserts data into another SQL Server instance. Note that this DataFlow will transfer the data very efficiently – capable of moving millions of rows in just a few minutes.
Congratulations! You just queried FoxPro data from Composable.
If you’re dealing with complex data migration and ETL tasks that require efficient and reliable execution, Composable DataOps Platform was purpose-built for you. Discover the power of Composable and revolutionize your data integration processes today.
