SSIS中的OData源组件未连接 [英] OData Source Component in SSIS Not Connecting
问题描述
这是上一个问题的后续内容: SSIS挂起中的OData源组件
This is a follow-on from a previous question: OData Source Component in SSIS Hanging
我在Visual Studio 2015(17.4)中使用SSIS，并希望使用OData连接从此页面提取公共NADAC数据:
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.
我已经成功创建了基本URL的OData源连接管理器: https://data.medicaid.gov/api/odata/v4/使用Windows身份验证.
I've successfully created an OData Source Connection Manager to the base URL: https://data.medicaid.gov/api/odata/v4/ using windows authentication.
我创建了一个包含2个组件的简单数据流. OData源和记录集目标. Recordset目标指向数据类型为Object的变量.
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.
在我的数据流上，我已经成功创建了一个OData源到资源路径a4y5-998d，并且能够预览数据.
On my Data Flow, I've successfully created an OData Source to resource path a4y5-998d and am able to preview the data.
屏幕截图，显示了预览数据和数据流布局以及OData Source Editor配置.
一切看起来不错，但是当我在Visual Studio中运行包 时，出现以下错误:
Everything looks good, but when I run my package in Visual Studio, I get the following error:
信息:SSIS数据流任务中的0x4004300A.管道:验证阶段正在开始.错误:0xC020801F(数据流任务中，OData源) [2]:无法从运行时获取托管连接 连接管理器..数据流任务上的错误:0xC0047017， SSIS.Pipeline:OData源验证失败并返回错误代码 0xC020801F. SSIS数据流任务时错误:0xC004700C管道:一个或 更多组件验证失败.数据流任务处的错误:0xC0024107: 任务验证过程中出现错误.
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.
环顾互联网，许多地方表明这可能是32位/64位驱动程序问题.但是，我已打开和关闭 Run64BitRuntime ，每次都得到相同的结果.
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.
无论是在SQL Server 2016还是SQL Server 2017模式下运行程序包，我都得到相同的结果.
I get the same result whether I run the package in SQL Server 2016 or SQL Server 2017 mode.
当我在OData Connection Manager编辑器中将URL更改为http而不是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.
该软件包通过验证，并且在OData Source上方出现一个黄色的旋转圆圈.但随后我收到一条消息:
The package validates, and I get a yellow spinning circle above OData Source. But then I get a message:
信息:SSIS数据流任务处的0x40043006.管道:准备 执行阶段开始.信息:数据流任务处的0x40043007， SSIS.Pipeline:预执行阶段正在开始.信息:0x4004300C 在SSIS数据流任务中.管道:执行阶段开始.错误: 数据流任务处的0xC02090F5，OData源[2]: OData源为 无法处理数据.基础连接已关闭: 发送时发生意外错误.数据流处错误:0xC0047038 任务，SSIS.Pipeline:SSIS错误代码DTS_E_PRIMEOUTPUTFAILED.这 OData Source上的PrimeOutput方法返回了错误代码0xC02090F5. 当管道引擎调用时，组件返回失败代码 PrimeOutput().故障代码的含义由 组件，但错误是致命的，并且管道停止执行. 在此之前可能会发布错误消息，并提供更多信息 关于失败.信息:数据流任务处的0x40043008， SSIS.Pipeline:后执行阶段开始.信息: SSIS.Data Flow Task上的0x4004300B，管道:记录集目标" 写了0行.信息:数据流任务处的0x40043009， SSIS.Pipeline:清理阶段正在开始.任务失败:数据流任务
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
此处的关键句子是:OData源无法处理数据.基础连接已关闭:发送中发生意外错误.
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.
尽管使用HTTP而不是HTTPS时，程序包的工作方式有所不同，但我知道这可能与根本问题完全无关.到目前为止，这是唯一有所作为的东西.
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.
对于使用SSIS中的ODATA Source组件引入公共OData提要，有人有建议吗?任何人都可以使它正常工作吗? (如果您想尝试一下，我的包装相当简单.)
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.)
这是不带身份验证的可公开访问的OData提要. SSIS中的OData Source组件非常简单:它真正需要的只是一个URL.我能够很好地预览数据.
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.
我为为什么无法实际加载数据而感到困惑.不应该那么难. (例如，将数据加载到Power BI中没有问题.)
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.)
我们将不胜感激.
推荐答案
我遇到了类似的问题.就我而言，OData仅可通过https访问.
I had a similar problem. In my case, the OData is available throught https only.
这些链接有很大帮助:
-
这是TLS版本，必须为1.2，但是使用.Net Framework 4.5的服务器未将其设置为默认版本.
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.
这篇关于SSIS中的OData源组件未连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
