BIDS: The given path’s format is not supported

Yesterday I came across an interesting error, that I have not yet found an explanation or solution for. Although I found a workaround. But first things first: the error.

For a new project I need to load data from multiple sources (flat files, SQL Server, Excel) into an Oracle database. I decided to use SSIS as a tool to achieve this, and which is made for exactly projects like this.

I downloaded the newest Oracle OLEDB provider from the Oracle homepage, installed it and tested the connection to my Oracle target database. Worked fine.

I then started to create my SSIS package in Visual Studio BIDS, created my data sources and created the connection to the target database.

I clicked Test Connection and as expected the result was positive.

I expected to get on with my work and clicked OK. How wrong you sometimes can be… because what happened was an error message as cryptic as can be.

Everything I found on the internet was either unsolved, had to do with file paths in ASP.NET or was solved by reinstalling the machine. That was not very helpful. The most annoying thing was, that the connection tested ok, but I couldn’t store the connection because of the error. And if I can’t create and store the connection, the SSIS is useless to me for this project. What to do? Reinstall the servers? Forget SSIS and use another architecture? No time for that.

I still don’t know the cause of the error or what it even means in this context. But I found a workaround.

Workaround

To check if there was something wrong with my connection string I opened SSMS and used the data import and export wizard to connect to the Oracle database with the same connection string and imported some tables into a test database. That worked without any problems.

Next I used the data import and export wizard again to export data to my Oracle database and this time I stored the generated package.

I opened the package in Visual Studio, removed everything except the Oracle destination connection, and behold! it worked just fine.

If you now think, the problem is solved: it is not. When I open the connection properties in Visual Studio and try to save them again, I still get that damn error.

So it works for me for this project. But before I start the next one I surely want to know what went wrong here.

8 Responses to BIDS: The given path’s format is not supported

  • Jakub says:

    I had the same problem. Here’s what I did to fix it.
    1) for connection, in field ‘Server name’ placed: my_host_name/database_service (instead of my_host_name:1521/database_service_name, as was shown in example above)
    2) turned of 64-bit runtime mode: right click on project (in Solution Explorer) -> Properties -> Debugging -> Run64BitRuntime = false.
    That’s it. After this changes, I can run package without problem.

  • blogbyrajesh says:

    Jakub’s reply works for me too! Need to try when the port number is different

  • Nicholas says:

    For all of my other connections that use the default port of 1521, host_name/database_name works just fine. The connection I’m trying to get to work out now is using a different port number. What a pain!

  • DM Cochran says:

    I know it is an old thread, but you can also add the connection in Net Manager and then refer to the alias.

    • xnnc says:

      In my case the port was non standard, I had to keep it, that’s why I set an ODBC Driver and refered to the alias, it worked perfectly.

  • Jim says:

    Yes, this is an old thread, but this is what finally worked for me: http://www.atawasol.com/ssis-f14/topic211.html
    Wanted to share in case anyone else comes across this looking for an answer for this obscure error.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: