The Problem

Last week I was asked to retrieve a number of rows from SQL Server (on-prem). These rows have to create a site on SharePoint in real-time. That’s where we bump into our first problem: getting real-time rows from an SQL Server. Unfortunately, it is impossible to use this trigger for a SQL Server on-prem. As explained in this blog post: https://flow.microsoft.com/en-us/blog/introducing-triggers-in-the-sql-connector/. In consultation with the end-users, we have agreed on a certain period of time during which we will pick up the rows.

After solving the retrieval of the rows, we collide with the next problem. Making a date filter to pick up the rows that were made during the last period. It was my very first time I implemented a date filter in flow. After a while, I managed to solve the puzzle. I want to share my solution in this blog post because I don’t think it’s the easiest solution to figure out.

The Flow

Step 1 – The Interval (here 5min)

To avoid the problem with the trigger, we will use the ‘Recurrence’ trigger in combination with the ‘Get paste time’ action.
This way we get every 5 minutes, the time of 5 minutes ago. The data in the ‘Created on’-field will have to be greater than the calculated time.

Step 2 – Get the rows out of SQL Server

In this step, we are going to retrieve the rows from SQL Server. These must be filtered based on the syscreated field which has DateTime as a datatype.

It is obvious that we are going to use the ‘utcNow()‘ function? This function is included in Flow by default and provides us today’s date. But here is the catch, the formatting of both DateTime fields does not match. I used different formatting for the ‘utcNow()‘, but none of the results were positive.

So I decided to change tack. Instead of using the full DateTime as a filter, take a look at both parts of the field itself, the date and the time part. This is the solution that worked for me:

Filter Formula:

date(syscreated) ge date(@{utcNow()}) and time(syscreated) ge time(@{body('Get_past_time')}))

Note: syscreated is the DateTime field from the SQL Database.

Step 3 – Create a SharePoint Site

In this last step, we are going to create a site with the data we extracted from SQL Server. The body statement, provided with parameters will fill in the following information on SharePoint:

  • displayName: This is going to be the site and group name.
  • alias: This will provide the Url of the site (example test.sharepoint.com/sites/alias
  • isPublic: This parameter will set the group visibility to ‘Public’ (if isPublic=true) or ‘Private’ (if isPublic=false)
  • Description: This will be the site description.
Afbeeldingsresultaat voor first post ever

So this was my first blog post ever! I hope you enjoyed reading. I’ll see you later for a new post!