photo by congerdesign

It is not uncommon for data warehousing to get the data from any possible source the organisation has. ETL developers like myself are enabling data collection from different database types, files and APIs. Recently I had to deal with something I haven’t came across before, OData – Open Data Protocol. I was pleased to find that as this protocol was initially developed by Microsoft, and it is nicely integrated with SSIS. It means that setting up a new OData data source is as simple as drag and drop. However, this was done long before my time, worked faultlessly for ages, so not so many memories about setting this thing up have been preserved.

OData task in SSIS

On my project we were migrating on-premise data warehouse to the cloud server. Once the perfectly working SSIS package had been copied to the shiny new server, I got the following error message when I tried to run it: “Cannot acquire a managed connection from the run-time connection manager.“ The first result in Google suggested me to check the type of OData connector. Turned out we didn’t have any installed on the new server; quite silly, but as I said this was my first time with OData. However, after the right version had been installed, I got the same error message as before. This time I was anticipating it not working; Windows authentication is not supposed to be used anymore as the server has moved from the local network, but I was hoping to see the error message telling me that.
Long story short, it took me some time to find the right person to help, as the other side of the connection was a complete unknown to me. First I had to figure out that the connection name Nav was not a random name given to server, but rather a Microsoft Dynamics Nav (Navision) product instance. This information let me stop bothering the system admins, but rather approach Nav support, who have created a user with a basic authentication for me. After a couple iterations (there are probably even less Nav specialist than OData experts, but we got there eventually) during which I could still see the same error message as before, “Cannot acquire a managed connection from the run-time connection manager“, it has started working in Visual Studio, but not on the server. Another silly thing from me, the SSIS package wasn’t set up to save the sensitive information. It was a big relieve to see the package finally running on server in Azure after I have provided it with the password.
Altogether it left me with mixed feelings. Microsoft has made it very simple to set up, there is pretty much nothing to be broken up here. However, I didn’t appreciate the same error message for multiple types of problems, either it’s a missing driver or failed authentication. And if you google it, almost all discussions in the internet are related to OData connection, which probably means that this is the only Microsoft product that has such a poor error handling.
Kate
Data masseuse