Tuesday, June 27, 2017

Inserting Multiple Records In On Prem SQL Using Logic App


In last post we saw how to Insert a single Record in SQL Table which is on Prem using Logic App Inserting data in On Premises SQL Database using Logic Apps

Here I intend to walk through the process of inserting multiple records using single Logic App, to do this we have two options we can choose from 
1. Split On 
2. For Each

So either we debatch/split the Request message and insert in table or loop through the array of records and insert in table, here I am using the later method.


In earlier post we had single record  thus input was -
        "Description": "Edifice",
        "ID": "123",
        "Price": "3424"

Here we want multiple records thus we need to create an array of records as following-
        "Description": "Edifice",
        "ID": "123",
        "Price": "3424"
        "Description": "Edifice1",
        "ID": "1231",
        "Price": "2424"
        "Description": "Edifice2",
        "ID": "1232",
        "Price": "2344"
        "Description": "Edifice3",
        "ID": "1233",
        "Price": "2324"

Now let's create Logic App 

I have covered creating instance of Logic App  in last post so will directly go to creating Logic App,first step is to add the trigger to start the LA, a HTTP endpoint 
Request Trigger
The trigger is also supposed to accept Request message and for that there is provision to provide a schema for it (it is optional), in last post I haven't provided schema, here I intend to show the use of providing it. Click on Use Sample payload to generate schema, in the popped up editor provide the sample payload and click ok.
provide sample for schema

I’ve used the same payload as a sample message to generate the JSON schema.The JSON schema validates the incoming request and is useful for helping subsequent workflow steps know which properties to reference. Just like disassembler in BizTalk promotes properties here Trigger does that based on schema defined.
for each action

Add next action as For each, and as you can see above , Products is available as parameter to be used in For Each. Glad to see that apart from  Body(which is available to all ), Products which is an array is only available(which is valid parameter)

for each action setting

Select the Products and click on Add an action and add SQL connector . As am using same table used in previous post I don't have to specify connection details again rather would use same connection. 
select Sql action

Select Insert Row, you will see Product table gets available in dropdown list. Here if you see the properties which are valid for columns are available to choose from (Apart from Body) and this is because we have defined schema.
Insert row action

Assign appropriate properties against column and below is how it should look
for each settings

That's it, Logic app is ready for the task

Complete Logic App


For testing I have copied the URL (which you get on Request Trigger after save) and used ARC tool to send request- data is same which I used to create schema
Arc tool
On checking the LA run history, trigger was successful and LA too.

Logic App run history

If you go one level deep , you would see For Each step has four Insert Row within it(in sample four records were used) all successful(click on Next to see status of next Insert row action) 

for each run history

 Finally we need to verify the entries made in DB

multiple records entered in SQL

 As can be seen above all four records have made entry in table.

Learn More about Logic App

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

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

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

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.

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

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

Thursday, June 22, 2017

Installing and Configuring On Premises Data Gateway - By adding user to Active Directory


This post is intended to walk-through the process of  Installing and Configuring On Premises Data Gateway when you don't have work account (But you have a Microsoft account)

It is stated that Microsoft account can't be used to configure/register OPDGW, instead you need to use school account or work account. So does that mean a person not having work/school account cannot explore/use OPDGW- Well there is a way, you add a user to Active Directory.

Let's see how we do it

1. How to add user to Active Directory

2. Install OPDGW on local machine
3. Configure OPDGW on machine
4. Register OPDGW on Azure Portal

How to add user to Active Directory

  • To add user to AD, login to portal and search for Active Directory
Select Active directory
  • Click on Acive Directory and Under Quick Links - select Add a user

  •  Before adding user, remember you have to use following pattern
Say your Microsoft account is - xyz@gmail.com then new user should be something@xyzgmail.onmicrosoft.com, the user must have an extension of onmicrosoft.com
On Premises Data Gateway create user
Out of curiosity, tried to test validation - it is in place. Error is reported if pattern is not followed

On Premises Data Gateway create user error

  • Now we need to make the user as Global Administrator - go to All Users and select the user added in above step

On Premises Data Gateway select user

Click on Directory role and select Global administrator in adjacent bladeOn Premises Data Gateway adding user as global admin
  • Now next is to Reset the password the newly created user, you can use this password to login to portal, but you will be prompted to change it at first login (copy this temporary password as this would be needed later )
new user reset password
  • Next, is to add the above user as Owner so that the user shares same subscription. Thus all the things done by/under this user will use your subscription.  
Go to your subscription --> Access control(IAM) and add the Role as Owner and select the user created aboveOn Premises Data Gateway adding user as owner

  • In the notification, you should see following
                    notification in Azure portal
  • Now final step with user, setting the password. Login to the azure portal with the new user created and you will get prompted to change the password                                    update new user password

Install OPDGW on local machine

On Premises Data Gateway download
  • Run the installation 
On Premises Data Gateway installation program on local machine

On Premises Data Gateway installation start on local machine

On Premises Data Gateway installation path on local machine

On Premises Data Gateway installation done on local machine

Configure OPDGW on local machine

  • Here we need to enter the User we created in step 1

On Premises Data Gateway installation done  on local machine

provide work account

If cookies are not enabled then you might see next screen 

cookies error

  • After you enable cookies, you should be able to continue. Provide the Name to the OPDGW to be setup and Recovery key

On Premises Data Gateway configuration  on local machine
Here if you see, the setup wizard choose the nearest Azure Region as West India based on proximity of the location (but it can be changed, recommended to choose the closest one)

On Premises Data Gateway configuration done on local machine

Now OPDGW is ready to use, it can be used by Logic App, Power BI, PowerApps and Flow.

On Premises Data Gateway Service on local machine

You can also verify on local machine too, go to services and you can see the service as can be seen above

Register OPDGW on Azure Portal (associate to new user created)

  • Login to Azure portal with the newly created user and search for On Premises Data Gateway
  • Click on Add to register the gateway you installed on local machine
  • Provide Name, Subscription , Resource Group. You will see that the installed OPDGW is already available in the drop-down list (So all the OPDGW which are configured with the current user will be populated)

Register On Premises Data Gateway

  • Click on Create , registration is done and now it should be available to use in the supporting Azure services
  • Just a small pointer (below) while giving name few characters are not supported as of now

Characters not allowed in Name of On Prem Data Gateway

Learn More about Logic App