SharePoint lists are popular and simple tools for storing modestly-sized discrete sets of data. SQL Server Integration Services (SSIS) includes a source component to allow reading data from SharePoint lists.
In this post, I’ll demonstrate how to use SSIS to read from a SharePoint list using the OData source.
SharePoint lists
A SharePoint list is a simple collection of related data items. These lists are easy to create and share with a team, and can aid in centralizing what would otherwise be reference data stored in various spreadsheets. Lists are found in both on-premises and cloud versions of SharePoint.
SharePoint lists serve both business and technical users alike. For business users, a SharePoint list is an easy way to create and maintain a visible copy of shared data. Since load processes can easily consume the (mostly) structured data in these lists, technical folks welcome them as well.
Connecting to SharePoint Lists with SSIS
When a SharePoint list is curated to the point that it is trustworthy as a source of data, it can be integrated into ETL processes using SSIS. Using the OData source in the SSIS data flow, you can easily retrieve data from a SharePoint list into your data pipeline.
As shown below, the OData source is one of the native sources in the SSIS data flow.
You can add this to your data flow workspace and open it to display the configuration properties, as shown below.
The highlighted item above is the OData connection manager, which is the connection to the instance of SharePoint from which we’ll be retrieving the list data. Clicking New here will open the OData Connection Manager Editor shown below.
As shown, appending the /_vti_bin/listdata.svc/ suffix to the SharePoint URL (or the site name, if your SharePoint setup has multiple sites) will invoke the web service to get the catalog of assets. Below there, you’ll specify the authentication method. Because this example is using SharePoint online, I’m using Microsoft Online Services to connect. (Note: If you are using Sharepoint Online, see the to-do install at the end of this post.)
Using the OData Source in the Data Flow
After testing the connectivity, click OK to return to the OData Source Editor window. the Collection dropdown list now shows the SharePoint items available. For this example I want the list ScrantonPeople.
After choosing this list, I’ll click Preview to show the data available in that feed. I’ve cut out a few of the columns for brevity. It’s important to note that the column metadata will differ depending on the options chosen for each field in the list.
With that, the OData source is ready to use! Use it as a source for loading a database table, as a lookup reference data set, or as part of business rules.
Error: “Could not load file or assembly ‘Microsoft.Sharepoint.Client.Runtime’
When using the OData source for an instance of SharePoint online, I encountered an error similar to the following:
Could not load file or assembly ‘Microsoft.SharePoint.Client.Runtime, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ or one of its dependencies. The system cannot find the file specified.
I discovered this error on recent versions of SSIS, using both Visual Studio 2017 (SSDT) and Visual Studio 2019. As it turns out, the online version of SharePoint requires a specific version of the assembly Microsoft.SharePoint.Client.Runtime. This runtime requires a separate download and install. As of this writing, the required version is 15.0 (the same version associated with SharePoint 2013), which can be downloaded here.
Dear Mr. Mitchell,
Thank you for such a nice elobarated blog post. when I was following your post I got an prompt for missing sharepoint client components sdk ( online sharepoint ) which I installed. eventhough I installed it I am getting the same prompt ? Coud you be kind to help me out in this error ?
For others who find this, TLS 1.0 and 1.1 are soon to be deprecated. If you run into connection issues, try enforcing strong crypto with the following command:
reg add HKLM\SOFTWARE\Microsoft\.NETFramework\v4.0.30319 /v SchUseStrongCrypto /t REG_DWORD /d 1 /reg:64
As always, don’t make registry modifications unless you’re sure you know what you’re doing.
Tim great article, I am having an issue with the quantity of items in one of my lists. Firstly, I have validated the data and everything is fine. I can read the whole List into Power BI no problem. My issue is that the list in question has around ~10K items within. Everything is working, but….there is always a but, the OData component fails with an error when just under 4K have been already extracted.
This is the error:
Error: 0xC02090F5 at Load Data From SharePoint Absence List, Absence [2]: The Absence was unable to process the data. An error occured when reading the OData feed.
Error: 0xC0047038 at Load Data From SharePoint Absence List,
SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Absence 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.
Have you come across this before?
Dear Tim Mitchell: I have functioning SSIS using OData in VS2019 (which gets data from SharePoint list into SQL server) but unfortunately my SQL server version is 2012 (& not in position to upgrade). I cannot schedule VS2019 package to run in MSStudio v 2012. My attempt to get SharePoint list data using any other method in SSIS with no success yet. SharePoint list adapter download ,msi file from codeplex is not available. Any suggestions?!
I have 2012 SQL server and not in position to upgrade. I need to import data from SharePoint List into this database. Here is what I have done so far:
Installed VS2019 and developed SSIS package, which uses OData component in SSIS. Thanks for your detailed article. The package works fine in VS2019 However, I cannot save this package into MSDB of SQL 2012 but only save in file system. I could not schedule it to run as a job in SQL Server 2012. I tried to run from command line using VS2019 command line utility DTExec.exe but no success either. Stcuk really bad! Any idea or help you can provide will be highly appreciated.
I have the same error
Could not load file or assembly ‘Microsoft.SharePoint.Client.Runtime, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ or one of its dependencies. The system cannot find the file specified. Download share point sdk as well.
Using vs 2017
Hi,
I am using SSDT 2015 and getting below error while executing package:
[OData Source [25]] Error: Cannot acquire a managed connection from the run-time connection manager.
I have used service account to authenticate SharePoint api in place of personal account due to multifactor authentication but running SSDT from my personal account.
Please suggest.