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 ofhttps://
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
Best Answer
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:
They mentioned the following solution:
Also based on the following article:
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:
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: