Problem writing to VFP data using SSIS

[chriscboy]

Hi All,

I am trying to do an overnight copy of some SQL2005 data into VFP7 tables. I have tried using SSIS but I am having no joy. I am using an OLE DB Destination to get the data into VFP but I keep getting the following error:

[Insert Records [273]] Error: An OLE DB error has occurred. Error code: 0x80040E54. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E54 Description: "Number of rows with pending changes exceeded the limit.". An OLE DB record is available. Source: "Microsoft OLE DB Provider for Visual FoxPro" Hresult: 0x80040E54 Description: "Number of rows with pending changes exceeded the limit.".

Anyone had any success at all with using SSIS and VFP7 tables, or might be able to shed some light on this? I have no problem using OLE DB to read data from VFP but always hit a brick wall when trying to write data.

The other option I am thinking of trying is to write a VFP app to do the updating and have it run by the task scheduler on our database server. It just seems a bit crazy to go down this route when SSIS/OLEDB is supposed to do the job.

Thanks

Chris

 

[Olaf Doschke]

This error is coming from OLE, not from the VFP layer, it's a general OLE DB Provider specific error.

Are you simply exceeding the 2GB limit of foxpro?

You should investigate on some limitation eg row size, number of rows, packet size, anything in that direction.

Regarding to this article on that OLE DB error, it can also be something simple as a too wide value for the destination field:

http://www.adopenstatic.com/faq/80040e21.asp

So double check, if the DBF destination table structure is insufficient. One thing for example is, that SQL Server and VFP differ in how to define decimal places in a numeric field N(x,y).

Bye, Olaf.

 

[chriscboy]

Hi Olaf,

Thanks for your reply.

After further investigation I have found the issue. The records I was trying to insert into the VFP table, already existed as deleted records. I packed the VFP table, and now it is all working correctly.