Using SSIS to convert DBF files

dBase is database management system (DBMS) similar to SQL or Oracle for microcomputers that was originally developed in the early 1980’s.  Over the years, it has lost significant market share to competitors like Microsoft Access.  dBase’s underlying file format, the .dbf file, is widely used in many other applications needing a simple format to store structured data.

I rarely see any .dbf files but occasionally they do pop up and someone needs them converted.  In SSIS you can load this data by using the Native OLE DB\Microsoft Jet 4.0 OLE DB provider.

To do this, create a Dataflow task and select OLE DB Source.

01

Select a new OLE DB Source and choose Microsoft Jet 4.0 OLE DB Provider.

03

When working with DBF files, the pysical folder represents the database, and the individual DBF files represent tables. Therefore the database file name text box must contain the path of the folder where the DBF file resides, and must not include the file name itself. You can type or paste in a folder path, or you can use the ‘Browse’ button to select your DBF file, and then remove the file name from the end of the folder path.

04

Before clicking OK, click on the All tab on the right side of the connection manager. Scroll to the top and in the Extended Properties enter ‘dBASE 5.0’. (or the dbase version you are importing)

06

Once complete, select ‘Test Connection’ to ensure that you have set up your connection properly. In the OLE DB Source Editor, click on the drop down to choose the table you want to use for your OLE DB Source.

07

Click ‘Preview’ to ensure your data is being represented properly.

08

Once you complete that, you can proceed with your package.

Advertisements

Tags: , ,

4 Responses to “Using SSIS to convert DBF files”

  1. Wayne Benz Says:

    Thanks for the simple and precise explanation for dbf import. Went to a lot of other sites where it said it couldn’t be done or only through Access/Excel import. This worked first time through. Thanks again.
    Wayne

  2. Wytdyk Says:

    Be aware that the Microsoft Jet provider is not available on 64-bit systems, so if you develop a package on a 32-bit system it won’t work if you schedule it as a job on a 64-bit server.

  3. Kate Says:

    Fabulous! Very clear. Exactly what I needed. Thanks. ~~Kate

  4. Rgilbert Says:

    Thank you! Worked like a charm, would have never thought of the extended properties on my own

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: