locked
Adding Oracle Data Source in SSIS - The given path's format is not support RRS feed

  • Question

  • Hi,

    I am trying to create new data source. I already tried these data sources

    Oracle Provider for OLE DB

    Oracle Client Data Provider

    Microsoft OLE DB Provider for Oracle.

    After configuring when i test the connection, it tells connection succeeded but if i click on then giving the error "The given path is not support".

    What should i do? Please help me in  this regard.

    Thanks in advance

    Dani

    • Moved by Robert Breitenhofer Thursday, November 24, 2011 3:42 PM English instead German (From:Für Entwickler: SQL Server)
    Monday, November 21, 2011 11:31 AM

Answers

All replies

  • Hi danimian,

    Please refer to the following related links:

    http://www.bidn.com/blogs/MMilligan/bidn-blog/1878/ssis-and-oracle-connections
    http://blogs.msdn.com/b/sqlperf/archive/2009/08/14/using-ssis-to-get-data-out-of-oracle-a-big-surprise.aspx

    And if the issue still persis, please supply more information about the issue, such as steps you have taken and configured and so on.

    Thanks,
    Eileen
    • Marked as answer by Eileen Zhao Thursday, December 1, 2011 8:51 AM
    Friday, November 25, 2011 7:55 AM
  • Hi Eileen,

    I would like to re-visit this issue because the links you have provided point at Microsoft Connector Version 2.0 for Oracle by Attunity. What danimian referred to was using the standard .Net OracleClient Data Provider.

    I have the same issue as him using Visual Studio 2010 Shell SP1Rel and trying to work on an SSIS package that connects to an Oracle database.

    That is how to re-produce the issue.  Note that the PORT used is different from the default ORACLE instance port (1521/1526).  I'm using 2500 for my example.

    Any suggestions here?  Because to overcome this, we create a new task "Set Variables" and dynamically hack the connection string to include the port, username and password for the connection in order for the SSIS package to execute correctly.


    Tomorrow is yesterday, where today are you and me?

    • Proposed as answer by Cayke Tuesday, June 9, 2015 2:42 PM
    Thursday, January 29, 2015 12:28 AM
  • Hi Eileen,

    I would like to re-visit this issue because the links you have provided point at Microsoft Connector Version 2.0 for Oracle by Attunity. What danimian referred to was using the standard .Net OracleClient Data Provider.

    I have the same issue as him using Visual Studio 2010 Shell SP1Rel and trying to work on an SSIS package that connects to an Oracle database.

    That is how to re-produce the issue.  Note that the PORT used is different from the default ORACLE instance port (1521/1526).  I'm using 2500 for my example.

    Any suggestions here?  Because to overcome this, we create a new task "Set Variables" and dynamically hack the connection string to include the port, username and password for the connection in order for the SSIS package to execute correctly.


    Tomorrow is yesterday, where today are you and me?

    Hi Ado,

    try use string to TNSNAME. This is similar the JDBC.

    For Exemple:

    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER.DOMAIN)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = INSTANCE)))

    

    • Edited by Cayke Tuesday, June 9, 2015 2:55 PM
    • Proposed as answer by Cayke Tuesday, June 9, 2015 6:58 PM
    Tuesday, June 9, 2015 2:52 PM
  • I encountered the same issue trying to connect oracle db using OLEDB. Here is the solution for those hasn't figured it out yet or this might be easy alternative for already figured out the different solution. This forum won't let me add screenshots until my account is verified. will add screenshots when I am allowed.

    1 - Error trying to save the connection because the Server name has ":" before port#.

    2 - Remove the ":" from Server name. And Click Ok. This will save the connection.

    3 - Go to the property of saved connection.

    4 - Now add ":" before port#. Bingo connection starts working.

    Hope this will save your time. I wasted lots of hours trying different solutions.

    • Proposed as answer by SQL Wayne Wednesday, November 11, 2015 12:29 PM
    Tuesday, August 11, 2015 7:14 PM
  • Worked for me, thanks!

    Thanks/Regards, Mike DePouw

    Thursday, October 22, 2015 4:23 PM
  • I tried this but it didn't work for me.

    VS 2008

    version 9.0.30729.4462 QFE


    Thanks/Regards, Mike DePouw

    Thursday, October 22, 2015 4:25 PM
  • Thank you rarediamond! It was incredibly difficult for me to find this solution.
    Wednesday, November 11, 2015 12:30 PM
  • Thank you, this worked for me too. 
    Thursday, November 19, 2015 8:52 PM
  • I tried this and I still get the same error: Path's format is not supported.

    I'm using the Native OLE DB\Oracle Provider for OLE DB.

    I've tried this:

    //SERVER_NAME:PORT/INSTANCE

    and

    SERVER_NAME:PORT/INSTANCE

    In both cases, if I test the connection it succeeds. But when trying to save and close the connection manager dialog box I get the "Path's format not supported" error.

    I'd rather not go through the hassle of dealing with TNS files, etc. This seems like it should be fairly straight forward!


    A. M. Robinson

    Wednesday, May 17, 2017 4:04 PM
  • Hi, thanks rarediamond, a subtle solution and certainly not one a lot would expect.   I would like to add my experience.   Was on windows 8 pro with local oracle 12c database and ssis 2010 from an ms sql 2012 install, a lot of this was 64 bit.  Oracle install was the full 12c db with ODTWithODAC12 installed on top of it.   I suspect anyone who has to specify the port number with their server will have this problem. I couldn't get SSIS OLEDB input to work but I could get ADO.NET to work.   I used localhost:1521/ORCL as my connect string and it "Tested" but gave "given path's format is not supported" when I tried to save.  (Hint to microsoft: don't successfully test a connection string and then tell people you can't save it. I understand you have xml parsing problems but then just fail the whole string up front.)  Shockingly enough rarediamond's suggestion works, just take the : out and it will let you save it.   I went back in with the "All" SSIS  on the oracle connection properties and edited the connection and put the : back in but found it was still occasionally saying it didn't like the format of the Oracle connection string. I also find if you change the connection string properties directly SSIS will sometimes blank the password without telling you.  So I saved and got out and went in with notepad against the Package file and edited back in the : with notepad and saved.   After that SSIS treated the Oracle connection string as gold and connected to the database.  So another approach for this.



    • Edited by etlman Friday, August 18, 2017 8:34 PM
    Thursday, August 17, 2017 9:30 PM
  • Ansonee,    Try the oracle provider on the ADO.NET input stage.   I prefer Oracle OLEDB also, but I couldn't get it to work with a OLE DB input stage with a :.   Don't use the slashes.  I've never seen SSIS listen to Active Directory syntax directly.  If you are getting a Success on Test Connection, you have the right server name, even it it won't save.   It's just when you try to save it, SSIS doesn't want to save the colon symbol (:).   So you need to take the colon out and put it back in.  Use the "All" connection string editor (click on the Oracle Connection at the bottom on the big Data Flow screen, not the OLEDB input and look at the properties to find the Connection String).  The connection string editor is the three dots off to the right of connection string box.  Or you can try the cheating suggestion I made above recently and just edit the Package file directly.  

    • Edited by etlman Friday, August 18, 2017 6:27 PM
    Thursday, August 17, 2017 10:07 PM
  • I am using VS 2013 and trying to connect oracle server (like \\SerevrName:portno\Instance) 

    I tried this thing but it does not work. 

    is there any concrete solution that can work ?? 


    Thursday, March 14, 2019 7:42 PM