How to Import FoxPro 2.x Tables into Microsoft SQL Server

Legacy FoxPro 2.x: Why it Still Matters

FoxPro 2.x was a cornerstone of many business applications during the early 1990s. It provided a lightweight, file‑based database system that could run on modest hardware while delivering solid performance for the workloads of that era. Even decades later, some companies still run mission‑critical applications that depend on FoxPro 2.x tables. When a firm decides to modernize its data layer - moving from the old file system to a robust, scalable platform like Microsoft SQL Server - the question arises: how can those legacy tables be brought into the new environment without losing data integrity or introducing downtime? The answer is not as simple as it seems, mainly because the tools that once made the migration painless are no longer available in the same form today.

At the heart of the problem is the way FoxPro 2.x stores its data. The system uses .DBF files that are organized with a specific structure, including record headers and field descriptors. Unlike modern relational databases, these files do not enforce strict data types or constraints, and they lack features such as foreign keys or default values. While this flexibility was an advantage for rapid development, it also means that importing the tables into SQL Server requires careful mapping of field types and handling of nullability. SQL Server, by contrast, demands that every column has a defined data type, and that nullability rules are respected. If a FoxPro table contains values that SQL Server cannot interpret - such as embedded null characters or unsupported character sets - the migration will fail or produce corrupt data.

Another challenge comes from the fact that Microsoft discontinued support for the original FoxPro ODBC driver many years ago. The driver that once allowed seamless connections to .DBF files was replaced by the Visual FoxPro Driver in MDAC 2.1. The newer driver can read Visual FoxPro tables (.VFP), which differ from the older 2.x format in several subtle ways. For example, Visual FoxPro uses a .DBT extension for binary fields, whereas FoxPro 2.x stores binary data directly within the .DBF file. Because of these differences, the older driver is no longer distributed with Windows, and many modern systems cannot even locate it. If a developer tries to connect to the legacy files using the outdated driver, the connection attempt will fail with a generic error such as “The Microsoft FoxPro driver is no longer supported. Use the Visual FoxPro Driver instead.” This warning points to a deeper incompatibility that cannot be ignored.

Because of the discontinued driver, most developers are forced to use the Data Transformation Services (DTS) wizard, which still supports the older FoxPro file format via the “Microsoft FoxPro VFP Driver (*.dbf)” option. DTS is a legacy tool that has been superseded by Integration Services in SQL Server 2005 and later, but it remains available in earlier editions and can handle the file‑based nature of FoxPro tables. The wizard can copy data directly from the .DBF files into SQL Server tables, but it requires a set of pre‑migration steps that are often overlooked. These steps include stopping any application that might lock the files, creating a Data Source Name (DSN) that points to the folder containing the .DBF files, and configuring the DSN to avoid null values - something that FoxPro 2.x cannot handle. Without these precautions, the migration can silently truncate data or insert incorrect defaults, leading to downstream problems in the new database.

In short, moving from FoxPro 2.x to SQL Server is a classic case of legacy data integration: the source system uses an outdated format, the drivers are no longer supported, and the destination system enforces stricter data rules. Understanding the constraints of both sides - how FoxPro stores data, how SQL Server expects data, and what tools can bridge the gap - is the first step toward a smooth migration. The next section explains how to set up the environment so that the data can be read correctly before the actual import begins.

Preparing the Environment for Migration

The migration process starts by isolating the FoxPro 2.x files from any running applications. The most common scenario is that a legacy client or server process is accessing the .DBF files, which keeps them locked and prevents an external tool from reading them. To avoid corruption, shut down any software that might be using the files, or temporarily stop the associated service. If the files are stored on a network share, ensure that no user session is actively connected to the share. Once the files are released, you can safely proceed to the next step: creating a DSN that tells the Data Transformation Services wizard how to connect to them.

In SQL Server, launch the DTS wizard by navigating to “SQL Server Management Studio” → “Tools” → “Data Transformation Services Wizard.” The wizard offers a guided interface that will walk you through the import. When you reach the data source selection screen, look for the option labeled “Microsoft FoxPro VFP Driver (*.dbf).” Despite the name, this driver supports the older FoxPro 2.x format, provided the files are located in a single directory without accompanying .DCB (database control block) files. If you already have a DSN that points to the folder, delete it first to avoid confusion. Click “New…” under the “User/System DSN” radio button, which opens the “Create New Data Source” dialog.

The dialog offers three choices: “Microsoft FoxPro VFP Driver (*.dbf),” “Visual FoxPro Driver,” and “Microsoft ODBC for Visual FoxPro.” Choose the first option, which is the only one that can read FoxPro 2.x tables. Click “Next.” At this point, you must give the DSN a name that will appear in the wizard’s data source list. For example, “FoxProLegacyDSN.” Then click “Finish” to close the dialog. You’ll be returned to the wizard, which will now list the DSN under the “Data Source” drop‑down.

Before you move forward, double‑check the driver’s configuration. Open the “Data Source Administrator” (search for “ODBC Data Sources” in Windows), locate the DSN you just created, and click “Configure.” In the driver configuration window, you’ll see an option labeled “Use the Free Table Directory.” FoxPro 2.x tables do not use .DCB files, so selecting this option tells the driver to treat every .DBF file in the directory as a table. Keep the option checked. Next, uncheck the “Allow NULL values” check‑box. FoxPro 2.x tables cannot store NULLs, so allowing them would cause data to be omitted or replaced with default values during the import. After making these changes, click “OK” to save the DSN settings.

