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.
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.
Assuming that you are already familiar with SharePoint Online list creation, I’ve gone ahead and created the following sample lists:
- Players Booked
- MatchDay1
- MatchDay2
- MatchDay3
The lists are currently empty and a preview of their structure is shown in Figures 2 and 3.
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.
We next select the SharePoint connector – Microsoft Flow connectors are almost equivalent to Control Flow Tasks in SSIS.
Within the SharePoint connector, we next select the SharePoint – When an item is created trigger
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.
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.
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.
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.
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).
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.
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.
We can always verify a flow’s execution by looking at its run history as shown in Figure 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.
Additional Microsoft Flow configurations
Use Gmail for flow notifications
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.
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.
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.
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.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu