3

Objective: Connect Odata Source in SSIS to Medicare data

When tried Odata v4 endpoint:

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

screenshot

  • Confirmed endpoint works in Tableau desktop connection

When tried Odata v2 endpoint:

  • Get connection successful when creating Connection Manager

screenshot

  • "unable to load collection" with Odata Source Editor

screenshot

Using SSIS Visual Studio 2017 v 15.5.5

Any help would be appreciated

Hadi
  • 36,233
  • 13
  • 65
  • 124
Vinh Ton
  • 107
  • 3
  • 10

1 Answers1

2

Update 1

Just enter the following URL in the ODATA connection manager

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

And it will work perfectly.

enter image description here

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

enter image description here

enter image description here

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)

enter image description here

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

enter image description here

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Tried and still not able to connect. What are you using for your other settings? I tried both Window Authentication and Basic Authentication as well as Collection and Resource Path – Vinh Ton Jan 29 '18 at 19:57
  • @VinhTon I found it, just enter `https://data.medicare.gov/OData.svc` as a datasource on the ODATA connection manager, Check my answer update – Hadi Jan 29 '18 at 20:06
  • thanks for the screenshots, I noticed my doesn't have protocal v3. I'm going to try updating the drivers, otherwise everything else looks the same – Vinh Ton Jan 29 '18 at 22:15
  • 2
    Thanks @Hadi !! Not sure if it was the drivers but it definitely was the path. I was including the resource rather than using only url: https://data.medicare.gov/OData.svc – Vinh Ton Jan 29 '18 at 22:26