Now you can safely return to the DTS wizard. The next screen asks for the folder path that contains the .DBF files. Browse to the directory, ensuring that the path ends with a backslash (e.g., “C:\LegacyFoxProData\”). The wizard will list all .DBF files it finds. If your FoxPro database includes indexes (.CDX files) or memo files (.DBT), you don’t need to worry about them; the driver will read the data from the primary .DBF files only. Click “Next” and the wizard will display the SQL Server instance and database you plan to import the tables into. If the target database does not yet exist, create it manually before proceeding.

The next step is selecting which tables to import. You can choose to bring in all tables, or filter the list to include only the ones you need. For each table, the wizard will automatically generate a corresponding SQL Server table name. If the name conflicts with an existing table, the wizard will prompt you to rename or skip it. At this point you can also review the column mappings: the wizard shows each field’s name, type, length, and nullability. If a field appears as a generic “VARCHAR” that is too wide, you may adjust the length to match the actual data. Be mindful that FoxPro data can contain extended ASCII or Unicode characters; if the source data uses a different code page than the destination server, you may see garbled characters. In such cases, change the column type to “NVARCHAR” and set the correct length.

Once you are satisfied with the mappings, click “Next” and then “Finish.” The DTS wizard will start copying data from each .DBF file into the target SQL Server database. Depending on the volume of data, the process may take several minutes. The wizard provides a progress bar and logs any errors that occur. Common errors include “null values not allowed” or “data conversion error.” These usually stem from mismatched column definitions, so revisit the mapping section if you see them. After the wizard completes, verify the data by running a quick SELECT on each imported table. Confirm that row counts match the original FoxPro tables and that no data has been truncated or corrupted.

That covers the core steps required to set up the environment, create a DSN, and use the DTS wizard to import FoxPro 2.x tables into SQL Server. While the process is mechanical, paying attention to each detail - especially the driver settings and nullability - ensures a reliable migration.

Common Issues and Workarounds

Even after following the steps above, some migrations hit snags that aren’t immediately obvious. Below are the most frequent problems developers encounter when moving FoxPro 2.x tables to SQL Server, along with practical solutions.

1. Locking Errors If you see messages like “The file is in use by another process,” it means one or more FoxPro applications are still running. Double‑check that no user sessions are connected to the network share, and that services such as the FoxPro database engine or any custom background jobs have been stopped. Sometimes antivirus software also locks files; temporarily disable it or add the folder to its exclusion list.

2. Character Set Mismatches FoxPro 2.x stores strings in a code page that may not match the code page of the SQL Server instance. If you notice garbled text, adjust the database’s default collation or explicitly set the target column to NVARCHAR and change the source collation in the DSN. Another approach is to run a script that converts the data after import, using SQL Server’s built‑in CONVERT function to shift from the old code page to UTF‑16.

3. Data Truncation The DTS wizard may silently truncate data if the destination column length is shorter than the source field. Review the column mappings carefully before starting the transfer. If you find a field that appears to have a length of 1,000 characters but actually contains longer strings, increase the target size to the maximum length you anticipate. This is especially important for memo fields that can hold up to 2 GB in FoxPro 2.x; map them to NVARCHAR(MAX) or VARCHAR(MAX) in SQL Server to preserve the entire content.

4. Nullability Violations FoxPro 2.x tables never allow null values, but if the DTS wizard incorrectly marks a column as nullable, the import will fail with an error. Verify that the DSN’s “Allow NULL values” option is unchecked. Additionally, in the mapping screen, ensure that each column is set to “Not Nullable.” If you have columns that truly can be null in the legacy system (perhaps because they were never populated), convert them to nullable in the target schema manually after the import.

5. Performance Bottlenecks Large FoxPro databases can take a long time to import. To speed up the process, split the import into batches: first import the tables that have the fewest rows to get the schema set up, then import the larger tables in parallel using separate DTS packages. Alternatively, use SQL Server Integration Services (SSIS) if you’re on a newer SQL Server version; SSIS offers more control over batch sizes, compression, and logging.

6. Missing Indexes and Constraints FoxPro 2.x tables typically don’t enforce foreign keys or unique constraints. After the import, you’ll need to rebuild indexes to improve query performance. Use the DBCC CHECKIDENT command to reseed identity columns, and run CREATE INDEX statements for any frequently queried columns. If you had primary keys in FoxPro, define them explicitly in SQL Server after the import; this will also help maintain data integrity in the long run.

7. Date and Time Handling FoxPro stores dates in a proprietary format that can be misinterpreted by the DTS wizard. If you see incorrect dates, change the data type of the target column to DATE or DATETIME and use a conversion function during import. A common trick is to cast the FoxPro date field to VARCHAR in the source query, then wrap it in a CONVERT function on the destination side.

By anticipating these common pitfalls and applying the suggested fixes, you can avoid many of the headaches that accompany legacy database migrations. The key is to treat the process as a data quality exercise: validate data after each step, confirm row counts, and compare sample records against the original FoxPro tables. Once you have a clean, reliable SQL Server copy, you can begin refactoring your applications to use the new data source and enjoy the benefits of a modern relational database system.