Martina Ponca's Blog

Environment:
Windows XP Professional (32-bit)
SSIS 2008 R2
Linked Server:

EXEC master.dbo.sp_addlinkedserver @server = N'VFP',
	@srvproduct=N'foxpro', @provider=N'VFPOLEDB',
	@datasrc=N'c:\MyFolder\', @provstr=N'VFPOLEDB.1'

GO

I tried to access the FoxPro files from the linked server but was getting the following error:

The OLE DB provider “VFPOLEDB” for linked server “VFP” reported an error. Access denied. Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB provider “VFPOLEDB” for linked server “VFP”. (Microsoft SQL Server, Error: 7399)

When the SQL Server service account run under NETWORK SERVICE or a DOMAIN Account, it didn’t work even though the domain account did have permissions to the FoxPro files. When I changed it to “Local System” it worked. To make it work with a domain account, I found the following answer in http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a525b569-77ca-41da-96e1-2ba1938dd32d

1. Start –> Run –> Dcomcnfg
2. Component services –> My computer –> DCOM config –> MSDAINITIALIZE
3. Right click on MSDAINITIALIZE –> properties –>security
4. Add the SQL Start-up account ( you may have to go to services in administrative tools under control panel to check log on account) under “launch and activation permission”, “Access permission” and “Configuration permission”.
5. Give full rights.

Environment:
SQL Server 2008 R2 Integration Services (32-bit)
Microsoft OLE DB Provider for Visual FoxPro
C:\Program Files\Common Files\System\Ole DB\VfpOleDB.dll 9.0.0.5815

I’m getting the following error when I click on “Columns” while editing OLE DB Source using “Microsoft OLE DB Provider for Visual FoxPro” named “Source – myTable”:

Warning:
Warning at {….} [Source – myTable]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the “DefaultCodePage” property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component’s locale ID will be used.
Fix:
Edit the properties of the OLE DB Source, [Source – myTable] and set property “AlwaysUseDefaultCodePage” to True.

This error happens because the server does not have a default code page for Visual FoxPro in “C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles”.

The following blog posting was very helpful: http://venkattechnicalblog.blogspot.com/2008/07/ssis-errors-part-4.html. Thank you.

I have encountered the following issue and reported it to Microsoft (Bug 697259 on connect.microsoft.ca). It’s marked as “Won’t Fix”, but in case you have the same issue, the workaround might come handy:

https://connect.microsoft.com/SQLServer/feedback/details/697259/ssis-2008-r2-ole-db-provider-for-visual-foxpro-ole-db-source-includes-deleted-records-even-if-deleted-true

Dev environment:
SQL Server 2008 R2 Integration Services (32-bit)
Microsoft OLE DB Provider for Visual FoxPro
C:\Program Files\Common Files\System\Ole DB\VfpOleDB.dll 9.0.0.5815

Bug:
When I create an SSIS package with “OLE DB Source” using “Microsoft OLE DB Provider for Visual FoxPro”, and for “Data access mode” use “Table or view” deleted records are included even if DELETED=True (means IGNORE DELETED=True).

Workaround:
For “Data access mode” use “SQL Command”, i.e. “select * from myTable”. Deleted records will be excluded.