locked
Oracle connection failure RRS feed

  • 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


    • Edited by SSDL Tuesday, March 25, 2014 6:24 AM
    • Changed type SathyanarrayananS Tuesday, March 25, 2014 6:37 AM Question
    Tuesday, March 25, 2014 6:19 AM

Answers

  • 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

    • Edited by Ch. Rajen Singh Thursday, March 27, 2014 5:08 PM
    • Proposed as answer by Mike Yin Monday, March 31, 2014 1:23 PM
    • Marked as answer by Mike Yin Sunday, April 6, 2014 4:22 PM
    Thursday, March 27, 2014 5:00 PM
  • 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/

    • Proposed as answer by Mike Yin Monday, March 31, 2014 1:23 PM
    • Marked as answer by Mike Yin Sunday, April 6, 2014 4:22 PM
    Thursday, March 27, 2014 7:06 PM

All replies

  • Check for the TNS and Listner files, have u set them properly?
    Tuesday, March 25, 2014 6:34 AM
  • How can i check them ? i did not touch any of these files

    Tuesday, March 25, 2014 6:39 AM
  • Is it 64 bit Oracle or 32 bit ?

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


    Regards, RSingh

    Tuesday, March 25, 2014 7:04 AM
  • its a 64 bit.so am running pacakge under 64 bit only.

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

    Tuesday, March 25, 2014 7:09 AM
  • 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

    Tuesday, March 25, 2014 7:09 AM
  • 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...

    Tuesday, March 25, 2014 7:13 AM
  • Post the script used in the script task.

    Regards, RSingh

    Tuesday, March 25, 2014 7:20 AM
  • its just a DFT, am coeencting to source and getting the counts into a variable.thats it
    Tuesday, March 25, 2014 8:14 AM
  • My query is how do you get count into the variable ??

    Regards, RSingh

    Tuesday, March 25, 2014 9:09 AM
  • am using a Dataflowtask...in that i used a ole db source and selected my source table and used a row count task.
    Tuesday, March 25, 2014 9:54 AM
  • 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

    • Edited by Ch. Rajen Singh Thursday, March 27, 2014 5:08 PM
    • Proposed as answer by Mike Yin Monday, March 31, 2014 1:23 PM
    • Marked as answer by Mike Yin Sunday, April 6, 2014 4:22 PM
    Thursday, March 27, 2014 5:00 PM
  • 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/

    • Proposed as answer by Mike Yin Monday, March 31, 2014 1:23 PM
    • Marked as answer by Mike Yin Sunday, April 6, 2014 4:22 PM
    Thursday, March 27, 2014 7:06 PM