Saving Microsoft Forms responses to Azure SQL with Power Automate

One of our customers was facing the issue of how to save responses from Microsoft Forms to Azure SQL database. The initial idea was to go with Google Forms and Zappier but we have recommended the use of Microsoft Forms to stay inside the Azure environment instead of being split across various service providers. The main part of the solution was realized with Power Automate Template available in the Templates library. I will show you click thru demonstration on how to make simple form and save responses directly to Azure SQL Database.

Let’s go to Microsoft Forms and build such a simple form as in the picture:

Go to Power Automate and search for “Store Microsoft Forms responses in a SQL Server Table” template:

Click on the template icon and on the next screen there is a simple description of the task. Below are two + buttons to set connections to your Microsoft Form you will receive responses from and to SQL Server database and table where received responses should be persisted.

Setup the Microsoft Forms connection first. It’s easy: just click the + button and it will connect you to the Microsoft forms store which belongs to your account you have signed in. The connection will be marked with a green OK symbol.

Then press the + for the SQL Server connection and fill in the form. In case it’s Azure SQL Database the connection will fail for the first time saying that the client with the displayed IP address can not connect to the database. This means that the connection was blocked by the Azure SQL Server firewall and you should take the address and create a new firewall rule for it.

Once both connections are set properly you can continue in the wizard.

Prepare the table where form responses will be stored. First there columns in the table are special columns for the ID of the responses, who submitted the response, and when. The remaining columns are matching to form questions one by one. Copy the CREATE table code and run it in the target database you have connected above.

CREATE TABLE [dbo].[SampleFormAnswers](
	[ResponseId] [INT] NOT NULL,
	[SubmittedBy] [NVARCHAR](200) NULL,
	[SubmittedDtm] [NVARCHAR](50) NULL,
	[Question1_Choice] [NVARCHAR](500) NULL,
	[Question2_Text] [NVARCHAR](500) NULL,
	[Question3_Rating] [NVARCHAR](500) NULL
)
GO

Go back to Power Automate and continue the wizard. It’s quite intuitive. Select your sample form in the first section. “Get response details” section keep as it was prefilled automatically. The interesting part is coming in the “Insert row (V2)” task where you will configure the mapping between form response values and database table columns. Select the Server, database, and the table you have created in the previous step. Power Automate will automatically list all table columns and display them in the form.

Connect all columns to response properties as in the picture. If you will click inside the column box search box will appear on the right side of it and you can search for the proper form fields in it:

Once completed, press the Save button in the wizzard and that’s it.

It’s time to test if it works. Navigate back to your form and press the Preview button in the top panel. This will allow you to fill in the form. Fill it as in the picture to have some interesting variants of data in the table later:

Submit the form and you should see the approval message:

Execute SQL query against your database to see if the form response arrived there:

SELECT * FROM [dbo].[SampleFormAnswers]
GO

In case you have properly configured everything the response looks like as in the picture (with a different ID maybe). You can see more execution details if you will click on the flow in the Power Automate administration:

In case of the failure you will see the failed execution in the “28-day run history” section and you can observe error details. Errors are enough descriptive to fix them quickly. What is really amazing is the Duration: form response arrived in the database nearly realtime.

Few points to consider:

• See the Power Automate pricing. It’s comparable to Zappier or other services but with the advantage that there is no such 15 minutes synchronization limit.
• You can use STRING_AGG() function for multivalue answers parsing.
• It’s a question if the Power Automate IP address you need to grant access in the Aure SQL Server firewall won’t change in time? I’m not sure about that.
• SubmittedDtm column is easier to have a string. When it’s DATETIME the flow will fail with a casting error.
• When you will do some changes in the table it may be required to drop the last task in the flow (insert row) and add it again to refresh the columns list.