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.
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=18.104.22.168, 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.