如何使用Microsoft Flow提取SharePoint Online列表数据

Data extraction is a pivotal part of any business process particularly when it comes to running reports and facilitating business decision-making. In the article, How to configure OData SSIS Connection for SharePoint Online, I covered data extraction off a SharePoint Online list using SQL Server Integration Services (SSIS). You would have noticed in the aforementioned article that getting SSIS to successfully integrate with SharePoint Online lists can be a laborious exercise, especially if you haven’t installed the correct SharePoint SDK files. Thus, in business environments where business and Power Users have more control of data extraction processes, SSIS could get complicated for an ordinary business user to operate. Therefore, given the nature of our source data and the platform in which it resides, ETL architects and developers alike may need to find alternative ETL tools to SSIS. This brings me to Microsoft Flow which could be one possible alternative to using SSIS for data extraction. Microsoft Flow is part of Office 365 applications and just like SharePoint Online, is a cloud-based application that is freely available, easier to operate and effortlessly integrates with – amongst other applications – SharePoint Online. The aim of this article is to demonstrate the convenience of extracting data from one SharePoint Online list to another using Microsoft Flow.

数据提取是任何业务流程的关键部分，特别是在运行报告和促进业务决策方面。 在“ 如何为SharePoint Online配置OData SSIS连接”一文中 ，我介绍了使用SQL Server Integration Services（SSIS）从SharePoint Online列表中提取数据。 您会在前面的文章中注意到，使SSIS与SharePoint Online列表成功集成可能是一项艰巨的工作，特别是如果您没有安装正确的SharePoint SDK文件。 因此，在业务和高级用户对数据提取过程具有更多控制权的业务环境中，SSIS对于普通业务用户而言可能会变得复杂。 因此，鉴于我们的源数据的性质以及它所驻留的平台，ETL架构师和开发人员都可能需要找到SSIS的替代ETL工具。 这使我进入了Microsoft Flow，它可能是使用SSIS进行数据提取的一种可能的替代方法。 Microsoft Flow是Office 365应用程序的一部分，就像SharePoint Online一样，它是一个基于云的应用程序，可免费使用，易于操作，并且可以与SharePoint Online等应用程序轻松集成。 本文的目的是演示使用Microsoft Flow从一个SharePoint Online列表提取数据到另一个SharePoint Online列表的便利性。

英超球员预订清单 (The Premier League player-booking list)

If you’ve ever read any of my articles on SQLShack, you would have picked up already that I’m indeed a great fan of Arsenal football club and the English Premier League in general. Therefore, it shouldn’t come as a surprise that my chosen business test case for demonstrating Microsoft Flow has something to do with the Premier League. Suppose the Premier League officials keep track of players booked for offences during matches through SharePoint Online lists. In such a scenario, every football match is allocated its own SharePoint Online list used to document information related to players bookings. At the end of all matches, Premier League officials then collect data from all matches into one consolidated SharePoint Online list called Players Booked as depicted in Figure 1.

如果您曾经阅读过有关SQLShack的任何文章，那么您肯定已经知道我确实是阿森纳足球俱乐部和英格兰超级联赛的忠实粉丝。 因此，我为演示Microsoft Flow而选择的业务测试案例与英超联赛有关并不奇怪。 假设英超联赛官员通过SharePoint Online列表跟踪在比赛中被定罪的球员名单。 在这种情况下，每次足球比赛都分配有自己的SharePoint Online列表，该列表用于记录与球员预订相关的信息。 在所有比赛结束时，英超联赛官员随后将所有比赛中的数据收集到一个合并的SharePoint Online列表中，该列表称为“预定球员”， 如图1所示 。

Assuming that you are already familiar with SharePoint Online list creation, I’ve gone ahead and created the following sample lists:

假设您已经熟悉SharePoint Online列表的创建，那么我继续并创建了以下示例列表：

• Players Booked
  预定球员
• MatchDay1
  比赛日1
• MatchDay2
  比赛日2
• MatchDay3
  比赛日3

The lists are currently empty and a preview of their structure is shown in Figures 2 and 3.

该列表当前为空，其结构预览如图2和3所示 。

基本的Microsoft Flow创建 (Basic Microsoft Flow creation)

Again, assuming that you have signed up and logged-in into Microsoft Flow, we begin by creating a flow from scratch by using the Create from blank option shown as per Figure 4.

再次，假设您已经注册并登录到Microsoft Flow ，我们首先使用如图4所示的Create from blank选项从头开始创建流。

We next select the SharePoint connector – Microsoft Flow connectors are almost equivalent to Control Flow Tasks in SSIS.

接下来，我们选择SharePoint连接器– Microsoft Flow连接器几乎等同于SSIS中的“控制流任务”。

Within the SharePoint connector, we next select the SharePoint – When an item is created trigger

在SharePoint连接器内，我们接下来选择SharePoint –创建项目时触发

We then redirected to the When an item is created mini-form wherein we provide the mandatory SharePoint Online site address as well the name of the list.

然后，我们重定向到创建项目时的微型表单，其中提供了必需的SharePoint Online网站地址以及列表名称。

Once we have defined the data source for our trigger, we next specify what should happen when the trigger executes. We do this by clicking New step > Add an action. Again, for this action, we choose a SharePoint connector but we select the SharePoint – Create item action type.

定义触发器的数据源后，接下来将指定执行触发器时应发生的情况。 为此，请点击新建步骤 > 添加操作 。 同样，对于此操作，我们选择一个SharePoint连接器，但选择“ SharePoint –创建项目”操作类型。

Again, we are redirected to a form that we need to complete in order for SharePoint items to be created. The form requires the name of the SharePoint Online site address as well as its list name. In this case, we will be inserting data into the Players Booked list.

同样，我们将重定向到我们需要完成的表单，以便创建SharePoint项目。 该表单需要SharePoint Online网站地址的名称及其列表名称。 在这种情况下，我们会将数据插入“已预订的玩家”列表中。

As per Figure 9, our destination SharePoint Online list had several fields that we needed to map. To choose fields to map from, we simply click on the empty box next to the field name (i.e. ClubName) and a pop-up window appears (usually on the right side of the page) with a list of available alphabetically-sorted fields that can be used for mapping.

如图9所示 ，目标SharePoint Online列表具有我们需要映射的几个字段。 要选择要映射的字段，我们只需单击字段名称（即ClubName ）旁边的空白框，然后会出现一个弹出窗口（通常在页面的右侧），其中列出了可用的按字母顺序排序的字段，可用于映射。

Alternatively, we can map source to target fields by using expressions. If you are familiar with Azure Logic apps then you would find writing expressions much easier as the syntax used in a Microsoft Flow expression is based off Workflow Definition Language (WDL).

另外，我们可以使用表达式将源映射到目标字段。 如果您熟悉Azure Logic应用程序，那么您会发现编写表达式要容易得多，因为Microsoft Flow表达式中使用的语法基于工作流定义语言（WDL） 。

Once we have mapped all the fields, we are ready to run our flow. Because the flow we created is event driven, we can only run it by capturing data in our MatchDay1 list to initiate the trigger. I have gone ahead and captured a sample player booking in the list as show in Figure 12.

映射完所有字段后，就可以开始运行流程了。 因为我们创建的流是事件驱动的，所以我们只能通过在MatchDay1列表中捕获数据来启动触发器来运行它。 我继续前进，并在列表中捕获了一个样本玩家预订， 如图12所示 。

As soon as I completed the capturing of the data, my flow recognised the changes in my MatchDay1 list and thus began replicating the captured data into my Players Booked list as shown in Figure 13.

一旦完成数据捕获，我的流程就会识别出MatchDay1列表中的更改，从而开始将捕获的数据复制到我的Players Booked列表中， 如图13所示。

We can always verify a flow’s execution by looking at its run history as shown in Figure 14.

我们总是可以通过查看流的运行历史来验证流的执行， 如图14所示。

Clicking on a run history item will further give you additional logs of steps that ran inside your flow and time spent executing flow controls.

单击运行历史记录项将进一步为您提供流程内运行的步骤的其他日志，以及执行流程控制所花费的时间。

其他Microsoft Flow配置 (Additional Microsoft Flow configurations)

Use Gmail for flow notifications

使用Gmail接收流通知

You would have noticed that when we selected the SharePoint connector, there were several other connector types available for selection. One connector that could be useful in our flow is Gmail, which we can use for notifications. We can setup notifications by configuring the Gmail – Send email action type and adding it at the end of flow steps as shown in Figure 15.

您会注意到，当我们选择SharePoint连接器时，还有其他几种连接器类型可供选择。 Gmail在我们的流程中可能有用的一种连接器，我们可以将其用于通知。 我们可以通过配置Gmail –发送电子邮件操作类型并在流程步骤结束时添加它来设置通知， 如图15所示。

Configure Retry Policy for basic error handling

配置重试策略以进行基本错误处理

It is possible that we may encounter timeouts and related intermittent failures when reading or writing data into our SharePoint Online lists. We can cater for such timeouts errors by configuring the Retry Policy property located under the Settings option in our flow controls.

在将数据读取或写入SharePoint Online列表时，我们可能会遇到超时和相关的间歇性故障。 我们可以通过配置流控件中“设置”选项下的“ 重试策略”属性来解决此类超时错误。

Add flow scheduling

添加流调度

Another option that we can consider adding into our flow could be the Schedule connector. Similarly, to the Job Schedule in SQL Server Agent jobs, we can configure the Schedule connector to run the flow at a specific interval.

我们可以考虑添加到流中的另一个选项是Schedule连接器。 同样，对于SQL Server代理作业中的作业计划，我们可以配置计划连接器以特定间隔运行流。

摘要 (Summary)

In this article we have demonstrated that through freely available tools such as Microsoft Flow, SSIS no longer needs to be your only hammer in your ETL toolbox, particularly when your data source is a cloud-based service such as SharePoint Online lists. Microsoft Flow gives you the flexibility to extract data based on triggers and actions that – amongst other things – could be rescheduled using the Schedule connector.

在本文中，我们证明了通过免费可用的工具（例如Microsoft Flow），SSIS不再是您在ETL工具箱中的唯一障碍，尤其是当您的数据源是基于云的服务（例如SharePoint Online列表）时。 Microsoft Flow使您可以灵活地基于触发器和操作来提取数据，这些触发器和操作尤其可以使用Schedule连接器重新安排。

翻译自: https://www.sqlshack.com/how-to-use-microsoft-flow-to-extract-sharepoint-online-list-data/