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

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...

---

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.

---

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)
    )
  )

---

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.

---

One of The following should work:

1. Check the tnsnames.ora file - It should have the connection details to the Oracle DB hat you are trying to connect through your package.

2. Check if the Oracle DB you are trying to connect to is working. I wasted around two days finding a root cause of the error and later came to know that the Oracle DB was shut down.

3. Check that the passowrds are intact in the configuration manager/Connection string.

4. If you have been working for long on the package try closing and opening it again. The