Search code examples
ssisodata

OData Source Component in SSIS Hanging

I'm using SSIS and would like to pull in the public NADAC data from this page using the OData connection: https://data.medicaid.gov/Drug-Pricing-and-Payment/NADAC-National-Average-Drug-Acquisition-Cost-/a4y5-998d.

You can find the OData endpoint on their page by clicking on the ellipse and selecting Access Data via OData. The v4 endpoint is: https://data.medicaid.gov/api/odata/v4/a4y5-998d

In SSIS (Visual Studio 2015, Version 14.0.500.272 targeted at SQL Server 2016), I am able to create an OData Source Connection Manager to the base URL: https://data.medicaid.gov/api/odata/v4/

Image of OData Connection Manager Editor with the URL clearly displayed, Windows Authentication selected, and a message "Test connection succeeded"

You can see the test connection succeeded. Note: If I specify the full OData End Point URL here, it does succeed, but then doesn't find any collections to connect to in the next step, which I why I'm not doing that. (The message is "Unable to load collection for the connection OData Source, reason: Cannot open this connection")

Also note, I'm using "Windows Authentication" but this is a public feed and no authentication is required. There doesn't appear to be a "anonymous authentication" option.

The next step is to drag the OData Source onto my data flow and right-click to edit it. When I do this, I first select the OData connection manager I just created. It recognizes the connection and displays a drop-down list of collections I can connect to.

Picture of the OData Source Editor showing a drop-down list of collections

Clearly, I'm doing something right up until this point, since it's recognizing the collections that are available.

However, whether I:

  1. Select any collection in the list
  2. Select the collection in the list that I want (a4y5-998d)
  3. Switch from collection to resource path and paste in the resource path I want (a4y5-998d)

and then click "Preview..." or "Columns" or even "OK" - Visual Studio becomes entirely non-responsive. For 20+ minutes. I have to use Task Manager to crash out and try again. If I let it sit forever, I eventually get a yellow warning message: Pipeline component has returned HRESULT error code 0xC020801F from a method call.

I also haven't had any success with the v2 OData feed endpoint that Medicaid provides (https://data.medicaid.gov/OData.svc/a4y5-998d).

My two questions:

  1. Am I doing something obviously wrong? Perhaps I'm using the wrong URL, or not setting something correctly?
  2. Do you experience the same problem or are you able to get it to work using the steps I've tried? To recreate, create a new SSIS package with a Data Flow component and follow my steps.

Note: I am able to bring the data in using Power BI (for example), so I don't think there's anything wrong with the feed. It's just SSIS I'm struggling with.

Thanks in advance for any help.

Solution

  • This was a visual studio bug. I upgraded from SSDT 17.0 to SSDT 17.4 (SSIS version 14.0.1000.169) and the error went away.