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

Question

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:

Changing 64bit debug option in package properties to false
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

Hadi · Answer 1 · 2019-02-16T23:06:43.400

Update 2 - ODATA connection properties

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

Update 1 - TLS issue

While searching i found a the following related article:

TLS Issue with SSIS package while accessing OData Source like Dynamics AX Online

They mentioned the following solution:

We can resolve this using the below steps:

Install Microsoft .NET 4.6 and above on your computer (https://technet.microsoft.com/en-us/library/security/2960358.aspx).

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:

TLS 1.2 and .NET Support: How to Avoid Connection Errors

.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)

OData Source Component in SSIS Not Connecting

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:

Hi Hadi, 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. — Vijay, Feb 16 '19 at 22:13
@Vijay can you provide the full package log, it may be some useful warnings that may lead to the cause — Hadi, Feb 16 '19 at 22:24
@vijay i found something interesting, check my answer update — Hadi, Feb 16 '19 at 22:36
Hi Hadi, This issue is resolved after changing the proxy settings in server. Thanks very much for your suggestions!! — Vijay, Feb 20 '19 at 15:02
I found my problem was solved by increasing the timeout value in the properties box to a value higher than 600. — Daniel K, May 20 '20 at 20:42
@Hadi, thank you for your thorough research and response. I have been battling an ODATA connection to Sharepoint for the last few days, finally thought that I had sovled it only to find this issue as well. Thanks for your thorough work — Ash, Sep 21 '21 at 07:26
I was using WindowsAuthentication which worked in the Visual Studio Designer, but didn't work when deploying the package even after adding all the registry settings. (Got the same error message as the the OP was getting.) Solution was to use BasicAuthentication (going to a SharePoint On-Premise list). Many many many hours just to figure that one out. — Tahari, Jul 06 '22 at 22:02
The registry edit to enforce TLS 1.2 solved the problem for me. — Sam Firke, Dec 07 '22 at 19:22

rivios7 · Answer 2 · 2019-07-29T16:10:08.690

4

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

edited Jul 29 '19 at 16:10

answered Jul 29 '19 at 13:57

rivios7

41
4

score 2 · Accepted Answer · answered Feb 20 '19 at 15:01

2

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.

answered Feb 20 '19 at 15:01

Vijay

107
1
1
9

1

it looks that Hadi has spent much time trying to solving this issue and has given many suggestions, i feel upset when at the end the user find a unexpected solution without upvoting the other answer In order to thank. – Yahfoufi Feb 21 '19 at 13:16

score 0 · Answer 4 · answered Dec 17 '21 at 04:25

If you are using a proxy, setting it for the .NET Framework in machine.config can help.

Open Notepad as administrator and open the following file(s):

C:\Windows\Microsoft.NET\framework\v4.0.30319\config\machine.config

C:\Windows\Microsoft.NET\framework64\v4.0.30319\config\machine.config

Search for <system.net> If <system.net> is not found, add the following text as the second line from the end above and substitute the proxy address as appropriate for the environment:

<system.net>
     <defaultProxy>
             <proxy autoDetect="false" bypassonlocal="true" proxyaddress="http://proxy_host:8080" />
     </defaultProxy>
</system.net>