SSIS

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.

%d bloggers like this: