ESB Toolkit Tip #5: Link BAM Itineraries data and Exceptions data with a custom Foreign Key

With the ESB Toolkit you get BAM out of the box. There are already two activity definitions created that give information about itineraries that are processed in BizTalk and about Exceptions that occur in BizTalk. You only have to deploy the definitions with the Bm.exe tool to enable BAM tracking so it’s very easy to use BAM with the ESB Toolkit.  
When you deploy the activity definitions are several BAM tables en views created in the BAMPrimaryImport database. They contain very valuable information but the strange thing is that you cannot link the Itineraries data to the Exceptions data. The data is stored in different tables and there is no foreign key to join the tables. This is very unfortunate because now you know for example that an error has occurred in an Itinerary Service but you don’t know in which itinerary. Also if you want to have an overview of the running itineraries you have to link the data because if an exception has occurred in an itinerary that itinerary is not running anymore but you don’t have information about that in the Itineraries data. How can you fix this? By creating a custom Foreign Key!


Creating a custom Foreign Key

In the bam_ItineraryServiceActivity_CompletedInstances view you have the InterchangeID that is set by the Messaging Engine for each message that arrives on the Server. In the bam_EsbExceptions_CompletedInstances view you don’t have it but you can use other fields to store it in. The FaultCode field is a suitable candidate because it has the same data type as the InterchangeID and you can set the FaultCode inside an Orchestration when an Exception occurs.

Use the following code to get the InterchangeID and to create a Fault message when an Exception occurs. The data of the Fault message is stored in the EsbExceptionDb database but also in the BAMPrimaryImport database.

// Get Context properties
interchangeID = msgInbound(BTS.InterchangeID);

// Create FaultMessage
msgFault = Microsoft.Practices.ESB.ExceptionHandling.ExceptionMgmt.CreateFaultMessage();

// Set Fault Message Properties
msgFault.Body.FaultCode = interchangeID;


Catch Exceptions











Retrieving data from multiple tables with SQL Joins

Create a SQL query in SQL Server Management Studio on the BAM views in the BAMPrimaryImport database. Use the InterchangeID and FaultCode fields to link the views.

Itineraries With Errors

In the following example are the bam_ItineraryServiceActivity_CompletedInstances view bam_EsbExceptions_CompletedInstances view joined to get an overview of Itineraries with errors. Itineraries With Errors





Running Itineraries

In the following example are the bam_ItineraryServiceActivity_CompletedInstances view bam_EsbExceptions_CompletedInstances view joined to get an overview of all the running Itineraries.
Running Itineraries


See Also

For more information see:

Using BAM in the ESB Toolkit

About the author

Tomasso Groenendijk lives in Netherlands and is a Solution Architect at Insight. He has over 17 years’ experience in software development and software design. Tomasso is specialized in application integration with a strong interest in the Windows Azure cloud platform. From 2014, Tomasso has been awarded four times with the Microsoft Azure MVP award. He is an active contributor to the integration community through his work on his blog, MSDN samples, TechNet Wiki and also speaking on events.

MVP profile Tomasso Groenendijk
Sentinet Product Specialist
BizTalk360 Product Specialist

Month List