Inserting data in On Premises SQL Database using Logic Apps

byMaheshkumar Tiwari •1:33 PM

2

{tocify} $title={Table of Contents}

Introduction

If you are a BizTalk developer and get to know about any other integration tool/offering, and if you have to evaluate that - the first couple of things that would come in to your mind is(at least in my mind 😏)
1. How to route file from one location to another
2. How to perform CRUD operation on database

Thus, I thought of creating a simple Logic app which will receive a Product information and insert into table, but the table is part of database which is on premises.

Following are needed to do this
1. Azure Subscription
2. On premises machine with database to be used
3. On Premises Data Gateway installed on the on premises machine and registered on Azure

Now lets create the Solution

Create a database and a table

To keep it simple have created a DB called DemoDb with table having three columns

Install,Configure and start OPDGW

If installation and configuration of OPDGW is done then make sure the OPDGDW service is started

If not done already then follow steps to install and configure covered in previous blog post- http://tech-findings.blogspot.in/2017/06/installing-and-configuring-on-premises-data-gateway.html

Create Logic App

Open Azure Portal, sign-in with your account and on your left side, click in New -> Web + Mobile -> Logic App
Provide Name, create/use existing Resource Group, location and click on Create then on designer blade select Blank Logic App template

Now add a Request/Response Trigger - Logic Apps always start with trigger, as in BizTalk Message creates instance of Orch likewise trigger creates instance of LA

Type Request and select Request/Response from the connectors list, as you see below this connector as 1 Trigger and 1 action associated with it

The Request expects us to define the JSON schema for the request message intended to be received and the method. As BizTalk works internally on XML , Logic app works on JSON.

configure request/response trigger actions

I haven't provided any schema(thus accepting all valid JSON) but it should be done. Once you save the configuration , the URL of the logic app will be created automatically

save after configure request/response trigger actions

You can copy the url, we would need it to invoke this Logic app later. So the first step is done, now click on next step - select Add an action

After receiving the request message, next action is to insert in SQL thus look out for SQL connector

Unlike Request/Response connector which had 1 Trigger associated with it, SQL connector does not and has only actions - seven actions are available as of now. As we intend to insert record in SQL table - select Insert Row action

As this is first time SQL connector is used, we need to create a connection to SQL and for it Logic app will use OPDGW thus select Connect via on-premise data gateway- provide ConnectionName and details of SQL Server,DB and authentication details.(This connection can be reused if operation on same table is to be performed anytime further).

Once connection is established, all the tables under DemoDb will be autopopulated. For now only one table is available so that is available - Product table, select it.

All the columns from the table get available with blank textbox against it to contain the value to be inserted

As in BizTalk Orchestration the received message is available for all following shapes likewise in logic apps the Body of the request trigger is available. But we want to have dedicated values for the columns, for that we need to explicitly let logic app know where to pick values from the body, for that we switch to code view

Use @{triggerBody()?} to get access to the JSON created in the previous step, then navigate through your structure to find the variables(Description, ID and Price).

Going back to the Designer View, click in Designer and now see that the Workflow has identified these values as coming from the request

That's it, logic app is ready to accept request and insert row in table.

Test Logic App

To test the logic app we can use a Postman or ARC or any other client. I have used ARC.

We can check the status of the trigger and logic app on the portal

And finally, we need to check the DB if entry is made

Error you might encountered

You might get below error while configuring the SQL connector if your On premises data gateway service is stopped or the machine hosting it isn't available over the network or is shutdown