May 2016

Volume 31 Number 5

[Microsoft Azure]

Enterprise Application Integration Using Azure Logic Apps

By Srikantan Sankaran | May 2016

Business process management is a key focus area for many enterprises and integration with heterogeneous systems forms the heart of process automation. Under this context, a critical use case that emerges is related to ingestion, transformation and routing of data across the enterprise and the Microsoft Azure Cloud. Using the traditional approach to implement such enterprise application integration (EAI) scenarios entails a significant investment of time and cost—to identify and invest in a tool, to build technical expertise and to provision compute infrastructure to support the scenarios end-to-end. A cloud-based managed service like Azure Logic Apps addresses these problem areas.

The Logic Apps feature of the Azure Apps Service provides a visual editing experience where developers can compose complex data exchange between multiple systems or data sources using a suite of standard and enterprise integration connectors. Scenarios like handling of transient failure, which is unique to cloud-based architectures, and others like condition-based routing of process flows and long-running transactions, are easily implemented through this service.

The capabilities of Logic Apps are demonstrated in this article through an enterprise integration scenario where a seller organization has deployed an order capturing system on Azure that leverages the capabilities provided by Logic Apps. This Logic App process picks up orders from a secure public endpoint exposed by a buyer organization, which is then passed through numerous steps comprised of data capture, data transformation and routing prior to getting registered in the back-end system. An acknowledgement ID is generated and sent back to the buyer organization for each of the orders registered.

EAI Process Flow

Figure 1 depicts a simple EAI scenario that’s implemented using Logic Apps.

Figure 1 Enterprise Application Integration Process Flow

Order data that triggers the integration is uploaded from the buyer organization in the form of flat files that are exposed securely using an SSH File Transfer Protocol (SFTP) endpoint over the Internet. Access to the endpoint is secured with authentication credentials that are shared with the seller organization. Separate folders are created to drop order data into and to receive acknowledgement messages. An Azure virtual machine running CentOS Linux is used as the SFTP server that hosts the endpoint for the file pickup and drop.

An Azure SFTP connector is used to connect to the SFTP endpoint from where the order data is picked up based on a time-triggered frequency. An instance of this connector is created in the Azure portal, from the Azure Marketplace and deployed as an API App. The same API App instance would also be used when uploading the order acknowledgement data back to the SFTP endpoint.

Here are key parameters to set in the SFTP connector instance configuration, when added to the Logic App flow:

• Trigger frequency: 2 minutes used in the scenario implementation
• Pickup location: Add a path relative to the root path (for example, /home/<sftpusername>) configured in the SFTP connector instance earlier; in this case: b2b/orders
• Select the Delete File after Read option

The BizTalk FlatFile encoder is used to read the flat file from the SFTP connector API App and convert that into a schema-bound XML document. The FlatFile Encoder is available as part of the Enterprise Integration Connectors for Logic Apps. An instance of this connector is created from the Azure Marketplace and deployed as an API App before consumption in the Logic App flow. While the FlatFile Encoder supports basic XSD schema generation from Flat files and JSON Documents, for more complex requirements involving multiple child records, such as those used in this scenario, the schema are generated using Visual Studio 2012 and BizTalk Services SDK. The XSD schema documents are then uploaded to the BizTalk FlatFile Encoder Connector API App.

The BizTalk Transform Services connector converts the XML document containing the order data into an XML payload for insertion into the Order Capture Database. It uses a Transformation Map that’s authored using Visual Studio 2012 and the BizTalk Services SDK. To the instance of this connector that’s deployed as an API App, the Map files, both for the order registration and for the acknowledgement flow, are uploaded. The schema files used in the Map do not have to be uploaded to this API App.

The SQL Database Connector API App instance consumes the XML document payload from the previous step and inserts the order data into the Azure SQL Database. The schema used to generate the XML payload for the insert operation can be generated in Visual Studio 2012 with BizTalk Services, based on an XML document instance, or using the BizTalk WCF LOB Adapters for SQL Server. However, the Azure SQL Database Connector API App itself now supports the generation of the schema for all the operations on a database, directly from the API App configuration in the Azure Portal. This obviates the need for the additional tools like the BizTalk WCF LOB Adapters or other third-party solutions, which otherwise would have been required for on-premises deployments.

Figure 2 shows some of the key parameters to set in the Azure SQL Database Connector.

Figure 2 Azure SQL Database Connector Configuration

On successful insertion of order data, the response from the Database Connector is transformed using the BizTalk Transform Service Connector API App configured in the earlier steps, from an Array of Order ID numbers to a comma-separated list.

The Azure Storage Blob Connector API App Instance is used in the Logic App flow to store the error messages when the data­base insertion in the previous step fails.

The order IDs generated in the response are uploaded using the SFTP Connector configured earlier to the buyer organization’s SFTP endpoint.

The Logic App process flow, when complete, would appear as depicted in Figure 3.

