There was a need to import address dictionary to SQL Server. The major problem was that the source files was a DBF files (dBASE format). I’ve searched the Google for using OPENROWSET function with DBF and found some examples. But no one could work.
The only working thing is SQL Server Integration Services (SSIS) package. In a very simple form it consists of 2 connections (one to DBF, the other to SQL server) and Data Flow task. The latter has OLE DB Source and SQL Server destination.
The most important is how to connect to DBF files. At first, you need to create a Connection Manager. It’s quite simple.
- Choose Native OLE DBMicrosoft Jet 4.0 OLE DB Provider
- Enter the path to directory where you DBF files is placed in the field “Database file name”.
- Leave User name and Password as is. I think OLE DB doesn’t use it when connecting.
- Click the All button on the left sidebar.
- Finally, enter “dBASE 5.0” in the “Extended Properties” field.
A particular DBF files is selected in the OLE DB Source component. SSIS automatically retrieves the list of all DBF files from the folder that you’ve entered in Connection Manager.
