Oracle connection failure

Question

Hi,

am trying to load a oracle table into sql server.

I created a connection manager for oracle and test connection succeded.but when i use that conn mgr in my execute sql task, its throwing me err

[Oracle Source [1]] Error: The AcquireConnection method call to the connection manager XXXX failed with error code 0x80004005.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The connection manager type is MSORA

Can you pls help me on this

Answer 1

Check for the TNS and Listner files, have u set them properly?

Answer 2

How can i check them ? i did not touch any of these files

Answer 3

Is it 64 bit Oracle or 32 bit ?

Did you try changing the package debugging property "Run64BitRuntime" property to "False".

---

Regards, RSingh

Answer 4

its a 64 bit.so am running pacakge under 64 bit only.

and i see my server name in the TNS file as well

Answer 5

Hi,

am trying to load a oracle table into sql server.

I created a connection manager for oracle and test connection succeded.but when i use that conn mgr in my execute sql task, its throwing me err

[Oracle Source [1]] Error: The AcquireConnection method call to the connection manager XXXX failed with error code 0x80004005.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The connection manager type is MSORA

Can you pls help me on this

Hi SSDL,

I think you can create a linked server to connect to Oracle DB, for copying the table. Read

http://sqlserverplanet.com/dba/how-to-add-a-linked-server

---

Many Thanks & Best Regards, Hua Min

Answer 6

yes, but currently i have an ssis pacakge existing already..am adding new oracle table to it...

This pacakge is running fine in prod, i just opened it in dev and i got this error..not sure if the dev has enough drivers or not...

Answer 7

Post the script used in the script task.

---

Regards, RSingh

Answer 8

its just a DFT, am coeencting to source and getting the counts into a variable.thats it

Answer 9

My query is how do you get count into the variable ??

Regards, RSingh

Answer 10

am using a Dataflowtask...in that i used a ole db source and selected my source table and used a row count task.

Answer 11

Can you make sure that Execute Task properties are set similar to the one in this link ?http://www.mssqltips.com/sqlservertip/2724/run-an-oracle-package-from-sql-server-integration-services/

You can get the count of the row in SQL Execute task itself using ssis variable. Refer this link http://www.bidn.com/blogs/kylewalker/ssis/421/setting-package-variables-using-execute-sql-tasks

---

Regards, RSingh

Answer 12

its a 64 bit.so am running pacakge under 64 bit only.

and i see my server name in the TNS file as well

Could you confirm what version of Oracle drivers you have installed on the DEV box?? BIDS would need 32 bot drivers whereas runtime would need 64 bit if you dont select the use32bitruntime while scheduling the job

In essence you would require both(64 and 32 bit) the drivers in your case it seems

---

Abhinav http://bishtabhinav.wordpress.com/