How to deal with an Oracle data source in SSIS?

Question

I know, this is a frequently asked question, but I'm not able to solve my problem about how to use an Oracle database in SSIS.

Oracle data source specs

Configuration

• I installed ODTWithODAC121021 & ODAC121021_x64
• I defined tnsNames.ora to C:...\client_1\network\admin

LANDESK_PP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PPRQU)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydomain.com)
    )
  )

Usage

If I use SQL Developer, the connection works properly, I can execute some queries. Moreover, If I create an Oracle Provider for OLEDBconnection manager, I can use the preview button too, but the package execution doesnt't work.

Now, If I run the package (in 32bit or 64bit debug mode), an error occured because the connection cannot be acquired.

Here is the error :

Error: 0xC020801C at Load LANDESK data, Load LANDESK [108]: Error code SSIS DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Landesk" failed.  

Any idea to solve this issue ?

Answer 1

You have run into the 64-bit vs 32-bit driver mess that Microsoft kindly created when they built Windows 64-bit. Personally I have given up on trying to solve this - I only install 32-bit drivers as the lowest common denominator.

So I would completely uninstall Oracle and reinstall 32-bit drivers only.

Visual Studio and SSMS are 32-bit so they will "see" 32-bit drivers. For 32-bit debug execution in Visual Studio, go to the Project Properties / Debugging and set Run64BitRuntime to False.