locked
Problem running SSIS package with connection to Oracle DB RRS feed

  • Question

  • Hello specialists, good afternoon.

    I have an issue that i would hope to see solved. I hope you are able to lay a hand.

    I have been migrating a solution from sql server 2005 platform to sql 2008, with the respective BIDS version. Now my problem is that i have a connection to an Oracle database, when i select the respective Oracle db provider for the connection, when i test the connection i receive the following error:

    ORA-12541:TNS: no listener

    Now in order to avoid this i have installed in the new server the Oracle insta client, and i have isntalled the sqldeveloper in the hopes that they would have any drivers that i could need. I tried to copy the TNSNAMES file from the previous server to the new one but i don't have the ORACLE directory where we usually put the TNSNAMES.ORA file in this server, i created it, as well as the respective ORACLE BASE DIRECTORY and defined all the necessary variables for all the above software to work.

    I still can't figure out why the connection is not working.

    Anyone has any clue?

    Thank you for your help in advance.

    • Edited by Goncalo M Tuesday, March 27, 2012 3:35 PM
    Tuesday, March 27, 2012 3:34 PM

Answers

  • Hello, thank you all for your effort.

    I managed to solve the issue.

    The problem was that my "path" variable had specified in it an older directory for oracle, so the tnsnames and listener it was taking into account was the one in that directory. I changed that and it now worked just fine.

    Thank you everyone.

    • Marked as answer by Goncalo M Thursday, March 29, 2012 2:36 PM
    Thursday, March 29, 2012 2:36 PM

All replies

  • Hello Goncalo,

    Could be a firewall, or a misconfiguration.

    Please see if the steps in http://www.dba-oracle.com/t_ora_12541_tns_no_listener.htm help.


    Arthur My Blog

    Tuesday, March 27, 2012 3:46 PM
  • Hey Arthur, thank you for your reply.

    I've checked the link and unfortunatelly i tried the proposed solutions to no avail.

    The listener is up and running.

    I was wondering, what type of Oracle client do you have to install in roder to have the db provider working, and how the TNSNAMES file should be deployed, how does the SSIS works when he tries to reach the ORacle DB, from what i found out i would only need to have an ORACLE software installed, but when i checked the TNSNAMES setup documentation most of them talk about a %ORACLE HOME%/network/admin when i installed the insta client from ORACLE and the SQLDEVELOPER it created no such directory...

    The %ORACLE HOME% and all other variables have been properly defined, and the changes in the registry have been made for the sqldeveloper installation.

    Could it be that the insta client and the sqldeveloper don't have what i need to ru nthe ORACLE provider in SSIS? Anyone has experience with this type of connections?

    Thanks in advance.

    Wednesday, March 28, 2012 8:59 AM
  • hey i did nt get with  your comments

    "I tried to copy the TNSNAMES file from the previous server to the new one but i don't have the ORACLE directory where we usually put the TNSNAMES.ORA file in this server"

    basically when u install an oracle client u will have by default a directory which will have  tns names where u will paste the tns info of your oracle . so its not something that you can create on your own .it will come with the default installation of your oracle client

    when  u will install the correct client  u will have an directory created for oracle. i have an oracle 10 g client installed in my c drive and under admin folder there is file tnsnames.ora this is where u will enter your tns details.

    C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

    what is your oracle db (10g or 11g) install the client accordingly and then try.

    Wednesday, March 28, 2012 9:48 AM
  • Thank you for the reply Nishink,

    I did what you suggested, after a few twinking with the client i installed it, version 10g, same as the DB.

    Now i'm no longer receiving the same error, this one is way more obfuscated...

    If anyone has ever got this one and knows how to solve it, or has any clue about it, please feel free to say how :)

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

    Unfortunatelly it doesn't help much.

    Thank you for the help so far.

    Wednesday, March 28, 2012 3:21 PM
  • Use this KB to remedy: http://support.microsoft.com/kb/834305 (even though it is SSRS related, it must be still applicable to your case).

    Arthur My Blog

    • Proposed as answer by Eileen Zhao Thursday, March 29, 2012 8:52 AM
    Wednesday, March 28, 2012 3:24 PM
  • i think you have to reinstall the provider for oracle u are having .Even the link from ARTHURZ states the same

    check what is your provider for ssis . u can check it at the following location

    go  to new connection in ssis---> in  provider drop down  microsoft oledb provider for oracle --->click all

    check the following location if you get provider for 10g

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/52871f80-02da-4823-85dc-8f104bbe932f/


    check the following link and try if you can get the oracle provider

    http://www.oracle.com/technetwork/database/windows/utilsoft-088126.html
    • Edited by Nishink Thursday, March 29, 2012 9:15 AM pasting the link for the oracle provider
    Thursday, March 29, 2012 7:28 AM
  • Hello, thank you all for your effort.

    I managed to solve the issue.

    The problem was that my "path" variable had specified in it an older directory for oracle, so the tnsnames and listener it was taking into account was the one in that directory. I changed that and it now worked just fine.

    Thank you everyone.

    • Marked as answer by Goncalo M Thursday, March 29, 2012 2:36 PM
    Thursday, March 29, 2012 2:36 PM