none
SSIS packages and Oracle TNSNames

    Question

  • Hi,

    I've question about SQL SERVER AND Oracle. Situation is that there is a terminal server and a SQL Server Server. I can't access both OS, unfortunately and I'm currently pingpong with the servicedesk about an issue of connecting SQL Server to Oracle

    I've SSIS package with a connection to Oracle. If I execute it on a Terminal server data is transferred from Oracle to the SQL database. The connection is created with "NATIVE OLEDB/ORACLE PROVIDER FOR OLEDB as the OLEDB provider.

    The oracle client is installed both on the Terminal server as on the "SQL Server" Server (I assume because I can't check this). If I execute a SSIS package on the terminal server it works and when I import it on the SQL Server Integration Services (Import Package and run package) it works too!!

    Now the question: Does this mean that the tnsnames.ora is correctly installed and configured on both servers?

    Greetz,

    Hennie

    Thursday, July 11, 2013 8:44 AM

Answers

  • When you connect to SSIS to Server B (SQL Server) from Server A (using management studio) and execute a package deployed in MSDB, the package is downloaded and run locally o n Server A. Only if it is a scheduled SQL Server job, then it is run on Server B even if you invoke it from Server A using management studio.

    As far as the error you are getting, is there a way you can try to connect to Oracle outside SSIS using any standard Oracle tool or anything else (like a .udl file)?

    DebarchanS - MSFT

    Tuesday, July 30, 2013 3:23 PM

All replies

  • More details will help.

    Can you explain with Server A, B?


    Rajkumar

    Thursday, July 11, 2013 9:16 AM
  • How do you execute the packages exactly? This might be important.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 11, 2013 9:17 AM
  • Hi,

    Terminal Server = A (BIDS)

    SQL Server Server = B (databases)

    Oracle = C

    If I execute the package in BIDS on A it will run.

    If I execute the package (run package) in Integration Services under stored packages on B it will run

    Servicedesk states that the tnsnames.ora is not installed in oracleclientdirectory(????) on B

    How come that it's working?????

    ps. There are two installations on the server:

    C:\app\product\11.2.0\client_1
    C:\app\product\11.2.0\client_2

    not sure why? may be because of the 32bit/64bit problem??

    Greetz,

    Hennie



    • Edited by Hennie7863 Thursday, July 11, 2013 9:31 AM name
    Thursday, July 11, 2013 9:22 AM
  • Do you actually log in onto the servers to execute the packages? (I think you do for server A).

    If you go with SSMS to Integration Services on your computer and you start the package, the package is executed on your computer and not on the server.

    Where is my SSIS package executed?

    Maybe the TNS names are configured through a shared drive instead of a local file?


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 11, 2013 9:36 AM
  • From the SQL Server Management Studio on server A I open a connection to server B and on that server I execute the SSIS package (and not on Server A, the terminal server). That means it's executed on server B, in my opinion...(?)

    Can you explain "Maybe the TNS names are configured through a shared drive instead of a local file?"

    I always thought that TNSNAmes.ora should be installed in the same dirctory as oracle client? Do you mean there is also a shared folder where oracle client is installed?

    Do you agree that if I can execute a package on the server B with Integration services there should be a tnsnames.ora installed????

    Gr,

    Hennie



    • Edited by Hennie7863 Thursday, July 11, 2013 10:20 AM extra
    Thursday, July 11, 2013 10:17 AM
  • If you use SSMS on server A and you connect to SSIS on Server B to execute a package, the package will be executed on Server A.

    You need to physically log into server B (using remote desktop) to start the package.

    Regarding the shared drive: not sure how it works (I don't know much about Oracle), but at my current project the TNSNAMES.ORA are not stored on every local PC, but they are stored in a network location.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Thursday, July 11, 2013 10:41 AM
  • Yep that's the problem...

    ORA-12154: TNS:could not resolve the connect identifier specified..

    It 's using the tnsnames.ora of the terminal server (A) and not server B. Isn't this strange?

    Now I need to solve this problem;-)

    Gr,

    Hennie

    Thursday, July 11, 2013 10:57 AM
  • Is it possible to create a connection to oracle without the file TNSNAMES?

    Something like TNSLESS connections???

    Thursday, July 11, 2013 12:39 PM
  • Hi Hennie,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Thanks,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Thursday, July 18, 2013 10:09 AM
  • Any Luck?

    Currently into a change procedure for placing the tnsNames.ora file on the server and it's taking an endless time to get it done (sigh).

    The next option what I'm trying to do is creating a TNS-Less connectionstring with SQL Server but there is very less information available on the internet and I haven't figured it out how to do it....

    On Connectionstrings.com there is an example for a tns-less connectionstring.

    Gr,

    Hennie

    Friday, July 26, 2013 12:03 PM
  • It seems that the tnsless conectionstring is working on the terminal server (server A), but not on the server B. I'm recieving the following error: ORA-12537: "TNS:connection closed" with the tnsnames.ora connection option as well as with the tnsless connection string. The administrator told me that the former file content of the tnsnames.ora didn't work and he copied the content into the file again. He said that it's now possible to tnsping the Oracle server. Not sure whether he pinged the right server because there are four connectionstrings in the file. I have to ask him about this..

     

    So if he is able to ping the server and I'm still recieving the error: "ORA-12537: TNS:connection closed" what could be problem? Firewall? rights?

     

    Thnx for your time and efforts....

     

    Greetz,

     

    Hennie

    Tuesday, July 30, 2013 7:36 AM
  • Any Luck?

    Currently into a change procedure for placing the tnsNames.ora file on the server and it's taking an endless time to get it done (sigh).

    The next option what I'm trying to do is creating a TNS-Less connectionstring with SQL Server but there is very less information available on the internet and I haven't figured it out how to do it....

    On Connectionstrings.com there is an example for a tns-less connectionstring.

    Gr,

    Hennie

    Hello Hennie,

    it is possible to set TNSNAMES.ORA file at shared location may be on NAS and all server which require access to  this file can have access by

    1.Giving the full rights on this NAS

    2.In each server you have to physically set you TNS parameter and that is done by  RK on MyComputer...properties...On left hand side select last option 'Advanced system setting'....and then select advanced tab ..then click environment variables..see this link for more details

    3. To identify your TNS location go to CMD and type TNSPING SERVERNAME--this servername is name of any server in TNSNMAES.ORA file when it will connect it will give you location from which it is picking tns file.Run this command on all machines to see location of TNS.Also after you put your TNS on NAS this will confirm u location

    Try this and please revert


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, July 30, 2013 10:45 AM
  • Sorry, perhaps I forgot to mention this or did not explain myself clearly. I don't have access to the OS, i don''t have access to a NAS, I can't acces the server and the Terminal server (on OS Level) and therefore I can't copy the TNSNAMES.ORA to another (shared) location. My options are very limited.

    Because of my limited options I can experiment things only on the SQL server (A & B).

    I opened this thread for speeding up the communication with the Service Provider and provide them with certain suggestions....

    The current situation is:

    • TNSNAMES.ORA and TNSLESS connection is working on the Terminal Server (A) in SSIS
    • TNSNAMES.ORA and TNSLESS connection is NOT working on SQL Server (B) in SSIS
    • I'm recieving error :  "ORA-12537: TNS:connection closed"
    • The administrator says that he is able to TNSPING to a certain server (1 out of 4) and therefore the TNSNAMES.ORA seems to be working....

    What  are your ideas about what is causing this problem?

    Greetz,

    Hennie de Nooijer


    • Edited by Hennie7863 Tuesday, July 30, 2013 12:17 PM adding
    Tuesday, July 30, 2013 12:16 PM
  • Sorry, perhaps I forgot to mention this or did not explain myself clearly. I don't have access to the OS, i don''t have access to a NAS, I can't acces the server and the Terminal server (on OS Level) and therefore I can't copy the TNSNAMES.ORA to another (shared) location. My options are very limited.

    • TNSNAMES.ORA and TNSLESS connection is working on the Terminal Server (A) in SSIS
    • TNSNAMES.ORA and TNSLESS connection is NOT working on SQL Server (B) in SSIS
    • I'm recieving error :  "ORA-12537: TNS:connection closed"
    • The administrator says that he is able to TNSPING to a certain server (1 out of 4) and therefore the TNSNAMES.ORA seems to be working....


    "ORA-12537: TNS:connection closed"..this sometimes means firewall issues/parameter not set properly/ ect see this.Hope u have taken care of every thing

    can u ping me result of TNSPING SERVERNAME for both A and B, are you doing TNSPING to same server from both A and B


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Tuesday, July 30, 2013 12:32 PM
  • When you connect to SSIS to Server B (SQL Server) from Server A (using management studio) and execute a package deployed in MSDB, the package is downloaded and run locally o n Server A. Only if it is a scheduled SQL Server job, then it is run on Server B even if you invoke it from Server A using management studio.

    As far as the error you are getting, is there a way you can try to connect to Oracle outside SSIS using any standard Oracle tool or anything else (like a .udl file)?

    DebarchanS - MSFT

    Tuesday, July 30, 2013 3:23 PM