How To Delta Load From SharePoint To SQL Using Power Automate

This article explains how to copy data from SharePoint to SQL in a delta load manner

5 min readAug 18, 2020

data pipeline; power automate; sharepoint to sql

This article will guide you through steps needed to implement delta logic to copy data from SharePoint to SQL with Power Automate. Before we start, I assume that you, my dear reader, know how to build a simple version of this data pipeline. If not, don’t worry. I got you covered. This article explains just that. On to the steps now:

Task:
1. Construct a Data Pipeline between SP and SQL.
2. If the DB is empty, copy the entire data set from SP.
3. If the DB is not empty, copy ONLY the unique set of records each day.

Step 1: Select an Automated flow that we will create from a scratch

Step 2: Select a schedule as a trigger

This is helpful as data pipelines are expected to work day-after-day without fail. A scheduled trigger helps us to customize the time frame under which the data pipeline will get executed.

Step 3: Select Get Items (SP) as an action

Get Items will read the data from the SP list we specify.

Step 4: Select Get Rows (SQL) as another action

Get rows will read the data from the SQL table. Since one of the conditions in the task is to know if there is existing data in the table or not, this action will be used to determine exactly that.

The pipeline should look like below:

Step 5: Select Condition as an action

This step is crucial for the pipeline to work. The action (Get rows) will read the data from the SQL table into an array. If there is no data in the array, it should be blank and if there is, the data will be shown in a JSON format. Now, a simple programming logic- how would we determine if there is any data in an array? You calculate its length.
Select expression tab as shown below:

The array containing data from SQL table is named value(list of items). We need to calculate its length. In the expression tab, type length(). Switch back to Dynamic content and select value(list of items) to be inserted inside the parenthesis. You will see the expression in the field and the overall condition as shown below:

The Yes condition is pretty much taken care of. If the length of the array is 0, the flow will execute the Yes condition wherein it will loop over all the values from the SP list and transfer them into an SQL table.

If the length of the array is not equal to 0, now you need to identify which records are unique; this essentially implies identifying records not present in the SQL table. In order to do this, we pick Employee number as the column whose values will be compared with a similar column from the SQL table. If there’s a match, the record is not unique. Otherwise, it is a new record.

conditional statement in power automate; using if else in power automate

Doesn’t look so pretty now, does it? That is because we have used a loop inside another loop. For the comparison to begin, we will loop over the values array from SP. Then, we will loop over the values array from the SQL. Afterwards, the condition will calculate whether the Employee number is equal to Client ID (assuming this has similar employee number data). If the result is Yes, we chose to do nothing. You can perform other activities such as “sending an email” or “raising a flag” but for this task, we will keep it as blank.

If the comparison result is No, we will do exactly what we did with the original Yes result of the condition; loop over the SP list and copy data from SP list to SQL.

The overall data pipeline looks like this:

power automate transfer data from SharePoint to SQL

Drilling into the conditional statements, the flow looks like this:

power automate transfer data from SharePoint to SQL; using if else condition

To prevent this, we will introduce a variable that will keep track of a new record. When a comparison will happen, the variable will be updated based upon the result of the comparison.

using variable in power automate; initialize variable in power automate

We have introduced a new variable- new record. Its initial value has been set to True. The new data pipeline now looks like this:

power automate/ms flow copy data from SP to SQL

continued below…

A Small Surprise

Now since you have had a hands-on experience with Power Automate, believe it or not, you have also learned how to build a data pipeline through Microsoft Azure Logic Apps. This is because the functionality in both the tools for the most part, if not for all of it, is exactly similar…. So yeah, you now know how to work with Azure Logic Apps as well.

An example of how it looks in Azure Logic Apps: