odata_如何为SharePoint Online配置OData SSIS连接

odata

As data warehouse developers, we often have to extract data from a myriad of source systems. Thus, whilst some source systems readily integrate with our ETL tools there are instances whereby we need to install additional drivers and software addons in order to successfully connect and extract data from other source systems. Microsoft SharePoint Online is one such source system that I recently had to extract data from and its connectors are by default not part of the standard SQL Server Integration Services (SSIS) package template. As SSIS developers we often don’t have solid background on environments such as SharePoint, thus figuring out which version of SharePoint Software Development Kit to install in order to enable successful connection from SSIS can sometimes be a frustrating experience. In this article, I try to alleviate some of that frustration by sharing some of my recent experiences relating to getting data out of a SharePoint list using SSIS.

作为数据仓库开发人员，我们经常不得不从无数个源系统中提取数据。 因此，尽管某些源系统可以轻松地与我们的ETL工具集成，但在某些情况下，我们需要安装其他驱动程序和软件插件才能成功连接其他源系统并从中提取数据。 Microsoft SharePoint Online是这样一种源系统，最近我不得不从中提取数据，并且其连接器默认情况下不是标准SQL Server Integration Services（SSIS）程序包模板的一部分。 作为SSIS开发人员，我们通常对SharePoint等环境没有扎实的背景，因此弄清楚要安装哪个版本的SharePoint软件开发工具包以实现与SSIS的成功连接有时会令人沮丧。 在本文中，我试图通过分享一些我最近使用SSIS从SharePoint列表中获取数据的经验来减轻这种沮丧。

第1部分：SharePoint列表作为数据源 (Part 1: SharePoint List as a Data Source)

We begin by looking at sample SharePoint environment that will be used as a data source and identifying a list of URL addresses that we will need in our SSIS package. Figure 1 gives a preview of the data contained within our sample ApexSQLFree SharePoint list – basically a list containing SQL addons produced by ApexSQL and licensed to the public for free.

我们首先查看将用作数据源的示例SharePoint环境，并确定我们在SSIS包中所需的URL地址列表。 图1预览了示例ApexSQLFree SharePoint列表中包含的数据-基本上是一个列表，其中包含由ApexSQL生成并免费授权给公众SQL插件。

The first URL address to take note of, is the link to your SharePoint list, in my case that will be the web address to the ApexSQLFree SharePoint list, which is as follows:

要注意的第一个URL地址是指向SharePoint列表的链接，在我的情况下，它将是ApexSQLFree SharePoint列表的网址，如下所示：

• https://mydomain.sharepoint.com/sites/SPSDemo/Lists/ApexSQLFree/sample.aspx

  https://mydomain.sharepoint.com/sites/SPSDemo/Lists/ApexSQLFree/sample.aspx

Obviously, assuming that you have a basic understanding of SharePoint, by looking at the URL address itself you would have noted the following:

显然，假设您对SharePoint有基本的了解，那么通过查看URL地址本身，您会注意到以下内容：

• mydomain is a placeholder for an actual domain; mydomain是实际域的占位符；
• SPSDemo is the name of the SharePoint site that this list is stored; and SPSDemo是此列表存储的SharePoint网站的名称。 和
• ApexSQLFree is the name of the list. ApexSQLFree是列表的名称。

We can use the SharePoint URL to derive a REST API web address that will be suffixed with listdata.svc. It is important that we get a *listdata.svc URL as we will need it for establishing connection to the SharePoint list using the REST API in an SSIS package. What worked for me in terms of deriving the *listdata.svc URL was appending /_vti_bin/listdata.svc just after the site name in the above SharePoint list URL such that it becomes the following:

我们可以使用SharePoint URL派生一个将以listdata.svc为后缀的REST API Web地址。 获得一个* listdata.svc URL很重要，因为使用SSIS包中的REST API建立与SharePoint列表的连接将需要它。 就派生* listdata.svc URL而言，对我有用的是将/ _vti_bin / listdata.svc附加在上述SharePoint列表URL中的网站名称之后，使得它变为以下内容：

• https://mydomain.sharepoint.com/sites/SPSDemo/_vti_bin/listdata.svc/

  https://mydomain.sharepoint.com/sites/SPSDemo/_vti_bin/listdata.svc/

Obviously, you can always test the generated *listdata.svc URL by running it into a browser as shown in Figure 2.

显然，您始终可以通过将生成的* listdata.svc URL运行到浏览器中来对其进行测试， 如图2所示。

第2部分：SSIS开发–为SharePoint Online配置OData源 (Part 2: SSIS Development – Configuring OData Source for SharePoint Online)

