This is the Part-2 of my blog post about “Real life hybrid scenario with Azure Logic App, SQL Connector, BizTalk XPath Extractor and File Connector with condition”. If you not have visited Part-1 and as it is mandatory to define the steps specified in that part, please refer it first and come back to this part after completing Part-1.
Create a Logic App to orchestrate the message flow:
At this stage, we have created the connectors and BizTalk XPath Extractor required to create our Logic App, where we will use these API apps (connectors and BizTalk XPath Extractor) and to route the message to different folders based on the value in the Quantity field.
- Click “+New” option at the bottom left of the Azure Portal.
- Click on “Web + Mobile”
- Under “Web + Mobile”, select “Logic App”
- In the “Create Logic App” blade, give it a name as “sqltofile-withcondition”
- Select the “App service plan” and click “Create”. Ensure you give the same resource group as the one which has been used for creating the Connectors and BizTalk XPath Extractor.
- After few minutes Logic App will be created and Logic App blade will be opened. In its “Settings” blade, select “Triggers and actions”. This will open the “Triggers and actions” blade with API Apps like SQL Connector, BizTalk XPath Extractor and File Connectors created in the resource group.
- Click on the “Microsoft SQL Connector” on the right side. This will list the available triggers – Poll Data (JSON) and Poll Data (XML).
- Since we want our Logic App to be triggered based on the availability of the record in SQL and route the message based on the Quantity file, we will choose the Poll Data (XML) as the trigger type.
- Configure the poll frequency to be every minute.
- Click the green tick mark to validate the configuration.
- Click BizTalk XPath Extractor from the API apps list in the right side. And Click “Extract using XPath” under “Choose an action”.
- In the “XPath” text box, give the XPath for the “Quantity” field. Note: I have retrieved the XML from SQL Connector by creating a sample Logic App which has SQL Connector and a file connector. So I just retrieved the XML representation of the record in the SQL database. It looks like the below:
- In the “Input XML” text box, click “…” button to get the list of values available and select “@triggers().outputs.body.OutputXml”
- Click File Connector from the API apps list in the right side. This file connector will receive the XML whose order quantity value is greater than 100.
- In the file path configure “@concat(‘/Out100/’,’Output.xml’)”. Here “Out100” is the destination folder name and “Output.xml” is the output file name. So with the command “@concat” we concatenate the Output folder name with output file name. (Make sure the folder with name Out100 is created under the root folder to which file connector is created-the root folder is the one which you would have specified while creating the instance of the File connector).
- In the content select “@triggers().outputs.body.OutputXml”. This specifies to get the Output XML of the “trigger” which is the SQL Connector.
- Now we want to apply a filter condition to this file connector to get the order whose quantity field is greater than value 100. So click on the small wheel button at top of the File connector action and click “Add a condition to be met”
- In the condition text box, give the value @greater(int(body(‘xpathextractor’).Result),100). Here @greater is the function we want to use. This function takes two parameter. First parameter is the value from the ‘xpathextractor’ action –which is the output of the BizTalk XPath Extractor action. And second parameter “100” is the value which we want to compare against the first parameter. Note: we need to type-cast the output of the BizTalk XPath Extractor to int for @greater to compare. I’ll write another blog about my experience on functions to be used in actions.
- Click the green tick mark for this setting to be set.
- Click another file connector from the right side to route message to another folder when the quantity is less than 100. Configure this file connector with similar setting as above.
- But give a different folder location for the File Path. @concat(‘/OutLesser100/’,’output.xml’). This will route the message to “OutLesser100” folder.
- Again set condition for the action again and give the condition value as @less(int(body(‘xpathextractor’).Result),100). @less is the function to use to check if the received message quantity field is less than 100. Click the green tick mark for this setting to be set.
- Save the Logic App, by clicking the “Save” button at top. When you save the Logic App, ensure in the SQL database OrderDetails OrderStatus flag is set to “Received”. As this Logic App is based on a trigger action which runs every minute you don’t need to click the “Run Now” button.
- Actions in Logic App are executed in parallel starting from triggers based on how they are chaining/linked. By default all the actions are executed unless you specific a condition for the action. For the above Logic App, not all the file connector actions are executed. Based on the value of the quantity field, one of the File connectors is executed and other file connector’s execution is skipped. If you check the status of the Logic App’s instance, you will get to know this.
With this scenario, we have
- Implemented an hybrid connection
- Used SQL Connectors
- Used File Connectors
- Used BizTalk XPath Extractor
- Used Conditions in File Connectors
In this scenario, we have implemented a real life hybrid integration where data from an on-premise SQL database is routed to different folder locations based on conditions.
In another blog series I’ll share my experience about functions, condition and how to handle batch message to loop through the received message.