Last week, Microsoft released the OData Source for Microsoft SQL Server 2012 . What is it? It allows SQL Server Integration Services (SSIS) to use an OData feed as a first class citizen data source in the same manner as SQL Server, Oracle, etc. Until now it was necessary to code OData connections using the script object.
This matters to those of us in the SharePoint world because any SharePoint list data can be expressed as OData.
I’ve written before about how SharePoint data can be extracted into a data warehouse using SSIS and the SharePoint List Source and Destination Adapters, available from CodePlex. These adapters plug in to SSIS and wrapper the SharePoint SOAP web services, and therefore do not need to be installed on a SharePoint server. We have used them for years, and they work very well, however, they are a CodePlex project, and therefore not fully supported.
These CodePlex adapters have more recently been bumping into another limitation. While SOAP web services are supported in Office 365, the adapters don’t support the Office 365 authentication mechanism, which effectively renders them useless. The OData services require the same authentication, but the the new OData Source supports it. It’s also an official Microsoft product, and is fully supported.
There is, however a trick to getting it working. Once you install the OData Source, you open up SQL Server Data Tools, open an SSIS project, and add or edit a data flow task. In the SSIS Toolbox, You should see the OData Source.
Drag the tool on to the design surface, and double click to configure it. You’ll first need to configure an OData Connection Manager, and you’ll do that by clicking the New button.
Give the connection a name. The connection will be common to all lists and libraries within a site, so something based on the name of the site is likely appropriate. The Service document location is the OData endpoint. It takes the form of the URL of the site, along with the suffix /_vti_bin/listdata.svc. If the connection is on premises, you can use Windows Authentication, but if it is Office 365, you must use a stored name and password.
If you are using Office 365, and you click Test Connection at this point, you’ll receive an error “Test connection failed –> The remote server returned an error: (400) Bad Request.”
This is due to Office 365’s “unique” authentication mechanism. In order to authenticate to Office 365, you must first select the “All” button in the toolbar, and set the value of “Microsoft Online Services Authentication” to true.
This option may not be available to you. If the Online Services Authentication option is disabled, or greyed out, as it was for me when I first tried to use it, it’s because a prerequisite is missing. In order to authenticate to Office 365, the machine must have the SharePoint Server 2013 Client Components SDK installed on it.
Once the client components are in place, and the option is selected, the data source should be able to connect to the source, and the connection manager can be closed. Lists are exposed as Collections, so if you want to work with list data, you can then select the list from the list of Collections.
At this point, the data source will act like any other SSIS data source, you can select and transform columns at will. More importantly, this will help you get SharePoint data both on-prem and in the cloud into a central data warehouse.
[…] 3. Using the SSIS OData Source Connector With SharePoint Online Authentication […]
[…] 3. Using the SSIS OData Source Connector With SharePoint Online Authentication […]
[…] the SSIS OData Source Connector With SharePoint Online Authentication | The White Pages http://whitepages.azurewebsites.net/2014/03/using-the-odata-source-connector-with-sharepoint-online-a… Sent from Maxthon […]
[…] Using the SSIS OData Source Connector With SharePoint Online Authentication | The White Pages http://whitepages.azurewebsites.net/2014/03/using-the-odata-source-connector-with-sharepoint-online-a… […]
Please note that this will not work with ADFS federated credentials, as the client components sdk referenced here and used/recognized by the OData Source Connector only supports non-federated authentication (v15 of the client and client runtime DLLs). There is a new SDK that supports federated authentication (http://www.microsoft.com/en-us/download/details.aspx?id=42038) but then was not able to switch the Services Authentication option to true. I even edited the code manually, but the connection still failed. I was able to get a connection working via a simple console app one of my developers put together, but until MS fixes this glaring oversight, SSIS integration remains 1 tiny step out of reach. That is, unless it is MY oversight 🙂
UPDATE: I copied the 2 required DLLs from the v16 SDK (located in C:Program FilesCommon Filesmicrosoft sharedWeb Server Extensions16ISAPI) to the C:Program FilesCommon Filesmicrosoft sharedWeb Server Extensions15ISAPI folder (v15 of the SDK) and was able to successfully make the connection.
I played with the OData Connector yesterday and I regret to report that it turned out to be next to useless as it simply doesn’t expose values from “Choice” columns. I tried adding query options as described eg. in http://www.odata.org/documentation/odata-version-2-0/uri-conventions/#QueryStringOptions but to no avail.
I hate to only be able to say that with my limited SSIS experience, I was able to export data and the choice values were showing in the data. However, for multi-select choice fields and lookups, as well as people pickers, it returned an ID, but even InfoPath has that same issue with lookups returning the ID value. I do agree that in this respect it is rather useless, but I overcame the issue with a few hidden columns that held the text value of the data (written by a workflow but you might be able to use calculated columns)
Thanks for this blog. Installing SharePoint client components SDK tip was very helful
Hi John, Thanks for your post. Thanks also to Chris for your comments. I’m having trouble with connecting to SharePoint online with this method. I’ve also copied over the DLLs as per Chris’ comment but to no Avail. Chris did you do anything else to get it working with federated credentials? Could I also ask what version of VS you’re using, and what versions of the SDK & OData Source (x64 or x86) you’re using?
Thanks again to both.
I am using VS 2012 and the 2 latest versions of the SDKs for on prem and online. All are x64. For what it’s worth, I also added the account used to access to the list with read permissions. I guess I really didn’t take a scientific approach because I did quite a few things prior to getting it to work, so I’m not positive of the exact combo. As for the federated account, the account I used was a company.com account and not an onmicrosoft.com account. Hope this helps.
Chris, thanks so much for getting back to me. So I’ve finally got it to work thanks to your last comment “the account I used was a company.com account and not an onmicrosoft.com account. Hope this helps.”. It certainly did help. Switching from an onmicrosoft.com account to a Company.com account resulted in a successful connection (to another site). I’m now hoping to test all the installations on a fresh environment so I know exactly what components I need to install on the server.
Are you still able to use this method when libraries have exceeded 5,000 items?
the odata source works great to download data from a list to a database or flat file destination but how do I upload data from a sql database to a cloud list effortlessly… is there a tool in ssis that will connect to claims based authentication in the cloud
Great article, although I’m able to successfully create a connection and test it, when I drop an OData Source into my Data Flow Task and select the connection I get the error message “Unable to load the collection for the connection…”. Has anyone ever come across this and knows how to resolve it?
Chris references “2 required dlls” that were copied from the v16 to v15 folder . What are the 2 dll’s?
Karen – I haven’t seen one yet. On prem you can use the SharePoint List and Destination Codeplex project, but it doesn’t work with O365 authentication.
Nick – Yes you can. The tool batches them 1000 at a time (by default)
I’m getting the following error after working through authentication issues: Test connection failed while parsing the XML document because it is not a valid OData service document.
Can I assume it found the listdata.svc file but couldn’t parse it? Any thoughts?
Or does SharePoint need to be enabled for OData feeds to work?
Can SSRS 2012 somehow leverage this?? We want to read SPO List data but SSRS does not support claims based authentication to o365/SPO.
This is in response to a question from anon. above. The 2 dlls to move are Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll. Copying them to the “15” folder as detailed above allows for Claims/Federated authentication in SSIS.
Cyberpine, Im not sure of SSRS. In any case, SSRS is much more integrated with on-prem SP2013. You could use an SSIS pkg to extract your list data from O365/SPO to SQL and then use SSRS to report off of that.
Very nice read and exactly what I have been looking for. Sadly, I can not go for it. Installed MS SQL Server 2014 BI (64 bit) and ODataSourceForSQLServer2014-amd64, but no chance to see any Odata Source component in SSIS development. Nothing in the common section, no connectors available within data flow window. I tried to add elements to the toolbox, nothing worked.
Any ideas on this one? Possible .NET version mismatch or something.
[…] First, get your environment ready, by following steps in this article: link […]
Hi Guys, thanks for this info. I was able to get info from the SharePOint list but I cannot find a way to update the list in the SSIS package. Someone has a idea of how to update the SharePOint list in the SSIS SQL 2014?
Best Regards
H.
Hi H.
Nope – this one is read only. You may want to look at the Codeplex project – it does what you want (but only works on prem)
@Cyberpine – you can pull your data into a data warehouse, and SSRS can report from there…..
Hey, folks!
I have SP on premise with ADFS, but for the life of me, cannot get it to work (neither using OData component or the SharePoint List Adapter (get a 403 forbidden)). Any advice?
All, after leaving my problem alone I came back to it today. I can use Power Query to access our Office 365 SharePoint site but then I would have to upload the data from Excel into the data warehouse. The SharePoint List Source Connector will not work, as far as I can tell with this site. Attempting to use OData method, but I continue to get the following error when setting up the ODate Source Connection Manager: Test connection failed while parsing the XML document because it is not a valid OData service document.
Does anyone have any insight? I’m apparently able to authenticate and reach the appropriate folder and file. Is it just a mal-formed service doc (I’m not sure who to contact if that’s the case)? Is this doc manually created or automatically maintained as part of the site infrastructure?
I was running into the following error: Unable to load collection for the connection xxx, reason: Cannot open this connection. After much troubleshooting, I figured out that the name of one of my sharepoint lists started with a number, and also contained choice columns. The name of the list in the catalog started with “c_123ListName”, and the choice element started with “C_123ListNameStatus”. Lower case c for the primary list resource, upper case C for the choice resource. Something wasn’t resolving correctly internally. When I changed the list name to start with a letter, the connection worked without issue.
I get an error using Odata source.
Here’s the error message:
[OData Source [2]] Error: The OData Source was unable to process the data. Object reference not set to an instance of an object.
SSIS package created using VS 2012 with BI template / table is in SQL 2008.
Trying to extract data from SP site into SQL table
Simple an straight forward process.
oData Souce task> OLEDB destination.
Any help!!
We are trying to connect from SSIS (SQL Server 2012 SP2) to an on prem SP 2010 environment that requires ADFS federated authentication. We’ve tried all options suggested above but without luck, the error is always “An existing connection was forcibly closed by the remote host”.
We have installed the SP 2010 SDK as well as the v15 and v16 Sharepoint SDKs that Chris mentioned. Any thoughts on how to get this working?
Thanks a lot for the post. I still cannot connect to the share point site. I downloaded the 64bit SDK that you suggested, but still no luck, getting the same error message? Does it matter where i save and run the SDK from?
You just saved my bacon. Thank you!!! The one gotcha I found. If you get an error along the lines of “For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method. (System.Xml)” Check your Site URL. Be sure you’re using the URL of the SITE and not of the LIST (as you would with the old Codeplex connectors).
How to get the attachments?
Hi @KenStuber
You just explained the solution for the error Im tring to solve now.
Not having my server at hand now, I cant try it… but can you please be more specific ?
What do you mean by Site URL and not the list url ?
shall I use http://mySharePointServer/mySite/_vti_bin insted of
http://mySharePointServer/mySite/_vti_bin/listdata.svc ?
I soo hope it is so , I tried for few hours today to solve it…
Much appreciated !
Alex
Great article! Exactly what I was looking for. Thank you!
Is there an OData component for a destination object, to write to a SharePoint list in a SSIS package. What would be the alternative for the SharePoint List Adapter for this if you are wanting to replace the adapter since it will not work in SQL 2014?
Thanks
Great read! Thanks very much for the clarification on why the codeplex adapters aren’t working.
Have there been any updates since the writing of this article? The codeplex adapters still don’t work and the ODATA adapter is a one-way street. Has anyone found a good solution?
Agree, great read! I am also looking for more info when the codeplex adapters are working for SQL 2014 or other possible solutions outside ODATA.
Sten-
I’ve commented to one of the developers on the Codeplex project – I’m hoping to get a reply soon.
Thank you! as suggested I installed OData source and placed the dll’s in assembly folder, it was working fine for few months. Unfortunately am getting the below error now. Can someone help me with this?
Error:
The partner returned a bad sign-in name or password error. For more information, see Federation Error-handling Scenarios. (Microsoft.SqlServer.IntegrationServices.ODataConnectionManager)
Hi, I installed the OData Source and SharePoint Server 2013 Client Components SDK, both x64 versions as my environment was so and the ‘Microsoft Online Services Authentication’ option was greyed out until I copied ‘Microsoft.Online.SharePoint.Client.Tenant.dll’ from C:Program FilesSharePoint Client ComponentsAssemblies to the folder mentioned by some of you above, ‘C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions15ISAPI’. Just in case it helps anyone.
Thanks for the article!
Enjoyed the article and gave OData a whirl. Got stopped because I say no way to specify a specific SP view for a list.
Can that be done?
This article saved me, I tried to use OData accessing SSRS (SharePoint integrated mode) using The Management Endpoints and it failed. Is there a solution to access SSRS through the SSIS?
Thanks
[…] I’m trying to retrieve information from a SharePoint Online list using SSIS following this guide: http://whitepages.azurewebsites.net/2014/03/using-the-odata-source-connector-with-sharepoint-online-a… […]
OData source is not picking the credentials from .config file in SSIS. I checked by entering the wrong password and still package execution is successful. Package protection level is DontSaveSensitive data. I tried removing the credentials from the connection manager but still it is running. Any suggestion please
Great tool, really useful.
I am using SP 2013, SSIS (VS 2015) and SQL server 2014.
I was trying to connect to sharepoint list using OData Source in SSIS, but its throwing me error below massages.
The underlying connection was closed: An unexpected error occurred on a receive. (System)
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
An existing connection was forcibly closed by the remote host (System)
Can you please tell me the solution for this error?
Is this error is because of the different tools versions?
I tried the same tool for other projects, its works fine there.
Please let me know if any more information needed.
Thanks you in advance. 🙂
Hi .
This is exactly what I was looking for. But unfortunately I am not getting the installables as mentioned in the below mentioned page
https://msdn.microsoft.com/en-us/library/dn584121(v=sql.110).aspx
I am using SQL 2012 and VS 2013 , sharepoint 2010/2013. I hope this will work
Please suggest regarding the installable path and more
I too am getting similar error:
The underlying connection was closed: An unexpected error occurred on a receive. (System)
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (System)
An existing connection was forcibly closed by the remote host (System)
I can connect to my SharePoint 365 list from SSIS, but I don’t see any choice fields. Any ideas on how to pull those in?
I imagine that your problem is that you’re allowing multiple values in your choice fields. OData doesn’t support that type. If you can disallow multiple values, you may have more luck.
Thanks for the blog, it was very helpful. Do you know if there is any way to capture the Attachment status (True/False) with the OData connection?
Our organisation uses OData connections to our flashy new Data Hub, i assume its Sharepoint 365. The documentation I have been provided works when i connect from Excel or PowerBI but no matter what I can’t connect from SSIS. We use basic auth, I’ve switched the MS Online Services Auth option to true – still unauthorized.
On the documentation i’ve been provided there is an extra step for Excel, where we change the URL that the Basic Auth applies to – there are two options “https://domain.com/xxx.svc” and “https://domain.com” we have to switch it to the root without the service doc name. I think this is what’s causing my problem but I have no idea of the settings in SSIS to do the same?
Hello sir,
Does O data source both read and write to SharePoint via SSIS package or do we need something else to write to SharePoint. Can you please advise.
OData will only let you read I’m afraid. You’ll likely want to look at the CodePlex solution if you need to write.
Hi
i am getting following error message, when i test the connection
Test connection failed -> For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create
Thank you for the info. I could able connect to my SharePoint 365 online list from SSIS, and execute the package in my machine. But, when I have deployed to SQL server (windows2012 R2-OS, SQlServer2016). Following error triggered. Any ideas what could be the issue?
“Failed to initialize SharePoint.Client. Runtime. The exception message was: Could not load file or assembly ‘Microsoft.SharePoint.Client.Runtime, version=15.0.0.0, Culture=neutral, PublickKey Token=71e9bce111e9429c’ or one of its dependencies, The system cannot find the file specified”.
Regards
Srini
Hi,
I was able to resolved my previously posted issue by installing SharePoint server 2013 Client components SDK
https://www.microsoft.com/en-pk/download/confirmation.aspx?id=35585
-Srini
Hello,
This article is great, and I have been able to preview the data using this method, but when I attempt to run the package in VS I receive the following error message:
“Exception deserializing the package “The package failed to load due to error 0xC0011008 “Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.”. This occurs when CPackage::LoadFromXML fails.”.
Hi,
I got this to work great on our company’s sharepoint. However, it seems as for our customer, claims based authentication create another hurdle. Any ideas on how to deal with that?
Thanks a lot in advance
Alexander
Hello Sir,
This is really nice blog to import Sharepoint online list data into SQL server….
However i was wondering if there is any components like “OData Destination” to push the SQL data into the Sharepoint?
Kindly help me with the methods of achieving my requirement…
Thanks..
Hi Imran. The OData solution is read only. There is a Codeplex solution “SharePoint list source and destination” that is read write, but it won’t work with SharePoint online.
John,
I have my OData Source connection to Office 365 set up. The test connection works and I see data in the preview. I have the 32 bit and the 64 bit SharePoint client dlls but whenever I run my package (in Visual Studio, from Integration Services in SSMS or as a SQL Job) I get the same error
cannot acquire a managed connection from the runtime connection manager
I have extensively researched this issue online but none of the ‘solutions’ work for me. Any suggestions gratefully accepted.
Hi John,
As a tenant admin (xxxx@xxxx.onmicrosoft.com), I am able to connect SPO site but as a site admin login credentials, I am getting the below error. Could you please check what changes are required from my end.
The partner returned a bad sign-in name or password error. For more information, see Federation Error-handling scenarios. (Microsoft.SqlServer.integrationServices.ODataConectionManager)
Hi John,
when connecting to sharepoint list collection having claim based authentication , i am not able to view microsoft online service dropdown , can you please help me wih this.
Hi John,
Is there anyway to parameterized the OData connection user ID and Password?
is OData Open source,i mean its gonna work for lifetime or its kinda trial version
Hi,
Can anyone please tell me why Collection: Unable to load collection for the connection OData Source, reason: Cannot open this connection
Whereas, the connection was successfully connected but won’t collect the data for some reason.
I’m trying to move SharePoint List data to SQL any suggestions?
Thanks
Hi,
For my project every thing working find till the end of the doc you shared. Bout when when I tried to open any collection I.e. Click on preview collection data is throwing ‘remote server returned 403 error’ and sometimes ‘operation time out error’.
Even after selecting any any specific collection under Listdata.svc,and tried to open Columns by clicking columns tab. It’s throwing an error as:
Pipeline component has returned HRESULT error code 0xC020801F from method call error
Please help
Hi Sindhu
We are facing similar issue. Is your issue resolved and what are the steps to resolve the same
Great article. On my sharepoint site I need to pull in the site list from the “Data” view. How do I pass in that value to my OData Source?
Hi White,
I am not seeing the option of microsoft online service authentication option while having the below installed list in my system where i am still facing the test connection failure.
SQL data tools 2015
OData Source for Microsoft SQL Server® 2014
SharePoint Server 2013 Client Components SDK
SharePoint Server 2016 Client Components SDK
Could you please help out with the solution ?
I am getting time outs. I can connect to the list just fine in a browser. I can preview the data from the ODATA connector. My package times out when I run it. I have tried mapping just a few columns to downsize for testing. I have tried source destinations of SQL as well as flat file. It’s not getting that far. The progress wheel on the ODATA source spins and spins until it times out. Any ideas?
@Joe, I am also getting similar error lately.. Could you let me know if you are able to get any solution for this ?
I can’t get it to work. In the ODATA Source Editor I can preview the SP OnLine list all day long. But when I execute the task it runs and runs until it times out. Any ideas?
@goutham, I totally forgot that I posted that on 4/11 and I posted again today. This is driving me a bit batty, actually. I see form the comments that a lot of people are able to get this to work but I don’t see what I am doing wrong.
Thank you, Its work for me.
I am also running into the earlier reported issue of “The partner returned a bad sign-in name or password error. For more information, see Federation Error-handling Scenarios”.
I have made sure to install the latest 2013 & 2016 SharePoint Client SDK’s
In the environment where we work, we use both SharePoint 2013 and SharePoint 365.
I can do the Preview of the list I want to access and import into the database, however when I run the package, I get the message:
SSIS package “C: \ Projects Integration Services \ Integration Services Project1 \ Test – oData.dtsx” starting.
Information: 0x4004300A at Task Data Flow, SSIS.Pipeline: The validation phase is starting.
Error: 0xC020801A at Task Data Flow, OData Source [17]: The connection “Connection” can not be found. Verify that the connection manager has a connection to that name.
Does anyone have any idea how to solve this?
As an update to my continued problem with Odata within SSIS package, I completely uninstalled all version of Visual Studio and SQL Server components and re-installed SQL 2012, SQL 2014 & SQL 2016 along with VS VS2013, VS2015 & VS2017. I also copied the SP SDK DLL’s from 16 folder to 15.
I am still unable to use the OData control in my Integration Services package. Keep getting the ““The partner returned a bad sign-in name or password error. For more information, see Federation Error-handling Scenarios”
Hopefully someone can help me ? MS SUpport so far has been unable to 🙁
Shreeni
Good afternoon Devs!
On 14/08 I posted a question about an error I was receiving when I use the ODataSource connector in SSIS to extract data from a Sharepoint list.
Well, after 15 days I finally got the package to run and blabber, the problem was only ONE parameter.
So, I came to share because I did not find this solution in any forum and could come to serve the professionals who are going through this problem at the moment or even that they happen in the future.
The solution is simple: when opening the solution in Visual Studio, click on the “PROJECT” menu >> “IN THE NEW WINDOW THAT OPEN, GO TO THE CONFIGURATION PROPERTIES GUIDE” > “GENERAL” OPTION >> ITEM “TARGETSERVERVERSION”
In my case, by Default, it displays the version of SQL Server 2017, however our database park is SQL Server 2016, however we are using the 2017 version for SSMS and SSIS, so the solution was to perform a Downgrade, ie I changed the value of the parameter to SQL SERVER 2016 and believe me …. it worked !!!
I hope this information helps and is relevant to other professionals.
A hug and good luck!
Thanks for the blog. I have a major problem happening with this OData connection. I am getting the 400 system error. Based on that when i goto change the property for Integrated Security and Microsoft Online Services Authentication, both these settings itself doesnt appear in the window. I have tried installing and reinstalling the odata adapter and sharepoint client and it didnt help. Could any of you advise how i can make those settings appear on my SSDT. I am using SSDT 2017.
Thanks for the blog. I have a major problem happening with this OData connection. I am getting the 400 system error. Based on that when i goto change the property for Integrated Security and Microsoft Online Services Authentication, both these settings itself doesnt appear in the window. I have tried installing and reinstalling the odata adapter and sharepoint client and it didnt help. Could any of you advise how i can make those settings appear on my SSDT. I am using SSDT 2017.
Regards,
Ravi
Hi Karen,
I followed the steps provided, unfortunately unable to make successful connection receiving the below error message.
TITLE: OData Connection Manager Editor
——————————
Test connection failed
——————————
ADDITIONAL INFORMATION:
For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method. (System.Xml)
It’s a great article, thank you! I’m new to SharePoint and SSIS and I’m trying to get data from excel file sheet1 and loed to my SQL server table. Would anyone can help? Thanks Edward
Thank you!
Thanks to you! I managed to solve it by following your instructions!
Hi,
Currently SSIS using Basic authentication to connecting Sharepoint lists, It’s need to change to Basic authentication.
Is it possible to amended Odatasource authentication in SSIS.
Folks, please advise if anyone encountered below issue and resolved or any idea:
Message:
Executed as user: xxx\srv-XXXX. Microsoft (R) SQL Server Execute Package Utility Version 12.0.6444.4 for 32-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 7:00:01 AM Error: 2023-12-08 07:00:03.63 Code: 0xC020801F Source: DFT Branch ODS Branch Source [71] Description: Cannot acquire a managed connection from the run-time connection manager. End Error Error: 2023-12-08 07:00:03.63 Code: 0xC0047017 Source: DFT Branch SSIS.Pipeline Description: ODS Branch Source failed validation and returned error code 0xC020801F. End Error Error: 2023-12-08 07:00:03.63 Code: 0xC004700C Source: DFT Branch SSIS.Pipeline Description: One or more component failed validation. End Error Error: 2023-12-08 07:00:03.63 Code: 0xC0024107 Source: DFT Branch Description: There were errors during task validation. End Error Error: 2023-12-08 07:00:03.63 Code: 0xC00220E4 Source: EPT_Branch Description: Error 0xC0012050 while preparing to load the package. Package failed validation from the ExecutePackage task. The package cannot run. . End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:00:01 AM Finished: 7:00:03 AM Elapsed: 2.406 seconds. The package execution failed. The step failed.
Development Platform:
SQLServer-2014 (msdb)
Visual Studio 2013
SQL Agent Job is failing with above error after 24 hours
Configuration File String:
“Service Document Url=https://XXXX.sharepoint.com/sites/reporting-centre/_layouts/15/viewlsts.aspx/_vti_bin/listdata.svc;Include Atom Elements=Auto;Include Expanded Entities=False;Integrated Security=False;Cookie=”SPOIDCRL=xxx/PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz48U1A+VjEzLDBoLmZ8bWVtYmVyc2hpcHwxMDAzMjAwMDRkMjQwNzQ3QGxpdmUuY29tLDAjLmZ8bWVtYmVyc2hpcHxzcnYtb2RhdGFjb25uZWN0b3JAbWVsYm91cm5lLnZpYy5nb3YuYXUsMTMzNDY0NTQ2NzcwMDAwMDAwLDEzMjY1ODU2NzYzMDAwMDAwMCwxMzM0NjU0MTA3NzMxNTczODcsMC4wLjAuMCw0LDdlYTFkMDdlLWE2ODYtNGU1Zi1hNDAzLWJjNzAzMTIzMzYyOSwsLDcxODJmNWEwLTQwNzEtMjAwMC1iYjViLWVmY2M5YzBlNmI3OSw3MTgyZjVhMC00MDcxLTIwMDAtYmI1Yi1lZmNjOWMwZTZiNzksLDAsMCwwLCwsLDI2NTA0Njc3NDM5OTk5OTk5OTksMCwsLCwsLCwwLCwxOTYwNDksR0FkeFdYM3FnLXBsUDRlOVhCUDF5MTZpZmpVLGhZSEMyblZTZWtQcUtBNzd1WkhDVXVaNmRDVUYrdG8zdUFmVkRidEI5d0xGWm94YVM0VE5jeHBvZXBYYXRpWjVKUWZ2VmkzUi8xNllDYW1sSmkyV0dsNDVsV1oxRjE5SisvOFhHOUpPcS9IV3Z2WE15RWNoa1FRUyt3cmt3MzFFUFFzM0txcEllZE4zNVpaRlZHSGExN1Z4UW5yWk1xWmY5bWRmSk0wTzdMOUk1Y1N4TWRDY0RUdTAxSE1FbEhZTWRjN1BYaWxsZlhMZHdJUHZCSHdwZDZDSU9xbWtGdVJRSFUwVFkzeXpDZzZnRm0vVGFNL1lUbEJTQUlKa3BLL1dCMTAxTzRScGlVQVBoTzBVMXFVQU1rVVptMUNsVk8zYTFoRCtYNktNb1puNkZNVG9pWUNFbS84UVArT294aDlER0lqcVRTUDAzU0JYS1V4ZGlrajBCZz09PC9TUD4=”;User ID=srv-XXX@xxx.com;Persist Security Info=False;Time Out=6000;Schema Sample Size=25;Retry Count=5;Retry Sleep=100;Keep Alive=True;Max Received Message Size=4398046511104″