This is a follow-on from a previous question: <a href="https://stackoverflow.com/questions/48026984/odata-source-component-in-ssis-hanging" rel="nofollow">OData Source Component in SSIS Hanging</a><h2>Setup</h2>
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: <a href="https://data.medicaid.gov/Drug-Pricing-and-Payment/NADAC-National-Average-Drug-Acquisition-Cost-/a4y5-998d" rel="nofollow">https://data.medicaid.gov/Drug-Pricing-and-Payment/NADAC-National-Average-Drug-Acquisition-Cost-/a4y5-998d</a>.
I've successfully created an OData Source Connection Manager to the base URL: <a href="https://data.medicaid.gov/api/odata/v4/" rel="nofollow">https://data.medicaid.gov/api/odata/v4/</a> 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.
<a href="https://i.stack.imgur.com/FbxG0.png" rel="nofollow"><img alt="In the background you can see my data flow components: an OData Source and a Recordset Destination. In the middle ground you can see how I've configured the OData Source Editor to use the resource path. On top, you can see the preview showing data from the OData source." class="b-lazy" data-src="https://i.stack.imgur.com/FbxG0.png" data-original="https://i.stack.imgur.com/FbxG0.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a> <em>Screenshot showing the preview data and the data flow layout & the OData Source Editor configuration.</em><h2>Core Problem</h2>
Everything looks good, but when I run my package <strong>in Visual Studio</strong>, I get the following error:<blockquote>
<em>Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Error: 0xC020801F at Data Flow Task, OData Source : <strong>Cannot acquire a managed connection from the run-time connection manager.</strong> 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.</em></blockquote>
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 <strong>Run64BitRuntime</strong> 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.<h2>HTTP vs. HTTPS</h2>
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:<blockquote>
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 : <strong>The OData Source was unable to process the data. The underlying connection was closed: An unexpected error occurred on a send.</strong> 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</blockquote>
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.<h2>Summary</h2>
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.Answer1:
I had a similar problem. In my case, the OData is available throught https only.
These links helped a lot:<ul><li>
<a href="https://blogs.msdn.microsoft.com/dataaccesstechnologies/2018/01/16/tls-issue-with-ssis-package-while-accessing-odata-source-like-dynamics-ax-online/" rel="nofollow">TLS Issue with SSIS package while accessing OData Source like Dynamics AX Online</a></li> <li>
<a href="https://blogs.perficient.com/2016/04/28/tsl-1-2-and-net-support/" rel="nofollow">TLS 1.2 and .NET Support: How to Avoid Connection Errors</a></li> </ul>
It was the TLS version, which had to be 1.2, but the server, using .Net Framework 4.5, didn't set it as default.