Data Flow Source for dbf (DBASE IV) files

Question

We're trying to read DBASE IV files as a source, but can't find any providers for that format. Will these be included in the final release? Is there another way? DBASE has always been supported, so it's kinda stranged.


Thanks,
Dirk

Answer 1

use the Connectionstring for JET:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=c:\somepath;" & _
          "Extended Properties=DBASE IV;"

Regards,
Willfried

Answer 2

In fact I believe that the MDAC and Jet components supporting dBase access were deprecated some time ago, but I can't find that in writing at the moment.

Your best bet would be to use the Jet OLE DB Provider, on a machine where the supporting ISAM driver is still installed, with some values specific to dBase files, so that a sample connection string might look like this:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\folder;Extended
Properties=dBASE IV;User ID=Admin;Password="

The Data Source property identifies the folder, not the file; your query will identify the individual file as the table. The Extended Properties contains the dBase file version.

Answer 3

Hi Willfried, hi Douglas,

thanks for your reply. Now I tried the following steps:

1. Create a local Folder F:\Importdata
2. Copy one of my dBASE files in this new folder and name it "flgdetalRef.dbf"
3. In the Connection Managers tab I create a new OLE DB Connection

     ConnectionString:
 Data Source=F:\Importdata\;User ID=Admin;Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=DBASE IV;

4. In my SSIS Project I drop an OLE DB Source and set theOLE DB connection manager to the Connection from step 3
5. Data Access Mode is set to "Table or view"
6. The combo "Name of the table or the view" loads dynamicly at the first drop down and shows my file/table name from step 2
7. I select this table and click the button "Preview"
8. I get the following error:

===================================

Error at Data Flow Task [OLE DB Source [2073]]: An OLE DB error has occurred. Error code: 0x80040E37.

Error at Data Flow Task [OLE DB Source [2073]]: Opening a rowset for "flgdetalRef" failed. Check that the object exists in the database.

 (Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
   at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
   at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)

I don't understand the hint from Douglas with the "old supporting ISAM driver". On my PC I have Office 2003, VS2003 and VS2005 Beta 2 installed.

Any more ideas?

Answer 4

I wonder if the Visual Foxpro provider would be able to read them.  It would be worth a try..

-Evan Black

Answer 5

Can you try setting all the properties through the OLE DB Connection Manager, instead of by setting the ConnectionString property directly?

I just did a successful test with a .DBF file and an OLE DB Connection Manager and Source, copying to a flat file destination.

In the OLE DB Connection Manager, I
1) chose the Jet provider
2) entered c:\debtest as the "Database file name" (without the closing "\")
3) on the All tab, for Extended Properties, entered DBASE IV

If nothing else, try omitting the closing backslash from the folder name.

You should see not only your table names, but successfully see your data in the Preview windows of both source and destination connection managers.

I do not know whether your difficulties may be related to the build that you have.

Best regards,

-Doug

Answer 6

I'm not able to find the Visual Foxpro provider in the providerlist. Where schould it be and if I have to install is separately - where can I find it?

Answer 7

OK, I think it was my fault...

I reveived at several dates DBASE files from my customer and archived them in the same local folder on my PC. When I received the mails with the single DBASE attachment, every file had the same filename. I changed the name for each file to xxx_1.dbf, xxx_2.dbf etc. to store them all in my central import folder for later processing with SSIS.

Now I have tried to import one of these (renamed) files with MS Access 2003 and Access was not able to read the file. It stopped with a similar error as seen in SSIS. Then I created my own DBASE IV file with some data from Northwind and everything worked fine in the way Douglas described the process.

After renaming one of my xxx_01.dbf files to the original name I was able to import these files too.

Thanks for all your assistance.

Best regards,
Dirk

Answer 8

The Visual Foxpro 9.0 oledb driver can be downloaded from http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx

Foxpro can import files from dBase, but its file formats are different and it might try to update the format.  If you try it, test it out on a copy of the file.  If you can get the dBase IV driver to work as described above, I would recommend that.

-Evan Black

Answer 9

I am new to the discussion but I had the same problem importing my dbf files.  Doug' explanation worked, however when I try to access the tables, "External Tables are not in expected format."  Anyone? 

 

 

 

 

Answer 10

It's been too long since my Support days to recall this error message.

If you own Access, I would try importing your DBF file into Access. If that works, then we should be able to get it to work from SSIS, I would think.

These are standalone DBF files and not tables that belong to a DBC, right?

-Doug

 

Answer 11

They are tables for a FoxPro database, however the are not held in a database container but as stand alone files, if that makes sense.

 

Answer 12

I've searched previous support incidents and newsgroup postings involving the "External tables are not in the expected format" without finding any definitive explanation of the problem or its solution.

Again, I would recommend that you try importing into Excel or Access from the DBF. Maybe you'll get a better error message, or maybe getting the same message will confirm that it's an issue (such as record size?) with the file itself.

-Doug

 

Answer 13

EvanBlack wrote:

The Visual Foxpro 9.0 oledb driver can be downloaded from http://msdn.microsoft.com/vfoxpro/downloads/updates/default.aspx Foxpro can import files from dBase, but its file formats are different and it might try to update the format.  If you try it, test it out on a copy of the file.  If you can get the dBase IV driver to work as described above, I would recommend that. -Evan Black

 

I am using 64bits SQL server, is this latest VFP 9.0 oleDB driver features to import the data from VFP database / free table to SQL 64 bits server?

I have tried to import a free table but got the following result

[VFP_Employee [891]] Error: The AcquireConnection method call to the connection manager "E:\Project\MTsys" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "VFP_Employee" (891) failed validation and returned error code 0xC020801C.
[Connection manager "E:\Project\MTsys"] Error: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".

 

Answer 14

 

Has anyone had any trouble moving a package using a OLE DB Connection Manager reading DBASE IV files? While developing I never had a problem, the confiugration string described here worked just fine. Now I'm finding as I move my package to production I can't access any of the files. I will always get a error as follows...

The AcquireConnection method call to the connection manager "ConnMangerName" failed with error code 0xC0202009.

 

Answer 15

 

I'm not sure how its going to look to everyone else, but apparently my post got inserted 1/2 way up the the first page..

Answer 16

If you resolved the above issue how did you resolve it?

Answer 17

I haven't seen this mentioned yet in this thread, but one wrinkle I keep running across is that the filename of the .dbf being referenced must adhere to 8.3 format when using Jet provider. If the file name exceeds this length it can be the cause the obscure error others have mentioned.

Answer 18

Thank you for reminding us of that...I haven't run into that limitation since I worked in Microsoft support many years ago. I'll see about adding that to the relevant Books Online topic.

-Doug