Sunday, July 20, 2008

Importing DBF files into SQL Server with SSIS

I was wondering why sometimes I have no trouble importing .dbf files with SSIS and other times it's a no-go. Rebekah has a nice Word doc that explains the specifics of doing this, but there is one more thing you have to know, or you'll go insane like I did. Anyway, many thanks to Timothy Chen Allen for pointing out that the .dfb files must be named in 8.3 notation! MS either doesn't know this or doesn't feel like sharing that particular piece of info. Here's an excerpt from Timothy's blog (http://timothychenallen.blogspot.com/2007/07/how-to-import-dbase-tables-into-sql.html):

-----

Ah, SQL Server Integration Services (SSIS). Just when I'd learned to love Data Transformation Services (DTS) in SQL 2000, they completely changed how Extraction, Transformation, and Loading (ETL) is done in SQL Server 2005.

That's okay. Something else to throw on the resume.

At my current position I have to read in dBase files (DBF) a lot. We use ESRI ArcMap and everyone here uses ESRI shape files, which put data in DBFs. So I had to learn how to read in DBFs in SSIS.

I was going to go into all the troubles I had doing this, but the fact is there is just so much that can go wrong here. Here is one way to make things go right:

  1. Change the name of the DBF file you want to read in to be 8.3 format. That is, 8 characters before the dot and 3 after. e.g. If the data file is named "2007 Data Load for Finance.DBF", change it to FIN2007.DBF or some such. SSIS will reckognize the DBF if the file name is not in 8.3 format, but will not be able to read data from it. This caused me much heartache before I figured it out.
  2. In SSIS, set up an OLE DB Source. Hit the "New..." button for the OLE DB connection manager". Choose "Native OLD DB\Microsoft Jet 4.0 OLE DB Provider" as the provider.
  3. For the Database File Name, put in the path to the DBF, but not the DBF file name itself. You will not be able to browse for this, because SSIS is looking for an MDB file at this point, which is not what you want. e.g. if your DBF is in c:\databases\FIN2007.DBF, put in "C:\databases\".
  4. Click the "All" button, scroll up to Extended Properties, and put in "dbase 5.0". If you don't do this, SSIS will try to read your dBase file as an Access file, which will fail. While you're here, you can hit "Test Connection" and it should work.
  5. Hit OK until you are back at the OLE DB Source Editor screen. Choose the name of the database file from "Name of the table or the view".
  6. At this point you should be able to hit "Preview..." and see your data. You can now use this DBF connection as a data source.

11 comments:

Tom Demask said...
This comment has been removed by the author.
Michael said...

I read your post with great relief. I am going crazy trying to get an SSIS package to read a Foxpro 5.0 .dbf file, and move it to a SQL Server 2005 table. I followed you post, and everything works as advertised through the "Test Connection." After I add the dbf file name, the connection test fails. The error message says it failed because of an error in the initializing provider, and says the path is invalid. When I remove the dbf filename from the path, the test succeeds. I know the dbf file exists and contains data.
If you have any ideas, please respond. Thanks

Tom Demask said...

Hey Michael. You cannot put the filename in path, only the directory containing the dbf file(s). Then you must add the "dbase 5.0" in the extended properties and finally put the actual 8.3 dbf filename in the Name of the Table field. It's a goofy procedure but it does work in the cases I've tried. You have to use the 8.3 file naming convention though.

OCD said...

Everything works until i hit the preview button. Thats when i get the error message below.. Is there another setting that i need to modify?

TITLE: Microsoft Visual Studio
------------------------------

Error at GalaxyFac [OLE DB Source [38]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

Error at GalaxyFac [OLE DB Source [38]]: Opening a rowset for "fee" failed. Check that the object exists in the database.



------------------------------
ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
BUTTONS:

OK
------------------------------

Angrandir said...

ocd: I just experienced the same problem - in my case it was caused by corrupted DBT files (empty DBTs were 4 bytes long instead of 512 bytes - export bug in some third party software), after fixing them everything is OK.

Anonymous said...

I am also getting the same error as ocd.But my files are not corrupt.Please help.

rebekah said...

Anon - Is your file named in an 8.3 format? Meaning XXXXXXXX.DBF ? If it's not then this won't work and you'll have to rename the file. And Like Tom said - MUST add the "dbase 5.0" in the extended properties (let me know if you can't find this). What does your setup look like? Any more details you can shoot out way?

Alexis said...

For work with databases I advise-dbf recovery,because in my view tool is quite reliable in this sphere and it is free as far as I know.Moreover program can also analyzing data in damaged dbf files and associated files containing MEMO fields (fpt or dbt depending on the version).

Anonymous said...

Thank you so much. This post saved me a lot of trouble

Silviu Niculita said...

I had no idea the 8.3 filename limitation was still around.

And it only cost me 2 hours of productive time trying to solve a cryptic error.

Thanks!

Илья Осипов said...

Thank you!