CODEWITHSUNDEEP
visual-studio-2015ssisodatasql-server-data-tools
stefan
stefan

Reputation: 115

SSIS Odata connection successful but collection does not load

I am trying to connect via an SSIS package within Visual Studio 2015 SQL Server Data Tools 2015 to an OData feed from Microsoft Dynamics NAV (hosted on the Microsoft Azure cloud).

The connection manager tells me when testing the connection "Test connection succeeded". However, when I want to specify this connection in the OData Source Editor it says "Unable to load collection for the connection xxxx, reason: cannot open this connection".

Interestingly, I am able to successfully connect to that OData feed and pull data via Power BI Desktop. This however is not practical as I cannot export data to a SQL Server DB from there.

enter image description here

Any suggestions what is causing this issue?

Upvotes: 4

Views: 11401

Answers (2)

Aducci
Aducci

Reputation: 26694

I have encountered this when your site contains:

  1. A List (Document Library, Calendar, etc..) that starts with a number (ie. 2016 Budget) AND
  2. The list contains a choice column

The only workaround I found was to rename the List:

  • 2016 Budget
  • Budget 2016

Upvotes: 1

Herronald Alexander
Herronald Alexander

Reputation: 21

I just recently had the need to connect to several SharePoint Lists in order to directly retrieve data. I decided to try using an OData Source. When creating the Connection Manager I always seemed to get a successful response when testing the connection when I put in the entire website location or sometimes parts of the url but when using the manager in the OData Source I received the error: "Unable to load collection for the connection xxxx, reason: cannot open this connection".

I finally located the following page that proved to be extremely helpful: https://www.mssqltips.com/sqlservertip/3220/using-the-new-odata-source-in-sql-server-integration-services/

In a nutshell the "Service document location:" in the OData Connection Manager Editor has to conform to the following format: http://mySharePointServer/mySite/_vti_bin/listdata.svc.

As the article says, the _vti_bin/listdata.svc part of the url does not change. After I used this format the OData Source filled the "Collection:" dropdown with all the lists in the Sharepoint site.

I hope this helps.

Upvotes: 2

Related Questions