Diagnosing BizTalk BAM TDDS errors

If you use BAM with your BizTalk solutions, you may encounter errors in your Windows Event Log for the BAM EventBus Service with an Event ID of 6 accompanied by a Warning with an Event ID of 5.  For example:

Execute event error. Error(s) occurred while executing events, see TDDS_FailedTrackingData table for more details.  SQLServer: biztalk-db, Database: BAMPrimaryImport.

Execute batch error.  Exception information:  TDDS failed to batch execution of streams.  SQLServer: biztalk-db, Database: BAMPrimaryImport.Error converting data type nvarchar to datetime..

In this example, the error is quite clear, a dodgy date being inserted into the BAM Primary Import database.  This database is created when you create BAM activities through the spreadsheet and bm tool, it is a straight off reflection of what you have defined.  In my example, I've defined a date in Excel, and that's created a datetime column in the BAM databases.

But whilst this error is clear, it doesn't tell us which date field is wrong, or for what instance of data, or what activity.  If you have a reasonably active BizTalk environment, where to do you start?  Well just where the error told us to look: the BAMPrimaryImport.dbo.TDDS_FailedTrackingData table.

USE BAMPrimaryImport;
SELECT TOP 100 * FROM TDDS_FailedTrackingData WITH (nolock) ORDER BY SeqNum DESC;

We can quickly make out the ErrMessage column, which contains the same error as in the Windows Event Log, and then a DataImage column.  It is this column that has the useful data.

Extracting DataImage

The DataImage column is of type image, which is a SQL binary column.  If you view it in SQL Management Studio, you'll see something like 0x 0001000000FFFFFFFF0100000000000000060100000004626C65650B.  This is simply binary data represented in hexadecimal, as indicated by convention by the 0x at the front.

For one-offs, we can convert the hex string to binary by pasting it into something like Notepad++ and running a Hex to ASCII converter and saving the outcome.  Or, more systematically we can use ADO and read the data directly out of the database and use the SqlDataReader.GetStream() method to pull it back as a stream.

Once extracted, you may wish to try and read it as plain-text.  Unfortunately, it's not a nice little Xml payload but an actual binary stream (although you may spot some useful things, like your data in it).  The best way to handle this data is to use the .Net Binary Formatter to deserialise the content:

var formatter = new System.Runtime.Serialization.Formatters.Binary.BinaryFormatter();
var bamdata = formatter.Deserialize(mystream);
Console.WriteLine(bamdata.ToString());

The object that gets deserialised is a BAMTraceFragment, which contains the activity name and all the data that was being inserted into BAM (not the original message or anything) as a hashtable.  By calling .ToString() on the object you'll get all this information printed out in a fairly nice format.

For my error above, I could easily see that the date format was in a different locale to the user in SQL that BizTalk was using to insert the data into the Primary Import database.

Manipulating BAMTraceFragment

If you want to do anything more specific with the deserialised BAMTraceFragment, then you're in for a bit more of a challenge.  This type is defined in the Microsoft.BizTalk.Bam.EventObservation.dll found in the Tracking directory of your BizTalk install folder; unfortunately it is an internal type so accessing properties is a little fiddly.

You can though of course use reflection to access the data, useful items are:

• ActivityName (internal property)
• InstanceID (internal property)
• m_args (private field)

var assEventObservation = Assembly.Load("Microsoft.BizTalk.Bam.EventObservation, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35");
var eventFragmentType = assEventObservation.GetTypes().Where(x => x.Name == "BAMTraceFragment").First();
var activityProperty = eventFragmentType.GetProperty("ActivityName", BindingFlags.Instance | BindingFlags.NonPublic);
var activityName = activityProperty.GetValue(bamdata) as String;

etc.