Introduction
If you are a novice to the Azure Cloud and want to learn what a logic app is, then go through my previous articles.
- An Overview of Cloud And Azure Cloud
- Pillars of Cloud And Azure Services
- How To Store File In SFTP Using Azure Logic App
Before you go through this article, it is recommended to go through below parts of this section,
- Overview of Azure on-premise data gateway
- Install and configure Azure on-premise data gateway
In this article, we will learn how to connect on-premise SQL Server from Microsoft Flow using on-premise data gateway.
Scenario
This article will help you to understand how to use Azure on-premise gateway with the help example - connect on-premise SQL server from Microsoft Flow, step by step.
Pre-requisite
- Basic knowledge Azure services
- Basic knowledge of on-premise data gateway
- Install and configure on-premise data gateway
- Office 365 account with flow subscription enabled
Background
We have got an overview of Azure on-premise data gateway and how to install/configure the same in the last two parts.
In this article, we will be exploring how to use this gateway to insert a row in on-premise SQL server table from Microsoft flow.
Overview
Let’s ready and gear up to execute the steps.
Step 1
Ready with SQL server table with few columns like Id, Name, Flag.
Step 2
Check on-premise gateway is in online and ready to use.
Step 3
Open and login to Microsoft flow portal
Step 4
Create connections in Microsoft flow.
Click on connections option present under data category.
Step 5
Click on ‘Add connection’ option
Step 6
Search for ‘SQL server’ and click on Create Link.
Step 8
Select option connect using on-premise data gateway. Also enter, remaining parameters like SQL server name, database name, its authentication type, select gateway. Then, click on the Create button.
Note
System automatically populates a list of gateways which are present for your account.
Step 9
Once your connection is ready then it shows as ready in Microsoft flow connection list.
Step 10
Till now, our SQL server, gateway and connection to the database from Microsoft Flow is ready. Now, we will focus on creating a flow to insert a row to this connected database.
Click on ‘My Flows’ and then click on ‘Create from blank’ option.
Step 11
Search for trigger ‘When HTTP request is received’.
Note
You can select trigger as per your need.
Step 12
Click on a new step.
Step 13
Search for SQL server action and then select action ‘Insert a row’.
Step 14
Now, the system will automatically populate the list of tables. It means we have connected on-premise SQL server from Microsoft flow.
Select the appropriate table to insert a row.
Step 15
Once you select table then system populates all columns as a field in the table. Enter data in the text box to add it as a row.
Step 16
Click on save button to save flow
Step 17
Flow is ready to use now. To test this flow, send an HTTP post request to flow.
How to get it HTTP request URL?
Go design of flow and copy ‘HTTP POST URL’,
Step 18
I am using postman tool to send a request to flow to run flow.
Step 19
Go to flow again and check history to verify flow successfully ran or not.
Step 20
Now, verify record is created or not in on-premise SQL server table.
That's it. Our entire set-up is ready to use now.
Summary
In this article, we have learned how to use Azure on-premise gateway, how to create a connection in Microsoft flow and how to create Microsoft flow to connect to on-premise resources like SQL server from Azure cloud.