I want to create a SSIS package to move data from a Foxpro 5.0 .dbf file to
a SQL Server 2005 table.
I tried to create a Flat File connection using the Flat File connection
manager, but the Flat File Connection Manager Editor wants information about
row and header delimiters that the Foxpro .dbf uses to separate records. I
tried combinations of delimiters the Flat File Connection Manager offered,
but none worked.
Following a Microsoft MSDN tip, I created an OLE DB Source connection by
right clicking "New" in the Connection Manager, then choosing "Native OLE
DB/Microsoft Jet 4.0 OLE DB Provider". I placed the path to the .dbf file in
the "Database File Name" box. Then clicked the "All" button, underneath the
"Connection" button, on the left side of the pane. I scrolled to "Extended
Properties" and entered "dbase 5.0"

The Test Connection said the connection was valid.

Then I entered the file name: audit.dbf. The connection test fails. The
error message says it failed because the path is invalid.

An Internet tip discussed name length. The file name must conform to the 8.3
convention. All the dbf file names conform to 8.3 .

When I remove the dbf filename from the path, the test succeeds. I know the
dbf file exists and contains data. The file is on a different server. Since
intel-a-sense shows the path and dbf file name, I know there is a connection.

A separate SSIS problem is with the Connection to SQL Server 2005 table that
will receive the data from the Foxpro 5.0 dbf file. The provider was set to
..Net Providers\SqlClient Data Provider; the Server Name is the resident
server. The Connection Test says the login failed. I have tried several valid
login IDs with the correct passwords and get the same invalid ID message.

Any ideas?

Thanks,
Mike Filipiak
GardenRidge
MFilipiak (AT) gardenridge (DOT) com