SQL Agent Job – SSIS Integration package which uses VFPOLEDB and SQL 2012 fails

Various error messages received including:

Description: ADO NET Source has failed to acquire the connection DataReaderSrc with the following error message: “ERROR [IM014] [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”

Solution:

  1. Create a SQL Credential
    1. SSMS > Security > Credentials
  2. Create a SQL Server Agent Proxy
    1. SSMS > SQL Server Agent > Proxies > SSIS Package Execution > New Proxy
  3. Return to job step in question > Run as > new proxy
  4. Execution options > Use 32-bit tick
Advertisements

Workaround for SQL Server 2012 64-bit connection to 32-bit VFPOLEDB – Stored Procedure and SQL Agent

In SQL Server 2005 32-bit > Linked Servers > Providers > VFPOLEDB was present, (with in process ticked)
This provider was not available in SQL Server 2012 64-bit

Workaround: Running a stored procedure from SQL 2012 back to to SQL 2005

  1. Create a linked server to the SQL Server 2005 32-bit. RPC = Remote Procedure Call. You need to set RPC Out to True. Possibly RPC as well.
  2. Then use/move/write queries in SQL 2005 and call them from SQL 2012 procedures using:
  3. EXECUTE [<SQL2005 server name>].[Database name].[Schema].[procedure]
  4. May need to add login to SQL 2005 and grant permission

Workaround: SQL Agent jobs that call procedures still not working

Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot initialize the data source object of OLE DB provider “VFPOLEDB” for linked server “”. [SQLSTATE 42000] (Error 7303)  OLE DB provider “VFPOLEDB” for linked server “” returned message “Invalid path or file name.”. [SQLSTATE 01000] (Error 7412).  The step failed.

  1. Change the job type from Transact SQL to Operating System (CmdExec)
  2. Command = osql -E -Q “EXEC <sql 2012 db name>.dbo.<sp name>”