Tutorial: Using BizTalk Service Bridges to Lookup Data from Azure SQL Database

  • Article

 

Important

Microsoft Azure BizTalk Services (MABS) is being retired, and replaced with Azure Logic Apps. If you currently use MABS, then Move from BizTalk Services to Logic Appsprovides some guidance on moving your integration solutions to Logic Apps.

If you're brand new to Logic Apps, then we suggest getting started here:

This tutorial provides guidance on how to use the Enrich stage within a BizTalk Services bridge to look up data from a Microsoft Azure SQL Database. To demonstrate how the bridge looks up data from Azure SQL Database, consider a scenario in which a flat-file message is picked from an FTP server, is processed using a BizTalk Services bridge, and the data from the flat file is finally inserted into an on-premises SQL Server. In addition to demonstrating how to look up data from Azure SQL Database, this tutorial also provides guidance on the following features::

With the support for processing flat-file messages, you can use a one-way bridge to process XML as well as flat-file messages, using the same bridge endpoint. However, you would still need to create and add the schema of the flat file message to the BizTalk Service project. The Flat File Schema wizard does that for you. If you already have a flat-file message, you can use the wizard to generate the schema for that flat-file message and also add it to the project. You can then put a flat file message on an FTP location and the bridge could consume the flat-file message through an FTP source, process it, and then send to the required destination endpoint. And finally, you could track the message as it gets processed within each stage of the bridge. Organizations can tie all these features together into an end-to-end scenario that to meet their business requirements. Using the following business scenario, this tutorial demonstrates these features and some other features in BizTalk Services.

Business Scenario

Northwind Traders is a healthcare service provider that handles medical insurance claims for an insurance provider, Humongous Insurance. Northwind sends insurance claims as flat-file messages to Humongous Insurance. Humongous Insurance processes these claims and stores them in-house using an on-premises SQL Server database. Humongous Insurance wants to deploy this business process as an application on Microsoft Azure. Humongous Insurance decides to use the integration capabilities provided with Microsoft Azure BizTalk Services to deploy this application on the cloud.

Here are the series of steps that Humongous Insurance and Northwind Traders have to perform at their ends to develop, configure, and deploy the application.

Humongous Insurance creates an BizTalk Service project using BizTalk Services. Within this project, it does the following:

Uses a sample flat-file instance message that it receives from Northwind Traders (out of band, over e-mail), to create the flat-file message schema. Humongous Insurance needs this schema to validate and process flat-file messages that it receives from Northwind.

Adds an FTP source component to the project. The FTP source represents the FTP server where Northwind Traders drops the flat-file message.

Adds a one-way bridge to process the flat-file messages that it receives from Northwind Traders. Within the bridge, Humongous Insurance does the following:

  • Uses a transform to convert the message received from Northwind to a format that is required to insert the message into a SQL Server database table where Humongous Insurance maintains all the insurance claims.

  • Performs data enrichment on the incoming message. Through data enrichment, Humongous Insurance enriches the message to include information which was not part of the original message that Northwind Traders sent. For example, in this scenario, the flat-file message from Northwind Traders only includes a claim type information. But Humongous Insurance must include the claim type description as well in message that is inserted into the on-premise SQL Server database. So, to achieve this data enrichment, Humongous Insurance looks up a Azure SQL Database table (which it maintains to map claim type to claim description) to see which claim type description maps to the claim type in the incoming message and then updates the message that is finally inserted in the on-premises SQL Server database to include the claim type description.

  • Promotes certain elements in the message as properties that it can use to track the message as the bridge processes it.

Finally, Humongous adds a BizTalk Adapter Service component to the BizTalk Service project that represents the on-premises SQL Server where the data from the message has to be inserted.

Humongous Insurance builds and deploys this solution on Service Bus.

Once the solution is deployed, Northwind Traders drops a flat-file message for an insurance claim at the specified location on the FTP Server. The XML One-Way Bridge consumes the message and inserts into the SQL Server database. The following illustration represents the same scenario.

Business scenrio

How to Use This Article

This tutorial is written around a sample, FlatFile_Bridge.zip, which is available as part of the download from the MSDN Code Gallery. You could either use the sample and go through this tutorial to understand how the sample was built or use this tutorial to create your own application. This tutorial is targeted towards the second approach so that you understand how this application was built. Also, as far as possible, the tutorial is consistent with the sample and uses the same names for artifacts (for example, schemas, transforms, and so on) as used in the sample.

Even though Microsoft recommends that you follow the tutorial to understand the concepts and procedures, if you wish to use the sample, do the following:

  • Download the FlatFile_Bridge.zip package, extract the FlatFile_Bridge sample, and make relevant changes like providing your service namespace, issuer name, issuer key, updating the LOB Relay, and FTP components to include your specific server details, and so on. After making required changes, build and deploy the application.

  • Drop a test message at the FTP location configured as part of the solution and verify that the application works as expected. If the message is successfully processed, it is routed to the SQL Server and you can verify that new records are entered in the Claims table.

In This Section

See Also

Developing Enterprise Applications with Azure