locked
Connect Oracle using SSIS RRS feed

  • Question

  • Hi,

    Can anybody help here, we are trying to connect Oracle(11G) using SSIS(sql server 2012, VS 2010) but getting below error while configuring OLED Oracle Connection:

    Test connection failed because of an error in initializing provider. Oracle error ocurred, but error message could not be retrieved from Oracle.


    Regards, Randhir

    Thursday, May 16, 2013 9:43 AM

Answers

  • Hi Randhir,

    Did you follow these steps:

    1. Install 32 bit Oracle clients.
    2. Paste the tnsnames.ora file to the following path ....ORACLE_HOME/Network/admin
    3. As Shanky, provided the link, http://kb.tableausoftware.com/articles/knowledgebase/oracle-connection , follow it create system env variable to specify the path of the tnsnames.ora file
    4. Under ODBC in administrative tools, make a system DSN for Microsoft ODBC for Oracle set up.
    5. Server name=hostname:PortNumber/ServiceName
    6. Drag a Ado.net source and choose .net provider\ ODBC data provider.


    Thanks, hsbal

    • Proposed as answer by Mike Yin Tuesday, May 21, 2013 8:47 AM
    • Marked as answer by Mike Yin Sunday, May 26, 2013 3:53 PM
    Friday, May 17, 2013 8:50 PM

All replies

  • Hi Randhir,

    Have you installed the Oracle client and networking compnents? Check with your DBA


    Nitesh Rai- Please mark the post as answered if it answers your question

    Thursday, May 16, 2013 9:46 AM
  • Yes we have Oracle client . Can you please what Networking components we requires.

    Regards, Randhir

    Thursday, May 16, 2013 9:58 AM
  • After you have installed Oracle client you are getting this error because you have not configured ORACLE_HOME,tnsnames.ora parameter

    Entry of your destination server has to be made in tnsnames.ora file which is located at ORACLE_HOME/Network/admin( u have to set oracle_HOME first)

    Take help of oracle dba...or please post ORACLE_HOME destination here.

    Are you trying to use OLEDB provider or ODBC provider ...

    Below link hepls setting tns entry on win 7 ..proc is almost same for win server

    http://kb.tableausoftware.com/articles/knowledgebase/oracle-connection


    Soldier..Sir we are surrounded from all sides by enemy.. Major: Good, we can attack in any direction Thats attitude.. Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, May 16, 2013 11:18 AM
  • Hi Randhir,

    Did you install the 32bit Oracle client? You will need the 32bit client to Test Connection from Visual Studio.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

    Friday, May 17, 2013 5:58 PM
  • Hi Randhir,

    Did you follow these steps:

    1. Install 32 bit Oracle clients.
    2. Paste the tnsnames.ora file to the following path ....ORACLE_HOME/Network/admin
    3. As Shanky, provided the link, http://kb.tableausoftware.com/articles/knowledgebase/oracle-connection , follow it create system env variable to specify the path of the tnsnames.ora file
    4. Under ODBC in administrative tools, make a system DSN for Microsoft ODBC for Oracle set up.
    5. Server name=hostname:PortNumber/ServiceName
    6. Drag a Ado.net source and choose .net provider\ ODBC data provider.


    Thanks, hsbal

    • Proposed as answer by Mike Yin Tuesday, May 21, 2013 8:47 AM
    • Marked as answer by Mike Yin Sunday, May 26, 2013 3:53 PM
    Friday, May 17, 2013 8:50 PM