Saturday, August 3, 2013

Importing DBF files to SQLServer Table using SSIS

Recently we had the requirement of importing data from multiple dbf files to SQL Server database. This post explains the step by step procedure of how this can be achieved using SSIS and also discusses the resolution to an issue faced while trying to configure the DBF connection manager.
The scenario is like this. There's a third party application which generates data feeds in dBase IV format (.dbf files) and sends it one of the fileshare within our network. The requirement was to create a package to process the file and send it to SQL Server table.
The package will look like below

The For Each Loop can be configured as follows

The folder is as follows

The data flow task is as follows
The DBF connection is configured as follows

The folder path is passed as value for database filename.

Set the appropriate type (dBASE IV, dBASE 5.0 etc) in the extended properties and test connection to get connection successful message.
Now go to data flow task and map this connection manager.
Select table or view and on selecting name of dbf from the dropdown you'll get the below error.

Now what has happened here. The error message suggests issue is with finding object with name chosen. After few minutes of research I understood the reason. DBF supports only maximum of 8 characters for the  tablename. I renamed filename to Customer and then was able to preview the data.

Execute the package after connecting OLEDB destination to SQL table and you'll get records transferred to the table from DBF file.

To ensure that the filename issue is resolved, we need to do few modifications to the package as below
The modified package will involve File System Tasks before and after the data flow task to rename the file to Customer to make it within DBF limit of 8 characters. After the processing the file name is reverted back to the original name and archived.