This has come up several times in the course of the last TWO jobs, so I’ll put it here for my reference (and yours).

To use a dBASE file as a source in SSIS (and I know you want to), in your new connection manager:

1. Select Native OLE DB\Microsoft Jet 4.0 OLE DB Provider .
2. Under Database File Name, enter the path to the folder containing the DBF file(s). For example, \\Server01\DBA\DB01\, if the DBF files reside in the DB01 folder.
3. Click the “All” button on the left and enter “dBASE III” (or “dBASE IV, or dBASE 5.0, as appropriate”, says Microsoft) in Extended Properties.
4. Click OK.

Secret Ingredient: Extended Properties!

Create a new data flow source and use your OLE DB/Jet connection manager; your drop-down list of tables will display each DBF file in the folder. That is to say, each DBF file in the folder is considered a table. Easy!  You know, once you know the trick.

Microsoft reference: How to: Connect to a dBASE or Other DBF File

Happy days,

Jen McCown

http://www.MidnightDBA.com/Jen/

Oh, P.S.! Sean will be at SQL Saturday 28 Baton Rouge this Saturday! There are still a few spots left, so go register and remember to be on #HairWatch!

ONE LAST P.S.! Remember to vot for the PASS Summit Community Choice sessions (specifically, Sean’s . Details here.