Having identified the SharePoint list from which data will be sourced, we switch to SSIS and configure the necessary components for SharePoint data extraction.

确定了要从中获取数据的SharePoint列表后，我们切换到SSIS并配置用于SharePoint数据提取的必要组件。

1. Configuring the OData Source Connection Manager

   配置OData源连接管理器

   The first SSIS component that ought to be configured is the OData Connection Manager. This connection type is available under New Connection… option in SSIS’s Connection Managers tab as shown in Figure 3.

   应该配置的第一个SSIS组件是OData Connection Manager 。 该连接类型在SSIS的“连接管理器”选项卡中的“ 新建连接…”选项下可用， 如图3所示。

   
   Figure 3 图3

   By clicking on the New Connection… option, the OData Connection Manager Editor will popup.

   通过单击“ 新建连接…”选项，将弹出OData连接管理器编辑器 。

   Fill in the Service document location box with your *listdata.svc URL and on the Authentication Type dropdown, choose “Microsoft Online Services”. The Microsoft Online Services will further require that you specify a username and password. Finally, if everything has been captured correctly, clicking the Test Connection button at the bottom left of the editor should return a “Test connection succeeded” message as shown in Figure 4.

   使用* listdata.svc URL填写“ 服务文档位置”框，然后在“ 身份验证类型”下拉列表中，选择“ Microsoft Online Services ”。 Microsoft Online Services将进一步要求您指定用户名和密码。 最后， 如图4所示，如果一切都被正确地捕捉，点击左边的编辑器应该返回一个底部的测试连接按钮“测试连接成功”的消息。

   
   Figure 4 图4

2. Configuring the Data Flow Task

   配置数据流任务

   Having successfully created and tested our OData connection, we are now ready to begin data extraction and SSIS facilitates such an exercise through its Data Flow Task component. Within the Data Flow Task, we start off by configuring our data source component – which in this case will be OData Source as indicated in Figure 5.

   成功创建并测试了我们的OData连接之后，我们现在就可以开始数据提取了，SSIS通过其Data Flow Task组件促进了这种练习。 在“数据流任务”中，我们首先配置数据源组件-在这种情况下将是OData Source， 如图5所示 。

   
   Figure 5 图5

   Once the OData Source has been added into the Data Flow Task pane and its editor opened, under the OData connection manager label we select the OData connection we configured above. As indicated in Figure 6, such a selection will result in the rest of the boxes being auto-populated (except for Query options).

   将OData Source添加到“数据流任务”窗格中并打开其编辑器后，在OData连接管理器标签下，我们选择上面配置的OData连接。 如图6所示 ，这种选择将导致自动填充其余的框（“ 查询”选项除外）。

   
   Figure 6 图6

   You can use several query options in your OData source connection as outlined here. For instance, if you want to return a subset of data from your SharePoint list, you can use the $top query option as indicated in Figure 7.

   你可以在你的OData源连接使用多个查询选项列出这里 。 例如，如果您想从SharePoint列表中返回数据的子集，则可以使用$ top查询选项， 如图7所示 。

   
   Figure 7 图7

   For the purposes of showing you how the filtering works, I duplicated the components in the data flow tasks such that one section applies the $top query option and the other doesn’t. As indicated in Figure 8, the first flow retrieves only 2 rows compared to the other section that returns all (7) rows.

   为了向您展示过滤的工作方式，我复制了数据流任务中的组件，以便一个部分应用$ top查询选项，而另一部分则不应用。 如图8所示 ，与返回所有（7）行的其他部分相比，第一个流程仅检索2行。

   
   Figure 8 图8

第3部分：要注意的一些问题 (Part 3: Some Issues to Look Out For)

In the preceding section of this article, I demonstrated an end-to-end seamless configuration and extraction of SharePoint data using the OData source in SSIS without any errors. However, in real world it this hardly happens – you are likely to run into an error message or two. In this section, I have put down some of the errors that you should look out for when working with SharePoint Online and how you can go about fixing such errors.

在本文的上一节中，我演示了使用SSIS中的OData源进行的端到端无缝配置和SharePoint数据提取，没有任何错误。 但是，在现实世界中几乎不会发生这种情况-您可能会遇到一两个错误消息。 在本节中，我介绍了在使用SharePoint Online时应注意的一些错误以及如何解决这些错误。

• Issue #1: Failed to Load SharePoint Assemblies

  问题＃1：无法加载SharePoint程序集

  When configuring an OData connection, choosing the Microsoft Online Services authentication type can return the error message shown in Figure 9. To get around such an error, you will need to download and install SharePoint Online Client Components SDK – which is freely available from the Microsoft Download Center page.

  配置OData连接时，选择Microsoft Online Services身份验证类型可以返回如图9所示的错误消息。 要解决此错误，您将需要下载并安装SharePoint Online客户端组件SDK-可从Microsoft下载中心页面免费获得。

  
  Figure 9 图9

