Debugging EAI Bridges with Tracking and Tracing – I

 Windows Azure BizTalk Service is a managed service which provides integration capabilities for the Windows Azure Platform to extend on-premises applications to the cloud, provide rich messaging endpoints on the cloud to process and transform messages, and help organizations integrate with disparate applications, both on cloud and on-premises. The core entity of BizTalk Services is a bridge that does all the processing. You may need to see what all operations were executed on the message inside the bridge or in case of a fault what went wrong. For debugging and troubleshooting of bridges in EAI scenarios, we have two broad types of information:

 1.  Operational Tracking – stage execution and message properties information

2.   Tracing – activity execution information stored in Azure WADLogsTable.

 For most scenarios, Operational tracking should provide enough information to troubleshoot the issue. Tracing contains unstructured data and will contain a lot of information which is not directly related to message processing. But it is useful to troubleshoot those hard to fix issues, and most likely, if you report an issue to Microsoft support, they will ask for these traces.

 In this post we’ll look at Tracking and Tracing will be covered in detail in the next post of this series.

Getting started

To get started you would need to create a BizTalk Services Deployment and Download the SDK.

Let’s take a simple scenario where you have an input purchase order message that has to be sent to one of the multiple warehouses based on the city. The warehouse information per city is stored in a database table as shown below:

For this we are going to use BizTalk Services One-Way Bridge receiving messages from an FTP server and sending to one of the two Sql LOB destinations as shown in Figure 1. You can use the sample project attached to this post. More details on the project are given below.

In a new BizTalk Services Project, an FTP source is dropped onto the design surface in the .bcs file and configured. Then we drop a one way bridge and two Sql LOBs (go here for more details on how to do this). We would need to promote some properties that can be used in the route filters to choose the destination. 

 So we promote two properties on the input - ‘City’ using Xpath and ‘Warehouse’ using SQL Azure Database Lookup on ‘City’. The bridge is connected to the destinations using connectors and the route filters are configured with conditions like Warehouse=’1’ as shown in the Figure.

Figure 1 Sample Scenario showing a bridge routing to SQL LOBs using filter condition WareHouse='10'

 Once the above project is successfully deployed to the Azure BizTalk Service, let’s say a message is dropped to the ftp folder with value of city being ‘timbaktu’. No new rows get added to the destination sql table and we need to debug the issue. For this we’ll make use of the tracked data available to us via the BizTalk portal. 

1 Tracking View in BizTalk Services Portal

When you drop an http message to a bridge and get a fault back or in case of FTP/SFTP sources when no output is seen in the destination, then the first place to be visited for troubleshooting is the Tracking section of the BizTalk Portal. Go here for details on how to reach this tracking section. You would see two types of events – informational and error. The tracked data in the view can be filtered using advanced search options available in the search textbox dropdown.

 Figure 2 BizTalk Portal showing the tracking section.  

2 Track records  

  Track records capture the information of message processing events like route, artifact retrieved, custom code and faults. The records show up in the BizTalk portal tracking section as seen in Figure 2. Select any row and click “Details” button on the bottom to see the more information for the track record as shown in Figure 3.

Figure 3. Track record showing the route stage processing information along with the promoted properties.

 Each message has a unique RequestId associated with it which is returned back to the client and can be cited for reference to the Microsoft support in case of any error. TrackId is same as RequestId and is regenerated when a message moves to another pipeline as part of chaining or EDI disassemble.

 Debugging our sample

 Let’s revisit our scenario and look at the tracking section of the portal for errors. We see an Error event here with activity information as “dataLookupPropertyExtract : Activitystatus” and clicking on the Details button we see a track record showing Error as “Lookup returned no results”. This means that our city matched no warehouse in the database and hence lookup failed. You can see the track record containing a property with name as ‘City’ but none with name as ‘Warehouse’.

 Taking another example, let’s say the city is changed to ‘Cincinnati’ and the file is dropped to FTP again. This time too no new rows appear in the destination table. In order to find out what happened we go to the tracking portal which shows an error “No Filter matched for the message”. Looking at the details of this track record we see that the value of Warehouse property does not match the filter conditions of any of the destinations and hence this error as shown in Figure 4.

 Figure 4.a Fault Track records from the Route stage showing the promoted properties   

Figure 4. b. TrackSubmitted Fault record from FTP source

You can even drop the messages directly to the bridge using message sender tool and in this case you would receive a fault back with the same error.

Now let‘s look at some more aspects of tracking.  

3  Tracking configuration

Figure 5. Track Properties on the bridge in the BizTalk Service Project

By default only a fixed set of records like route success, custom code calls and faults are tracked. In order to add more information to be tracked, the design surface in the BizTalk Service project exposes a property “Track Properties” on each Bridge. Click the ellipses against this property to launch the dialog as shown in Figure 4. You can choose one or more of the below options in the dialog:

1. Track Message Processing events - emits informational records to the tracking database. For more details refer to section 5.
2. Track all message properties – select all or some of the promoted properties to be shown in the track records.

4  Tracking Database

The tracking information shown in the portal is backed by the tracking database. When you created the BizTalk Service Deployment, you would have created this database in Azure where all the tracking related data goes.

The information in the database can be used for viewing pipeline and source track records. You can write analytics based on the tracked data like total number of incoming messages, the no. of successful messages as well as the promoted properties.

The tracking database exposes a GetTrackRecords stored procedure that can be used for retrieving the track records as well as automation of failure notification. The main tables in the database are:

 Figure 6. Tracking Database example showing contents of PipelineTrackRecords Table.

5   Pipeline and Source Track Records 

Track Records can be emitted by the Pipelines as well as FTP and SFTP Sources. Pipelines can emit below information and error events based on the configuration:

     1.  Pipeline started

     2.  Pipeline completed

     3.  Stage started – emitted for all the Enabled Stages like xmlvalidator, requestMessageExtractor, requestMessageTransform, transformedResponseEnrich, route, sendReply and others.

     4.  Stage completed

     5.  Artifact Retrieved - for artifacts like schemas and transforms

     6.  Custom – emitted from custom code inspectors when they are set.

     7.  Route – show the route status for the message ie faulted or success.

     8.  Faulted – when any error is thrown like lookup failed, validate failed

With "Track All Message Processing Events" option set to true all the above events get saved in the tracking database, but the BizTalk Portal Tracking section will only show last four events. For EAI scenarios, FTP and SFTP Sources emit below events to the tracking database.

1. TrackSubmitted – emitted when the message is submitted to the pipeline

2. TrackProcessed – emitted when the message is deleted from the source. Faulted when delete fails as in case of read only user.

3. TrackStatus – emitted on source start and stopped. Faulted when service cannot connect to the FTP server.

4. TrackRetrieved – error event is emitted when the file cannot be retrieved from the server in cases like it being a subfolder inside the FTP folder. Error looks like ‘The remote server returned an error: (550) File unavailable (e.g., file not found, no access)’.

5. Poll Error – emitted when one of the above errors happen. The poll interval in increased to 1.5 times on each poll error in order to give time to rectify the issue.

6. Listing error – emitted when the listing of the files fails eg when the configured FTP folder is deleted 

The Tracking portal will only show error events for the sources.

6  Summary

In this post we have gone through the details of tracking portal and the tracking database and looked at the various track records useful for debugging. And if that seems insufficient to investigate the issue further, for instance with custom code, when tracking portal does not show the error stack traces. Then you can go to the traces stored in the WADLogsTable of the monitoring store. We shall see how to do this using Tracing in the next blog.

EAITracking.zip