Problem running SSIS with ORACLE Data Source problem in Windows 64 bit

Question

I've managed to connect my SQL Server 2005 in Windows 64 bit server with ORACLE database. (Thanks to Mr. Jeyong Park :http://knol.google.com/k/jeyong-park/accessing-oracle-data-source-from-64bit/3vywlm4f31xae/12)

The problem is : In SSIS when I used Oracle as a OLE DB Data Source and previewed the data, it works, however, when I run the Package, the OLE DB Data Source task failed with the message :

*[OLE DB Source [10882]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.
The AcquireConnection method call to the connection manager "PROD_cm" failed with error code 0xC0202009.
There may be error messages posted before this with more information on why the AcquireConnection method call failed.*

Since I can preview the data, I think there's no problem with the connection.

Please help...

Answer 1

When I encountered this problem a year ago with SQL Server 2008 on Windows Server 2003 64-bit the issue was caused by incorrect drivers. What is the version of the Oracle database to which you are connecting? If you are connecting to a 8g or 9i Oracle server, then you need to use the 9i Oracle drivers. If you are connecting to a 10g or better server, then you can use the 11i drivers.

Have you tried running the SSIS package while logged into the server as the SQL Server Agent service account or have you only tested this on your workstation? If it works on your workstation and doesn't work while logged into the server as the service account, then this screams driver issue to me.

Answer 2

Another possible problem is that you tnsnames.ora file may not be correctly setup. The file contents should be something like the below code for the 9.2 drivers. Please note you need to replace , , and with appropriate values.

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.


<SERVER_NAME> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <IP_ADDRESS>)(PORT = <PORT_NUMBER>))
    )
    (CONNECT_DATA =
      (SID = <SERVER_NAME>
      (SERVER = DEDICATED)
    )
  )

Answer 3

One more possible solution. If you can get the linked server connection working and cannot get the data flow to work for some reason, then you could execute a linked server query to pull down the data. I remember doing this for about 6 months when we couldn't get the MYSQL drivers to work with the CTP of SQL Server 2008. Eventually we found the right solution, but this helped us through the short-run period to get up and running.