Figure 3 Complete Logic App Flow

At this time, the default schema version that’s used when creating a new Logic App is 2015-08-01-preview, which does not yet provide the ability to add enterprise connectors. Hence, to implement the current EAI scenario, this schema version should be replaced with 2014-12-01-preview. This can be done in the Code View of the Logic App by editing the JSON code directly.

Remember to save changes, close the Logic App editor and reload it to let the changes take effect.

When adding resources into the Logic App flow like all the connectors, Azure SQL Database, Storage Connections and so on, it’s good practice to group all of them under a single resource group for ease of handling and ease of deployment. It’s also preferable that the resources are deployed in the same region (Azure Datacenter) in order to minimize latency during the flow execution.

Transient Error Handling in the Flow

Azure Logic Apps provide the ability to incorporate a retry logic to take care of transient errors that are unique to cloud-based arch­itectures. This involves adding a JSON code fragment—as you can see by the highlighted code in Figure 4.

Figure 4 Handling Transient Failures

"b2bmicrosoftsqlconnector": {
  "type": "ApiApp",
  "inputs": {
    "apiVersion": "2015-01-14",
    "host": {
      },
    "operation": "XMLInsertOnordersax",
    "parameters": {
      "requestMessage": {
        "InputXml": "@{body('transformservice').OutputXml}"
      }
    },
    "retryPolicy" : {
      "type": "fixed",
      "interval": "PT30S",
      "count": 2
    },
  },

When there are intermittent failures or connectivity exceptions in executing the order insert operation in the Azure SQL Database, the Connector API App will retry the request two times until it returns an error.

Logic Apps also support the ability to add features such as Repeating Call execution until a predetermined condition is met, implement Wait Actions, invoke other workflows and so on.

Conditional Logic App Flow Execution

Within a Logic App flow, it’s possible to define actions based on the outcome of the previous operation. The option to define a condition can be set either in the Configuration pane in the Designer or in the Code View of the Logic App.

For example, in the current flow, if the outcome of the order insertion into the Azure SQL Database is successful, then the response Message is transformed before upload to the SFTP endpoint at the buyer organization.

In case of a failure, an error message is uploaded to the Azure Storage using the Azure Blob Storage Connector. The Logic App UI visually depicts the alternate flows thus configured. Refer to Figure 6 that depicts how Conditional Actions can be defined and how the Logic App visually depicts the flow that involves them.

Figure 6 Visual Representation of Conditional Actions

Working with XSD Schema Documents

Development tools that developers have been using to build their artifacts for BizTalk Server-based Enterprise Integration Solutions can be extended for use to Logic Apps, as well. The BizTalk Services SDK, in conjunction with Visual Studio 2012, provides additional project templates that enable the developer to generate or author XSD schemas and transformation maps. There are built-in wizards that could be used to generate these both from flat files and from XML document instances. The output XSD schema documents generated in Visual Studio 2012 could be directly uploaded to the FlatFile Encoder Instance used in the Logic App.

A sample XML file that was used to generate the XSD schema for Azure SQL Database order data insert, using the Schema Generation Wizard in Visual Studio 2012, is shown in Figure 5.

Figure 5 XML File Used to Generate XSD Schema for Azure SQL Database Order Data Insert

<Insert xmlns="https://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/ordersax ">
  <Rows>
  <ordersax xmlns="https://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">
    <orderidsap>sap0101</orderidsap>
    <orderdate>2016-02-03</orderdate>
    <supplierid>sail001</supplierid>
    <orderamount>25000</orderamount>
    <currency>INR</currency>
    <discount>12</discount>
    <delvydate>2016-06-03</delvydate>
    <contract>C001</contract>
    <contact>Mr</contact>
  </ordersax>
  <ordersax xmlns="https://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo">
    <...>
  </ordersax>
... and more records ...
  </Rows>
</Insert>

The reusability of such artifacts that are used for on-premises deployment of EAI Solutions using BizTalk Server provides enterprises extended value on their investments to explore Azure as an option on which to deploy their solutions. Along with that, there are additional benefits like quick time to deployment, ease of provisioning of infrastructure, ease of manageability and monitoring of the solution.

Working with Maps

The Azure BizTalk transform service connector uses BizTalk transform maps to transform XML documents. However, to create these maps, Visual Studio 2012 with BizTalk Services SDK is required. As with the XSD schemas, the map transform files once generated here can be imported into Azure Logic Apps directly.

In the Process Flow considered here, to map the incoming order XML to the database Insert XML schema and to iterate through every record in the incoming document, the MapEach Loop functoid is used, as depicted in Figure 7.

Figure 7 Mapping of Order Insert Acknowledgement

To map the order creation response message to the outgoing message, the array of Order IDs generated from the SQL Server database needs to be converted into comma-separated values inside a single Element. To achieve this, the Cumulative Concatenate functoid is used.

Executing the Scenario

In order to try out the scenario described here, drop the file orderssap.csv into the /home/<sftpusername>/b2b/orders folder using the WinSCP/equivalent Tool.

Monitor the status of the Logic App Connector trigger logs to ensure that the trigger has indeed fired.

If there are no errors, a file, resp.XML, would be deposited in the /home/<sftpusername>/b2b/resp folder, which would contain the comma-separated order ID values generated from the Order Processing System in Azure.

Now, drop the orderssap.csv file again into the same folder, without any changes. The Database Connector will throw an exception due to a Unique Constraint Violation error from the database. An error message will be uploaded into Azure Blob Storage, into the container “orders.”

Logic App Execution—Tracking and Monitoring

Use the Operations tile to monitor the exe­cution of the Logic App Flow. There’s also a separate log for the trigger events on the Logic App. Drill down into each of them and view a breakdown of the execution of each connector in the flow, the outcome of the execution and duration.

Extending the Scenario for Cross-Premises Integration

While the SQL database consumed in this scenario is deployed in Azure, it would be just as easy to integrate with a SQL server database running inside a corporate network. The Azure App Services infrastructure, of which Logic Apps is a part, provides a feature called hybrid connections. Implementation of this feature involves installation of a Hybrid Connection Manager on a server behind the firewall in the corporate network, through which the Logic App integrates with this database. Apart from SQL Server, other databases that can be accessed using hybrid connections are Oracle, DB2 and Informix.

Using hybrid connections, the use of the BizTalk enterprise connectors can be extended to line-of-business (LOB) systems like SAP and SharePoint deployed inside corpo­rate networks. They can also be extended to REST Services and Web services deployed on premises.

Software Prerequisites to Implement This Scenario

Apart from an Azure subscription that’s required to implement the scenario described here, the following development tools and additional software is also required:

• Visual Studio 2012 along with the BizTalk Services SDK running on Windows 8/8.1/10 or Windows Server 2012 R2 is required.
• SQL Management Studio for SQL Server 2014 or later to create and connect to the Azure SQL Database. Alternatively, use Visual Studio 2012/or higher Tools for SQL Server.
• WinSCP or an equivalent tool to connect to the SFTP Server, create the “send” and “receive” folders and to drop and receive messages.

Artifacts Available for Download

The following artifacts used to implement this scenario are available for download from the GitHub repository  at bit.ly/1poJPgs:

• Azure SQL Database creation script–ordersax.sql
• Sample order data file–orderssap.csv
• XSD schemas:
  • dbinsert_sampledata.xsd: SQL Insert schema
  • dbinsert_sampledata1.xsd: part of, and referenced in the schema above
  • orderssap.xsd: schema for the incoming Order Document
  • orderresponse.xsd: SQL Insert response
  • orderresponse1.xsd: part of, and referenced in the SQL Insert response schema
  • ordersaxresp.xsd: schema of the outgoing XML response message
• Maps:
  • orders.trfm: Mapping incoming message to SQL Insert schema
  • ordersaxtosap.trfm: Mapping SQL Insert response schema to outgoing Message schema

The following references would be useful when implementing the solution covered in this scenario:

• Software prerequisites to develop schemas and maps used in the solution: bit.ly/1R3RzvH
• Using Visual Studio 2012 to generate schemas from flat files: bit.ly/1nP6qBy
• Configuring an SFTP Connector in Azure: bit.ly/254QfC2

Wrapping Up

Azure Logic Apps provides a platform to implement end-to-end EAI scenarios. The comprehensive set of standard connectors and enterprise connectors makes it possible to integrate with numerous enterprise LOB systems and Software-as-a-Service applications in the cloud. It can seamlessly consume artifacts generated for on-premises EAI solution deployments using BizTalk Server 2013, like schemas and maps. This provides enterprises with an option to repurpose their current investments and unlock their potential to implement scenarios in the cloud, leveraging the benefits of high availability, reliability and ease of set up of the infrastructure that Azure provides. With hybrid connections support in Logic Apps, additional value can be derived from current investments made in LoB systems deployed on-premises, by opening up opportunities to have them participate in integration scenarios running in the cloud.

Tip!

A quick way to get started with developing the artifacts in the solution is to use the Gallery VM image in the Azure Management Portal that runs BizTalk Server 2013 Standard Edition. Installing Visual Studio 2012 and the BizTalk Services SDK in the VM took me no more than 30 minutes.

Srikantan Sankaran is a technical evangelist from the DX team in India, based out of Bangalore. He works with numerous ISVs in India and helps them architect and deploy their solutions on Microsoft Azure. Reach him at sansri@microsoft.com.

Thanks to the following Microsoft technical expert for reviewing this article: Sandeep Alur
Sandeep Alur – is the Lead Evangelist for DX India and is based out of Bangalore.

Discuss this article in the MSDN Magazine forum