• Issue #2: Incompatible SharePoint Assembly Version

  问题＃2：不兼容的SharePoint程序集版本

  Despite having installed the SharePoint Online Client Components SDK, you may still run into another error message when attempting to test you newly configured OData connection. This usually occurs when you have installed SDKs for SharePoint Online instead of SharePoint on-prem, thus make sure that when you google/bing the SharePoint SDKs client components, that you install the correct ones.

  尽管已经安装了SharePoint Online客户端组件SDK ，但在尝试测试新配置的OData连接时，仍然可能会遇到另一条错误消息。 当您安装了SharePoint Online的SDK而不是本地的SharePoint时，通常会发生这种情况，因此请确保当您用Google搜索/查询SharePoint SDK的客户端组件时，安装了正确的组件。

  
  Figure 10 图10

  Table 1 gives a breakdown of common on-prem SharePoint servers and their respective version numbers.

  表1列出了常见的本地SharePoint服务器及其各自的版本号。

  SharePoint Version	Version Number
  SharePoint Server 2010	14.0.XXXX.XXXX
  SharePoint Server 2013	15.0.XXXX.XXXX
  SharePoint Server 2016	16.0.XXXX.XXXX

  SharePoint版本	版本号
  SharePoint Server 2010	14.0.XXXX.XXXX
  SharePoint Server 2013	15.0.XXXX.XXXX
  SharePoint Server 2016	16.0.XXXX.XXXX

  Table 1 表格1

  Furthermore, you can always verify the version installed by looking at the path: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\%version number%\ISAP where %version number% is a parameter for version of SharePoint, thus for SharePoint Server 2016 you should have a path as follows: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAP.

  此外，您始终可以通过以下路径来验证安装的版本： C：\ Program Files \ Common Files \ Microsoft Shared \ Web服务器扩展\％version number％\ ISAP ，其中％version number％是SharePoint版本的参数，因此，对于SharePoint Server 2016，您应该具有如下路径： C：\ Program Files \ Common Files \ Microsoft Shared \ Web服务器扩展\ 16 \ ISAP 。

• Issue #3: Data Conversion Error

  问题3：数据转换错误

  Finally, one of the frustrating error message you may encounter when extracting using SSIS to extract data out of a SharePoint list is a series of data conversion errors. This is because all string related fields from the OData Source component will be defaulted Unicode text stream data types (i.e. DT_NTEXT) as shown in Figure 11.

  最后，在使用SSIS提取SharePoint列表中的数据进行提取时，您可能会遇到的令人沮丧的错误消息是一系列数据转换错误。 这是因为来自OData Source组件的所有与字符串相关的字段将被默认为Unicode文本流数据类型（即DT_NTEXT）， 如图11所示。

  
  Figure 11 图11

  There are several ways to get around this error; you can add a data conversion transformation component and convert data as suggested in the error message. Alternatively, you can simply convert the destination output to a Unicode string by ticking the Unicode box as indicated in Figure 12.

  有几种方法可以解决此错误； 您可以添加数据转换转换组件并按照错误消息中的建议转换数据。 另外，您可以通过勾选Unicode框，简单地将目标输出转换为Unicode字符串， 如图12所示 。

  
  Figure 12 图12

摘要 (Summary)

SharePoint list is one of many data sources that as data warehouse developers can be required to extract data from. Unlike your typical data sources such as SQL Server or Excel, required drivers are often not readily installed in SSIS package template thus requiring a bit of work from your side in terms of identifying, downloading and ultimately installing the correct software addons. In this article we have demonstrated how data can be sourced from a SharePoint list and how you can go about dealing with several common issues.

SharePoint列表是许多数据源之一，随着数据仓库开发人员可能需要从中提取数据。 与典型的数据源（例如SQL Server或Excel）不同，所需的驱动程序通常不容易安装在SSIS包模板中，因此在识别，下载并最终安装正确的软件附件方面需要您付出一些工作。 在本文中，我们演示了如何从SharePoint列表中获取数据以及如何处理几个常见问题。

参考资料 (References)

• OData Connection Manager OData连接管理器
• SharePoint Server build numbers SharePoint Server内部版本号
• Using the SharePoint REST Interface 使用SharePoint REST界面
• OData system query options using the OData endpoint 使用OData端点的OData系统查询选项

翻译自: https://www.sqlshack.com/how-to-configure-odata-ssis-connection-for-sharepoint-online/

odata