Error occur during Oracle and Sql Server Integration

Posted on

Question :

I have developed a SQL Server Integration Services package pulling data from Oracle 11g and storing in a SQL Server 2008 database. Everything works as expected, but when I schedule a sqlserver job to run the SSIS package it returns the following error:

Error: System.InvalidOperationException: Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed. —> System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) at System.Data.Common.UnsafeNativeMethods.OCILobCopy2(IntPtr svchp, IntPtr errhp, IntPtr dst_locp, IntPtr src_locp, UInt64 amount, UInt64 dst_offset, UInt64 src_offset) at System.Data.OracleClient.OCI.DetermineClientVersion() — End of inner exception stack trace — at System.Data.OracleClient.OCI.DetermineClientVersion() at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName) at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions) at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OracleClient.OracleConnection.Open() at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)

Please note this error occurs only while running the package as a sqlserver job. If I run the package via Integrated Services in SQL Server Management Studio then I don’t get any error.

I have even installed both 64-bit and 34-bit Oracle driver but no luck 🙁

Please provide inputs.

Answer :

Solved the question.Select the check box ‘use 32 bit runtime‘ in Execution options tab as shown below

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *