Sunday, June 25, 2017

Inserting data in On Premises SQL Database using Logic Apps

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


1. Create a database and a table 



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

Demo Db on premises





2.Install,Configure and start OPDGW 


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

on premises data gateway service







3. 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
create logic appcreate logic app blank 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
request/response trigger


  • 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

request/response trigger actions


  • 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
new step - new action




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


  • 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

SQL Connector Actions Insert row


  • 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).










SQL Connector  Insert row
  • 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.

SQL Connector Insert row table

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

SQL Connector Insert row table - body



  • 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 viewlogic app 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). 


logic app designer view



  • 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.


4. Test Logic App


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

ARC tool


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

logic app diagnostics


  • And finally, we need to check the DB if entry is made table after insert


5.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

opdgw error



Related Post


2 comments:

  1. The transactions are designed such that they either complete a task in its totality or produce no effect if a specific transaction remains incomplete.kpi dashboards

    ReplyDelete