26440

OData Source Component in SSIS Not Connecting

Question:

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 [2]: <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 [2]: <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.

Recommend

  • SSIS Package Cancels instantly on Debug
  • Can I host (self host) a WCF Service in a Windows Service and expose it via http?
  • Effectively searching through entire 1 level nested JSONB in Postgres
  • Take picture automatically with no user interaction [closed]
  • Problem with time() function in embedded application with C
  • Unknown number of sublists with AngularJS
  • HUAWEI kill a background app when the phone is locked
  • Write and Read from a file
  • Allow QGraphicsView to move outside scene
  • Linq query with a Conditional WHERE Clause
  • Remove Textmate Git Bundle
  • Encode string to Base64 in Inno Setup (Unicode Version of Inno Setup)
  • Pass multiple lines of stdin input to interactive Java command line program, non-interactively
  • How to check if the current repository is the top level git repo
  • MySQL ordering a date range to the beginning, then ordering everything else by other criteria
  • Lodash cloneDeepWith to omit undefined
  • Create One-To-One relationship between table and view in EF4?
  • Python tk scrollbar becomes inactive once text is outside the screen
  • Move elements from a listbox to another
  • How to set http status code when responding to servlet client from Filter class-method in tomcat
  • Bazel failed to include a external static library .a
  • How to get WinForms custom control's default value to be respected when first dropped on a form
  • date changes on export kendoGrid
  • No Gradle file syntax highlighting in Eclipse Mars.2
  • Creating Dictionaries from Lists inside of Dictionaries
  • Failed to resolve: firebase-auth-15.0.0 [closed]
  • Unable to run testNG tests from maven
  • Unable to start a WebView from an AsyncTask
  • separate tokens in batch file
  • playing mp3 from nsbundle
  • How to change the host IP sent in emails to new GitLab users to a publicly visible IP, not the local
  • Getting the type of a “Type” in C# reflection
  • How do I use libcurl to printf a remote FTP directory listing?
  • Support of :after in IE7
  • Cloud Code: Creating a Parse.File from URL
  • Google App Engine backend servlet not responding
  • Bad automatic Triangulation with Mayavi for coloring a surface known only by its corner
  • Make checkout phone field optional for specific countries in WooCommerce
  • Excel VBA : conditional formatting of sheet1 cells from sheet2 values in excel 2007