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:
- Select Native OLE DB\Microsoft Jet 4.0 OLE DB Provider .
- 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.
- 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.
- Click OK.
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
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.