Sql-server – OData Source Component in SSIS Not Connecting

This is a follow-on from a previous question: OData Source Component in SSIS Hanging

Setup

I'm using SSIS in Visual Studio 2015 (17.4) 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.

I've successfully created an OData Source Connection Manager to the base URL: https://data.medicaid.gov/api/odata/v4/ using windows authentication.

I've created a simple Data Flow with 2 components. An OData Source and a Recordset Destination. The Recordset Destination is pointing to a variable with a Data type of Object.

On my Data Flow, I've successfully created an OData Source to resource path a4y5-998d and am able to preview the data.

Screenshot showing the preview data and the data flow layout & the OData Source Editor configuration.

Core Problem

Everything looks good, but when I run my package in Visual Studio, I get the following error:

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Error: 0xC020801F at Data Flow Task, OData Source
[2]: Cannot acquire a managed connection from the run-time
connection manager. Error: 0xC0047017 at Data Flow Task,
SSIS.Pipeline: OData Source failed validation and returned error code
0xC020801F. Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or
more component failed validation. Error: 0xC0024107 at Data Flow Task:
There were errors during task validation.

I've bolded the key sentence (cannot acquire a managed connection from the run-time connection manager) but included more messages for context.

Because this is a validation error, the data flow doesn't even start to run.

Looking around the internet, a number of places suggest this could be a 32-bit/64-bit driver problem. However, I've toggled Run64BitRuntime on and off and I get the same result each time.

I get the same result whether I run the package in SQL Server 2016 or SQL Server 2017 mode.

HTTP vs. HTTPS

When I change the URL in the OData Connection Manager Editor to be http instead of https, things seem to progress a little better.

The package validates, and I get a yellow spinning circle above OData Source. But then I get a message:

Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for
Execute phase is beginning. Information: 0x40043007 at Data Flow Task,
SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C
at Data Flow Task, SSIS.Pipeline: Execute phase is beginning. Error:
0xC02090F5 at Data Flow Task, OData Source [2]: The OData Source was
unable to process the data. The underlying connection was closed: An
unexpected error occurred on a send. Error: 0xC0047038 at Data Flow
Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The
PrimeOutput method on OData Source returned error code 0xC02090F5.
The component returned a failure code when the pipeline engine called
PrimeOutput(). The meaning of the failure code is defined by the
component, but the error is fatal and the pipeline stopped executing.
There may be error messages posted before this with more information
about the failure. Information: 0x40043008 at Data Flow Task,
SSIS.Pipeline: Post Execute phase is beginning. Information:
0x4004300B at Data Flow Task, SSIS.Pipeline: "Recordset Destination"
wrote 0 rows. Information: 0x40043009 at Data Flow Task,
SSIS.Pipeline: Cleanup phase is beginning. Task failed: Data Flow Task

The key sentence here is: The OData Source was unable to process the data. The underlying connection was closed: An unexpected error occurred on a send.

Though the package works differently when using HTTP instead of HTTPS, I'm aware this may be something completely unrelated to the fundamental problem. It's the only thing that's made a difference so far.

Summary

Does anyone have any suggestions for using the ODATA Source component in SSIS to bring in a public OData feed? Is anyone be able to get this working? (My package is fairly simple if you want to try yourself.)

This is a publically accessible OData feed with no authentication. The OData Source component in SSIS is very simple: all it really requires is a URL. I'm able to preview the data perfectly fine.

I'm stumped as to why I can't get the data actually loading. It shouldn't be this hard. (I have no issues loading the data into Power BI for example.)

Any help you can offer would be greatly appreciated.