Part 1: Real life hybrid scenario with Azure Logic App, SQL Connector, BizTalk XPath Extractor and File Connector with condition

I have been doing a proof of concept using Logic App and there has been some very good learnings which I am going to share in series of articles on Logic App.

Let me explain a simple real life scenario which I have been using for my POC. A supply chain management company called Indway limited receives order details from its supplier and maintains an on-premise SQL Database which gets updated when an order has been received. Then Indway routes the orders to its suppliers based on the quantity of the order. So for our simple scenario if the order value is greater than 100 it will route the message to another company called Fortuvis Systems Limited’s on-premise folder. Or if the order value is lesser than 100 it will route the message to JAC Enterprise. This hybrid scenario is shown in the below picture:

Logic App Scenaior

Because of the length of this article, I am going to split this blog post into two parts.

In Part-1, we will create required API apps (connectors and BizTalk XPath Extractor) and in Part-2, we will create a Logic App using the API apps created in Part-1 to orchestrate the message flow.

To achieve this scenario, we need to do following:

  • Create on-premise SQL Database.
  • Create an instance of SQL Connector API
  • Create an instance of BizTalk XPath Extractor API
  • Create an instance of File Connector API.
  • Create a Logic App to orchestrate the message flow.
  • Executing actions in Logic App based on conditions.

Create on-premise SQL Database:

I am not going into details of creating SQL server database, but I will highlight the points we need to consider creating on-premise SQL database for hybrid scenario.

When using Hybrid Connections to access on-premise SQL Server, consider using SQL Authentication as the end-to-end authorization method supported by the Azure application and the on-premise SQL server. So while configuring the authentication mode for database, select “Mixed Mode (SQL Server authentication and Windows authentication)”.

SQL Mixed Mode authentication

On the Database Engine Configuration page, under Authentication Mode, choose Mixed Mode (SQL Server authentication and Windows authentication), and provide a password.

For this scenario, I created a SQL Database named “OrdersDB” with just a table called “OrderDetails”. And this table has a record as shown:

OrderDetails_Record

Order details with line items scenario are for another article. For this simple scenario, we will have just one record for an order whose OrderStatus column will have an initial value as “Received” when the OrderDB gets the order details. When this record is processed and routed to suppliers based on the “Quantity” column, the “OrderStatus” column will be changed to “Processed”.

OrderDetails_RecordProcessed

Create an instance of SQL Connector API:

As the name suggest connectors are used in Logic App to connect the end points. They are used to fetch, process or push data as a part of a flow. As part of various connectors available, SQL Connector are used to work with SQL database on SQL Server (located on-premise and behind the firewall) or with Azure SQL (cloud based). For this scenario, we will be creating a hybrid SQL database.

Before creating the SQL Connector API instance, we need to have Service Bus connection string information.

For hybrid connection like accessing the on-premise SQL database, SQL Connector uses Service Bus Relay. So we have to create a Service Bus namespace and provides its connection string information here. While creating the Service Bus namespace, ensure you select the subscription type as “Standard” as Service Bus relay is only available as part of the standard subscription. Service bus can only be created using old portal: https://manage.windowsazure.com/.

Service Bus

Once you have created the Service Bus namespace, click on its “Connection Information” at the bottom of the service bus page to get the “Access connection information” as shown and get the “Connection String” from it.

Service Bus Connection String

To create an instance of the SQL Connector’ API App, do the following steps:

  1. Go to the Azure portal – https://portal.azure.com/
  2. Click “+New” option at the bottom left of the Azure Portal.
  3. Click on “Web + Mobile”
  4. Then click on the “Azure Marketplace” option.
  5. In the Marketplace blade, search for “SQL Connector”. You can either search under “Everything” options or to be specific “API Apps” Azure Marketplace SQL Connector in MarketPlace
  6. On the SQL Connector blade, give a name for your SQL Connector, select App service plan. Then click on the “Package Setting” link and configure the SQL connector with the following properties.

SQL Connector Package Setting

Server NameSpecify the SQL Server name. In my case its “machiceName”
User NameSpecify a valid user name to connect to the SQL server. In my case its “sa”
PasswordSpecify a valid password to connect to the SQL server.
Database NameSpecify a valid database name in the SQL Server. In my case: “OrdersDB”.
On-PremisesDefault value is “false”. Change it to “true” as we are trying to connect on-premise SQL database.
Service Bus Connection StringPaste the connection string of the service which we created before.
TablesSpecify the table in the database that are allowed to be modified by the connector. In my case it is “OrderDetails”.
Stored ProceduresSpecify the stored procedure in the database that can be called by the connector. In my case, I am going to use a select statement rather than a stored procedure. So I have left this property empty.
Data Available QuerySpecify the SQL statement to determine whether any data is available for table. I have configured it with “SELECT * FROM OrderDetails WHERE OrderStatus='Received'”
Poll Data QuerySpecify the SQL statement to poll the SQL Server database table. To avoid same record being polled multiple times, we can specify multiple SQL statements separated by a semicolon. So in this case, we only receive order details whose “OrderStatus” is “Received” (based on a flag), I have specified the SQL query as “SELECT * FROM OrderDetails WHERE OrderStatus='Received'; UPDATE OrderDetails SET OrderStatus='Processed' WHERE OrderStatus='Received'
”. So this statement retrieves the record whose “OrderStatus” is “Received” and changes this flag value to ”Processed” so this record is not polled again. If the retrieval query is not based on a flag, you can precede the select statement with delete statement.

SQL Connector Configuration1 SQL Connector Configuration2

After configuring the SQL Connector properties. Click “Ok” to close the “Package settings” blade and return to the SQL Connection blade. Then click the “Create” button. After few minutes SQL Connector will be created. You can navigate to the created SQL Connector by selecting Browse –> API Apps –> Your SQL Connector. With these steps you have just created a SQL Connector API instance. You still need to configure the Hybrid connection setup.

Newly created SQL Connector instance will look like this:

SQL Connector New instance

Hybrid Connection lens will have warning sign and would say “On-Premise Setup Incomplete”. Note: When you click on the newly created SQL Connection instance, Hybrid connection lens would look something like below, with the status as “Testing Connection…”.

Hybrid Connection Testing Connection...

This means the hybrid connection status is still in progress of being tested. So wait for this lens to get updated.

Click on the above lens (Hybrid Connection: “On-Premise Setup Incomplete”) will open the “Hybrid Connection” blade. Then click on the link “Download and Configure” to download and install the “Microsoft Azure BizTalk Hybrid Connection Listener”

Hybrid Connection Listener Setup

In the textbox “Please Enter Relay Listen Connection String”, provide the “PRIMARY CONFIGURATION STRING” value from the Hybrid Connection blade and click install. After installing the listener, wait for few minutes and come back to the SQL Connection instance that we have created. Now the “Hybrid Connection” lens status should have changed to “Connected”.

Hybrid Connection Connected

Create an instance of BizTalk XPath Extractor API:

Since in our POC, we want to route the message from on-premise SQL database to different folders based on the value “Quantity”, we need to access the “Quantity” field from the XML message received from SQL connector. So to access the “Quantity” field from the XML message we will use BizTalk XPath Extractor API.

Again go to the Azure Marketplace, search for “BizTalk XPath Extractor” and create a new instance. Steps for creating the BizTalk XPath Extractor is straight forward and doesn’t need any hybrid connection step up.

BizTalk XPath Extractor

Create an instance of File Connector API:

As specified, we will be routing the message based on its quantity value to two different folders. Though we need to create two instances of File Connector, each for two different destination folder, to make this blog simple I am just going to use the same File Connector instance configured to two different folders.

For creating and configuring the File connectors, I would suggest to refer to Saravana Kumar’s blog: http://blogs.biztalk360.com/azure-api-app-and-logic-app-in-depth-look-into-hybrid-connector-marriage-between-cloud-and-on-premise/

In this part of the blog post, we have created Connectors and BizTalk XPath Extractor required to create the Logic App. In Part-2 of this series, I am going create a Logic App utilizing the connectors and BizTalk XPath Extractor to route the message to different folders based on the value in the Quantity field.

Comment (1)

Leave a Reply

Your email address will not be published. Required fields are marked *