How to fix "[OData Source [56]] Error: Cannot acquire a managed connection from the run-time connection manager." error in SSIS?

3
votes

I am having problem when running SSIS which extracts data from SharePoint list. SSIS Package was running fine till 2 weeks back and suddenly the package is failing with this error:

[OData Source [56]] Error: Cannot acquire a managed connection from the run-time connection manager. [SSIS.Pipeline] Error: OData Source failed validation and returned error code 0xC020801F. [SSIS.Pipeline] Error: One or more component failed validation. Error: There were errors during task validation

Tried:

  1. Changing 64bit debug option in package properties to false
  2. http:// instead of https://

I expect SSIS package to extract data from SharePoint list without any issues but facing the above error message recently which was working fine for more than a year.

Server detail: Windows server 2012 R2.

Application details: Visual studio 2010, .NET framework - 4.5

Update @ 2019-02-17

We are not using windows authentication and SSIS Passage is getting failed even after running SSDT as administrator. We are able to preview the Sharepoint list data in ODATA Source, but package is getting failed with the mentioned error message when triggered

3
ssisconnectionodataetlsharepoint-list

3 Answers

10
votes

Update 2 - ODATA connection properties

In the ODATA connection manager try setting the following properties as shown in the image:

enter image description here

Update 1 - TLS issue

While searching i found a the following related article:

They mentioned the following solution:

We can resolve this using the below steps:

  1. Install Microsoft .NET 4.6 and above on your computer (https://technet.microsoft.com/en-us/library/security/2960358.aspx).
  2. Enforce TLS 1.2 on your machine through registry settings. In an elevated command prompt run the following commands:
    • reg add HKLM\SOFTWARE\Microsoft.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:64
    • reg add HKLM\SOFTWARE\Microsoft.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:32

Also based on the following article:

  • .NET 4.6 and above. You don’t need to do any additional work to support TLS 1.2, it’s supported by default.
  • .NET 4.5 TLS 1.2 is supported, but it’s not a default protocol. You need to opt-in to use it. The following code will make TLS 1.2 default, make sure to execute it before making a connection to secured resource: ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12

Similar issue linke

(The link i mentioned are also mentioned in this question, also there are many helpful comments, this question is very related but the difference is that they are not connecting to Sharepoint list, they are connecting to a public data source)

Original Answer

Trying to figure out the issue

Based on the information you provided, it looks like the issue is not caused by SSIS, it is related to the environment you are working in or maybe the credentials you are using to access the Sharepoint List.

In the ODATA Connection Manager, there are many Authentication option you can use:

Authentication

Select one of the following options:

  • Windows Authentication. For anonymous access, select this option.
  • Basic Authentication
  • Microsoft Dynamics AX Online for Dynamics AX Online
  • Microsoft Dynamics CRM Online for Dynamics CRM Online
  • Microsoft Online Services for Microsoft Online Services

If you select an option other than Windows Authentication, enter the username and password.

If you are using Windows Authentication, try to run the Visual Studio as Administrator and run the package again, if you are using other authentication type check that this account still have access.

If you are running the package usin SQL job, check also that the user has necessary permissions to read data from Sharepoint list.

Some helpful links

In addition, i will provide some helpful links on how to connect to a Sharepoint list using ODATA Source, it may give you some insights:

2
votes

We got this error since proxy was enabled in the server. SSIS Package is not able to access Sharepoint List during execution when the proxy is enabled. This is working fine after changing the connection to "automatically detect settings" in the internet options.

2
votes

This solution helped but the command still has an issue. The \ is missing from the command (between Microsoft and .NETFramework)

reg add HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:64