Unable to connect to Odata source v4 and v2 in SSIS

Question

Objective: Connect Odata Source in SSIS to Medicare data

When tried Odata v4 endpoint:

• Get "Test connection Failed" when creating Connection Manager.

• Confirmed endpoint works in Tableau desktop connection

When tried Odata v2 endpoint:

• Get connection successful when creating Connection Manager

• "unable to load collection" with Odata Source Editor

Using SSIS Visual Studio 2017 v 15.5.5

Any help would be appreciated

Answer 1

Update 1

Just enter the following URL in the ODATA connection manager

https://data.medicare.gov/OData.svc

And it will work perfectly.

To get the dataset you are looking for just Select to use Resource Path instead of collection, and write 4pq5-n9py as Resource Path

---

Initial Answer

First of all, this is the link of v4

https://data.medicare.gov/api/odata/v4/4pq5-n9py

And if you access it you will see that there is an error retrieving data from this link (just copy and paste this link into your browser)

And using v2 you have to work with this link

https://data.medicare.gov/OData.svc/4pq5-n9py

If you open it using your browser, you will see that it returns data, but it will take a long time to finish loading it.

After trying to open the data from browser, try again to do this using ODATA Source, it will works, but it will take a lot of time.

The issue may be caused due to a Timeout expiration, or due to the message maximum size.

Try changing the corresponding values in the ODATA connection manager