未加星标

Reading SharePoint Lists with Integration Services 2017

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

By: Koen Verbeeck || Related Tips: >Sharepoint

Attend this free live mssqlTips webcast

Leveraging Storage Spaces Direct for SQL Server High Availability Thursday, July 19, 2018 - click here to learn more

Problem

We have data stored in SharePoint lists, which we need to extract into our database. We need to use SQL Server Integration Services (SSIS) for this task. How can we achieve this using the built-in components?

Solution

In previous versions of SSIS, the open-source SharePoint List adaptors from Codeplex could be used to access data stored in SharePoint lists. This was described in a previous version of this tip, however, Codeplex has been deprecated by Microsoft and it seems there are no further updates for the SharePoint List adaptors. This means we have to use an alternative. For reading data from a SharePoint List, the OData Source component can be used since a SharePoint List is exposed as an OData feed.

The availability of the OData source component depends on the version of SSIS:

For SSIS 2012 you have a separate download . For SSIS 2014 you need to download the component from the SQL Server 2014 feature pack . In SSIS 2016 & 2017, the source component is included out-of-the-box.

In this tip, we’ll read data from a SharePoint Online list using SSIS 2017. You can download the latest version of SQL Server Data Tools for Visual Studio 2017 here .

Test Data

First, we need a SharePoint List to read from. In this tip, we’ll use SharePoint Online, but the process is the same for an on-premises SharePoint. In the SharePoint site, navigate to Site Contents . There, you can add a new list by clicking on the plus symbol and choosing List from the dropdown.


Reading SharePoint Lists with Integration Services 2017

You’ll need to specify a name and an optional description. You can also choose if the list is displayed in the navigation menu at the left.


Reading SharePoint Lists with Integration Services 2017

When the list is created, a column with the name Title will be added by default. You can click on the plus icon on the right of the Title column to easily add new columns.


Reading SharePoint Lists with Integration Services 2017

We’ll add two extra columns: a code column containing integers and a text column. First, we’ll add a Number column:


Reading SharePoint Lists with Integration Services 2017

The column has the following properties:


Reading SharePoint Lists with Integration Services 2017

Next up is the text column.


Reading SharePoint Lists with Integration Services 2017

It has the following properties:


Reading SharePoint Lists with Integration Services 2017

Now we’re going to get rid of the Title column. In the top right corner, click on the arrow at All Items and select Edit Current View .


Reading SharePoint Lists with Integration Services 2017

Deselect the Title column from the list of columns and click OK:


Reading SharePoint Lists with Integration Services 2017

This removes Title from the view, but it’s still there and it requires values. Trying to insert data will result in an error if no value was specified for the Title column. We can change this behavior in the Settings. Go back to editing the view. At the top, click on Settings . This will take you to the list settings.


Reading SharePoint Lists with Integration Services 2017

In the columns section, click on Title.


Reading SharePoint Lists with Integration Services 2017

In the properties, set the setting for requiring information to No:


Reading SharePoint Lists with Integration Services 2017

Click OK and go back to the list. Now you can click on Quick Edit to fill in some data.


Reading SharePoint Lists with Integration Services 2017
Reading the List

Add a data flow to your SSIS package. From the Common section, drag the OData source into the canvas. Be careful, it is not listed under sources!


Reading SharePoint Lists with Integration Services 2017

Open the editor. Click on New to create a new OData connection manager.


Reading SharePoint Lists with Integration Services 2017

Specify a name for the connection manager and the URL for the SharePoint website. The URL takes the following format: https:// mycompany .sharepoint.com/sites/ mysite /_vti_bin/listdata.svc. The parts in bold need to be replaced for your situation. If you use an on-premises installation of SharePoint, you’ll also need to replace sharepoint.com with the address of you SharePoint website.


Reading SharePoint Lists with Integration Services 2017

Depending on your scenario, there are different authentication options available. Since we’re using SharePoint Online in this tip and we want to specify a username and password (so it can maybe be parameterized later on), we’ll choose Microsoft Online Services . In order for this authentication type to work on the server running SSIS, you need to install the SharePoint Server 2013 Clients Components SDK . You need to install those libraries on every server running the SSIS package.

Once you selected the Microsoft Online Services option, you can specify a username (most likely an email address) and password.


Reading SharePoint Lists with Integration Services 2017

Click on OK to create the connection manager. Now we can choose our SharePoint list from the Collection dropdown:


Reading SharePoint Lists with Integration Services 2017

In the OData Source component, you can work with Collections or Resource Paths . To read from a SharePoint list, collections are the easiest choice. For more info on resource paths, you can check out the tip Using the OData Source in SQL Server Integration Services . Click OK to close the editor. With a data viewer, we can inspect the data coming out of the SharePoint List:


Reading SharePoint Lists with Integration Services 2017
There are two

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

tags: SharePoint,In,SSIS,column
分页:12
转载请注明
本文标题:Reading SharePoint Lists with Integration Services 2017
本站链接:https://www.codesec.net/view/578046.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(31)