Contents
1 - Use Case
Hi,
How are you doing?
I have been looking into some of the connectors in Power Automate that I haven't used many times before. I came across the SQL Server connector, and it reminded me of my days back in another job where I was using SQL all the time and how much I enjoyed it. So, I thought I would write a post on how you can get setup a SQL Server, connect to it via Power Automate cloud flow and then pass some information downstream to a Power Automate Desktop flow. This blog post will go into this in detail.
1 Use case
We want to be able to add a row to a SQL database table which triggers a Power Automate cloud flow, that in turn passes data through a Power Automate Desktop flow to an Excel spreadsheet stored locally on our computer.
2 Installing SQL Server Express
For this blog post, you are going to need to download SQL Server Express. You can do this by going to the following link:
On this page, scroll down and download the Express version
Once this has downloaded, install it. You can just go with the basic settings for now and let SQL Server Express install:
Once this is installed you can then go on to configure SQL Server 2019:
When asked to install components, just go with these:
Next up is Instance Configuration, this will be the name of your SQL Server, I have called mine SQLEXPRESS as per the screenshot:
Next, is authentication and how to access the SQL server, for this choose "Mixed mode" and provide a password for the system administrator account (SA). You will also notice your windows user is listed in the "Specify SQL Server Administrators" section. This allows you to logon to this server with your windows credentials:
Once this is installed, we then need to install the SQL Server Management Studio from here:
Click on the "Free download for SQL Server Management Studio (SSMS)" link on this page:
3 SQL Database Setup
Now that we have installed SQL Express 2019 and SQL Server Management Studio SSMS), we can open SSMS and create our first database and table.
From the Windows Start button, locate SQL Server Management Studio under Microsoft SQL Server Tools 18:
When SSMS runs, you will be asked to logon to the SQLEXPRESS instance that we created above, use your windows credentials to do this.
Right click on the Databases in the Object Explorer and click New database
Give the database a name, i.e. Testing, and click OK.
You will now see the Testing Database listed in the object explorer pane:
Expand the Testing database, and expand Tables, then right click and select new Table:
We can now create the columns inside the Table inside the Testing Database, I am building a very simple database for this example, it will have the following fields:
ID
First name
Last name
Date of Birth
Date created
We want the ID of the table to be the primary key. Select the field and then right click, and choose "Set primary key":
First name and Last name have a data type of varchar(50) this allows for a string of up to 50 characters in length, this is enough for both first name and last name.
I also want to record the date of birth, I have selected Date as it's data type.
For Date created, I want this to be automatically populated when the record is created with today's date and time. Select the column, and then in the Column Properties tab at the bottom of the screen, go down to the Computed Column Specification section, and inside the text field to the right of (Formula) enter
SYSDATETIME()
This will automatically populate the system date and time in this field when the record is saved.
Finally, as part of this table, we want the ID field to be automatically populated on save. We can do this, by clicking on the ID field and again in Column Properties, scroll down till Identity Specification and change the drop-down value of (Is Identity) to Yes:
Now, when we add a record, the ID will be saved automatically starting with 1.
Click the Save button and give the table a name, I am calling mine tblTesting, click OK and then right click on the Tables section in the Object Explorer and our table is there:
4 Add Data to SQL Table
Select the dbo.tblTesting table from the Object Explorer and click on Edit top 200 rows. We are taken to the table with no data present.
Add a record, inputting first name, last name and date of birth and hit enter. Note how the ID field has saved a "1" for its value and the Date Created is the system's date and time.
Awesome ! We have now completed the creation of the table and added our first row of data.
5 Allow remote connections to the SQL Database
For us to be able to write to the table, we need to be able to enable the database to allow remote connections.
To do this right click on the server in the Object Explorer and click Properties. From here click on the Connections section and ensure that the "Allow remote connections to this server" is checked:
6 Firewall Inbound Rule
If you are sitting behind a firewall, then you will need to configure a new inbound firewall rule. There is a good blog post on how to do that here.
7 On-Premise Data Gateway
As we are connecting to an on-premise SQL server instance, we need to install the on-premise data gateway. You can get this from here,
Follow the steps to install this, it is very straightforward.
TOP TIP:
You must make sure the gateway you install is in the same Region as your Power Platform environment, or you will not be able to see the on-premise gateway listed.
8 SQL Server Connector in Power Automate
Jump over to https://flow,microsoft.com and lets create a new Automated Cloud Flow, give it a name and search for the SQL Server connector, choose the "When an item is created" trigger and click create:
If it is the first time of you using this trigger action, you will be asked to choose the Authentication type of how we are connecting to the SQL server, choose Windows Authentication
We are then asked to complete the details for the connection to the SQL server:
Ignore if any error messages come up while you are completing the details for this connection. The SQL server name and Username have been hidden in the screenshot below.
The SQL Server name can be found by right clicking on the top of the tree in the Object Explorer and clicking properties, you will then be able to copy and paste the Name value into this field
The SQL database name is the one we set up earlier - Testing. Then enter your windows credentials, including domain name, and finally ensure your on-premise gateway application is running and signed in and choose your Gateway name (I have hidden this name in the screenshot). Once all the fields have been completed the Create button will be activated, click it.
We have now created the connection to the SQL server, choose our server name, database name and table name from the drop down
9 Power Automate Desktop
For this example, we are now going to create a Power Automate Desktop flow that will take the information from the newly created record and update a row inside an excel spreadsheet. This is what we are doing in this example, you could easily expand this to pass this information to a legacy system if you wanted to.
First of all we need to create a spreadsheet to store the new record in, to do this, open up Excel and create columns for each of the columns we have created inside our SQL table. Once you have created them, format the spreadsheet as a table. It will look something like this:
Save the file as Test.xlsx and save it in C:\Testing.
Open Power Automate Desktop, and ensure you are in the same environment as your Power Automate Cloud Flow that you have just created
Click on New Flow and give it a name, I am calling mine Testing.
First of all, we want to launch excel and open the Test spreadsheet we just created.
Search for Launch Excel in the Actions on the left-hand side and then drag and drop that step to the main Power Automate Desktop canvas:
Choose "and open the following document" in the Launch Excel drop down and point to the Test.xlsx document inside the Document path. Switch the "Make instance visible" to off.
Click save, the step is added to the canvas.
Next, search for Get first free column/row from Excel worksheet in the actions list. Leave the options exactly as they are and click save.
This step looks at where the first row and column that have no data is in our spreadsheet, we need to know this as we need to know what row to add our new line of data to:
Next, search for Insert row to Excel worksheet. Here we are going to insert a row into the first free row
After this, we need to start adding a row to the spreadsheet, to do this though we need to initialize Input variables for each column of the data coming from SQL.
On the right-hand side click the plus button inside the "Input/output variables" and then click on Input.
We now need to add an input variable for each of the columns in the SQL table. Input variables are what are passed from the parent cloud flow to the child desktop flow, more on that later. We need to create an input variable for each of the following:
ID
First name
Last name
Date of birth
Date created
Once they have all been created you can then find a Write to Excel worksheet action and write each of the column values to the columns inside your spreadsheet and choose the FirstFreeRow variable for the row. This will add this data to this row.
We then need to copy and paste this step for each of the columns, your Power Automate Desktop flow will look something like this:
We have finished writing data to the excel file now, so we need to save the file. Search for Save Excel and drag and drop it to the end of the steps on the canvas.
Finally search for close excel and drag and drop that to the canvas, the Power Automate Desktop flow, now will look like this:
Click save and then close the window.
10 Updating Power Automate Cloud Flow
Go back to the Power Automate cloud flow and click New Step. Click on the Desktop flows action and select "Run a flow built with Power Automate for desktop"
Ensure your on-premise gateway application is still running, and then if you are connecting to Power Automate Desktop for the first time, choose the gateway you setup above and provide your domain and username plus your password:
Click Create when you have provided all the information
Then select the Testing Desktop flow, choose Attended for the run mode, then match up the input variables the desktop flow is asking for from the Dynamic content fields from the SQL step above.
This step should look something like this.
Ensure the spreadsheet is closed.
Then go back to SQL Server Management Studio, and add a new record to the table, the cloud flow will then run, and Power Automate Desktop will add the record to the table:
