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.
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.
