Power Automate for desktop provides the ability to automate databases through the database actions. In this article I will be exploring the Database actions functionality by connecting to a local SQL database.
First, we will start by setting up a local SQL Server Database.
Setting up a local SQL Server Database on Windows
For the purposes of this article, we will download and install the free Developer edition. SQL Server 2019 Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment. You can easily upgrade to a paid version from the Developer edition for production use.
SQL Server 2019 Developer is a full-featured free edition, licensed for use as a development and test database in a non-production environment.
To connect to the SQL Server using the Microsoft SQL Server Management Studio:
Launch the Microsoft SQL Server Management Studio.
Enter the information for the Server name (I used localhost), Authentication (SQL Server Authentication),user name and password, then click in the Connect button to connect to the SQL Server.
Connect to the SQL Server using the Microsoft SQL Server Management Studio.If the connection is successful, you will see a green icon in the Object Explorer.Right Click on the Database and choose New Database… from the menu. Enter the name of the database e.g., ExampleDb and click the OK button.Right Click on the Tables and choose New > Table… from the menu.In the create Table view, add a column Name called ‘id’ this will be our primary key.Right click on the arrow left of the column ‘id’ and select Set Primary Key from the menu.On the bottom of the screen, you will find the Column Properties view. Click on the Identity Specification attribute to expend and then double click on (Is Identity) . This will set the Is Identity value to Yes, set the Identity Increment to 1 and Identity Seed to 1. Which basically will increment the id value automatically every time we add a new record to our table. Include any columns that you will be using to store data.At any point if you feel like you need to go back to your table and modify it, right click on the table, Design will take you into the table edit mode.
Create a SQL connection in Power Automate Desktop
To connect to a database, use the Open SQL Connection action. This action only requires one input: the connection string. The Connection String field specifies all information that is necessary to connect to a database, such as the driver, the database, server names, and the username and password.
SQL actions require a database connection. When the action connects to a database, it stores the connection into a SQL connection variable. To connect to a database, enter the string manually or as a variable.
We will use the connection string wizard. Select Build connections string to open the Data Link Properties window. The data link tool helps the user compose the required connection string step by step.Once you access the wizard, select the correct driver for the database under the Provider tab.Next, under the Connection tab, enter the remaining details such as the server name, the username, password, and database name. Select the Test Connection button to test that the connection string connects to a database successfully.
Execute SQL Statement in Power Automate Desktop
For simplicity, right click on the table ‘dbo.Dogs’ in my exmaple and select Select Top 1000 Rows. This will generate a query that we can use to build the SQL Statement section in Power Automate Desktop.Querying id, lastName, firstName, age, breed from the Dogs tableI would like to write the data stored in the Dogs table to an Excel spreadsheet. Use the Launch Excel action.Use the Write to Excel worksheet to write values into a worksheet. Use the Close Excel action to save the document and close the excel spreadsheet. Use the Close SQL connection action to terminate the connection to the database. The SQL connection variable is required.Here is the full flow, it will save the excel file to the Document folder.
Microsoft Skilled 
Leave a Reply