none
The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

    Question

  • Hi,

    I am creating a SSIS package for which uses oracle provider for OLEDB.

    Where as it's throwing an error when testing the connection. Please find the below error..

    Test connection failed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

    SSIS is on server1 and Oracle is on Server2.

    Do u i need do anything with TNSNAMES.ORA file?? If yes, in which server??

    Thanks

    Bhanu

    Monday, November 26, 2018 11:58 AM

All replies

  • Hello,

    Have you installed ODAC on the server where SSIS is running and this in the same 32/64 bit achitecture as SSIS?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, November 26, 2018 12:11 PM
  • Have you installed Oracle client tools? (ODAC)

    You would need to install correct version of Oracle client tools and also make corresponding entry in tnsnames.ora file

    If you want to use this from SSDT then you need to install 32 bit version of the driver as SSDT is a 32 bit application

    see

    https://ayadshammout.com/2015/02/06/ssis-to-oracle-connection/

    https://jorgklein.com/2011/06/02/ssis-connect-to-oracle-on-a-64-bit-machine-updated-for-ssis-2008-r2/


    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

    Monday, November 26, 2018 12:18 PM
  • Thanks Visakh

    I had already installed ODAC tools that i got from oracle website. The file came with the name of :ODTwithODAC1120320_32bit.

    In tnsname.ora i had submitted following details.

    # ora_test =
    #  (DESCRIPTION =
    #    (ADDRESS = (PROTOCOL = TCP)(HOST = [Port_Number])(PORT = 1521))
    #    (CONNECT_DATA =
    #      (SERVER = DEDICATED)
    #      (SERVICE_NAME = ora_test)
    #    )
    #  )

    In OLEDB connection manager i had provided Server or file name as ora_test. And Log On greyed out as well.

    Did anything i have done wrong here.

    Thanks 

    Bhanu

    Monday, November 26, 2018 12:43 PM
  • Thanks Olaf 

    I had installed ODAC tools that i got from oracle website. The file came with the name of :ODTwithODAC1120320_32bit. 

    Thanks 

    Bhanu

    Monday, November 26, 2018 12:44 PM
  • ODTwithODAC1120320_32bit.


    That's the 32 bit OleDB provider and works with SSDT as 32 bit tool, but Job in 64 bit SSIS will fail; you have to enforce the execution as 32 bit in package/Job properties.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, November 26, 2018 12:54 PM
  • Why does it say [Port Number] under host?

    It should be your host name not port number


    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

    Monday, November 26, 2018 12:57 PM
  • Thanks Vishal

    sorry it's not Port Number it's IP address of Oracle Server. Ya i changed it to Host name..

    New error fallen in my bucket. Find below error

    TITLE: Connection Manager
    ------------------------------
    Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified.

    Thanks 

    Bhanu

    Monday, November 26, 2018 1:56 PM
  • Ok Sure Olaf , Thanks !

    Monday, November 26, 2018 1:57 PM
  • Hi,

    I am creating a SSIS package for which uses oracle provider for OLEDB.

    Where as it's throwing an error when testing the connection. Please find the below error..

    Test connection failed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine.

    SSIS is on server1 and Oracle is on Server2.

    Do u i need do anything with TNSNAMES.ORA file?? If yes, in which server??

    Thanks

    Bhanu

    I suggest you to try everything from CMD and move forward.

    Try tnsping and make sure you have a connection to oracle DB

    tnsping DBname

    If you have a oracle credential, try connect the database from CMD shell. 

    sqlplus username@dbname

    tnsping:

    C:\Users\username>tnsping DBname

    Used parameter files:

    F:\Oracel_32Bit\product\11.2.0\client_1\Network\Admin\sqlnet.ora

    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = sukgch0

    5) (Port = 1521)) (CONNECT_DATA = (SID = DBname)))

    OK (50 msec)



    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum

    Monday, November 26, 2018 3:06 PM
  • Thanks Vishal

    sorry it's not Port Number it's IP address of Oracle Server. Ya i changed it to Host name..

    New error fallen in my bucket. Find below error

    TITLE: Connection Manager
    ------------------------------
    Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified.

    Thanks 

    Bhanu

    It seems host name is still incorrect or you're not using correct reference name in your connection manager

    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

    Monday, November 26, 2018 4:23 PM
  • Hi Bhanu,

    Any update on this thread? 

    Have you successfully connect to ORACLE database?

    If you have any question, feel free to ask. 


    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

    Wednesday, November 28, 2018 9:56 AM