locked
Error extracting oracle data RRS feed

  • Question

  • Hi,

    We are using SSIS 2016 to fetch oracle data through OLEDB source. We performed below actions to extract the data

    1. Installed Oracle 11g client 64 bit (machine is 64 bit)
    2. Configured tnsnames.ora file with the oracle server connection details at path "C:\Oracle\product\11.2.0\client_1\Network\Admin"
    3. Created one environment variable TNS_ADMIN with the value "C:\Oracle\product\11.2.0\client_1\Network\Admin"
    4. Restarted the machine

    but still we are getting the error below

    we tried to resolve it by setting project property Run64BitRuntime to false without luck.


    Cheers,

    Wednesday, March 28, 2018 5:59 AM

Answers

  • Hello,

    I guess you get the error in SSDT and not during runtime of a SSIS package? SSDT is a 32 bit tool (no 64 bit available), so you have to install 32 bit ODAC, too.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Visakh16MVP Wednesday, March 28, 2018 6:18 AM
    • Marked as answer by Amit-Tomar Thursday, March 29, 2018 12:55 AM
    Wednesday, March 28, 2018 6:13 AM
  • Hi Amit-Tomar,

    You need to install 32 bit Oracle client and 32 bit ODAC, even if you have a 64-bit machine.

    Then you should be able to use Oracle Provider for OLE DB or Microsoft OLE DB  Provider for ORACLE.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Amit-Tomar Thursday, March 29, 2018 12:55 AM
    Wednesday, March 28, 2018 7:10 AM

All replies

  • Hello,

    I guess you get the error in SSDT and not during runtime of a SSIS package? SSDT is a 32 bit tool (no 64 bit available), so you have to install 32 bit ODAC, too.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Visakh16MVP Wednesday, March 28, 2018 6:18 AM
    • Marked as answer by Amit-Tomar Thursday, March 29, 2018 12:55 AM
    Wednesday, March 28, 2018 6:13 AM
  • Hi Amit-Tomar,

    You need to install 32 bit Oracle client and 32 bit ODAC, even if you have a 64-bit machine.

    Then you should be able to use Oracle Provider for OLE DB or Microsoft OLE DB  Provider for ORACLE.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Amit-Tomar Thursday, March 29, 2018 12:55 AM
    Wednesday, March 28, 2018 7:10 AM
  • Hi Olaf,Pirlo,

    Thanks for your answers, it work on my machine after installing 32 bit client. The same way i installed 32 bit client on a newly built SSIS server but the same package failed there with below error 

    

    Does it mean that i need to install both the drivers 32 bit & 64 bit on this new server, if so then which tnnames.ora file path i need to pick in the environment variable 64 bit one or 32 bit.


    Cheers,

    Thursday, March 29, 2018 2:14 AM
  • MSDAORA is the "Microsoft Data Provider for Oracle"; tath one is deprecated since a long period and works only with Oracle up to Version 8; don't use that one.

    Always use origin Oracle OleDB data Provider, which comes with ODAC, what you already have installed.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, March 29, 2018 5:33 AM
  • Hi Olaf,Pirlo,

    Thanks for your answers, it work on my machine after installing 32 bit client. The same way i installed 32 bit client on a newly built SSIS server but the same package failed there with below error 

    

    Does it mean that i need to install both the drivers 32 bit & 64 bit on this new server, if so then which tnnames.ora file path i need to pick in the environment variable 64 bit one or 32 bit.


    Cheers,

    yes

    if server os is 64 bit you've to install both if you want it to be run in 64 bit environment from the server

    If not, just install 32 bit and set Use 32 bit runtime property when configuring to run from the server


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, March 29, 2018 5:46 AM
  • Hi Olaf,

    Thanks for your reply. 

    In fact with the same Oracle client i am able to connect successfully from my machine and package ran successfully as well, after that i installed it on the server.

    

    Moreover i have installed Oracle Provider for OLE DB Release 11.2.0.3.20 from Oracle which has ODAC (image below) and I hope that is ok.



    Cheers,


    • Edited by Amit-Tomar Thursday, March 29, 2018 6:04 AM
    Thursday, March 29, 2018 6:04 AM