BAM Activity missing data

BizTalk BAM (Business Activity Monitoring) components are a fickle and complicated beast.  It seems to be fantastic, you define some metrics, do some clicky-clicky in a UI and you have instant data capture and insights into what's going through your solutions.

Unfortunately, anybody who has used it knows it's not as simple as that.  For one, the bundled UI for viewing the data only works in IE5 compatibility mode - which probably sums up the amount of love the product receives.

Today I'm looking at data going missing.  I've got a message that comes into an Orchestration and does stuff.  Using TPE I track various data items on the inbound message, including a field of type xs:dateTime.

When I view the bam_XXX_AllInstances view in the BAMPrimaryImport database I can see my entry, but it's missing all the data from the message.

To troubleshoot this, you will need to check the failed data table:

SELECT TOP 100 
  dtLogTime, ErrMessage, DataImage 
FROM TDDS_FailedTrackingData WITH (nolock) 
ORDER BY SeqNum DESC

Around about the same time as your message was processed will be the accompanying error as to why it went wrong.  You also have a serialised (not XmlSerializer) binary blob of the data that it was trying to insert, you can extract this and decode it to see what data was in the object being saved.  The important bit is the error message.

I am not going to go into all the possible reasons and solutions as to what could go wrong as it's fairly limitless.  I will though, note a frustrating one:

The issue in this instance is the xs:dateTime field in my message, which has been supplied with a valid ISO-8601 value: 2018-09-07T16:02:48.6620158+01:00.  This causes a problem with SQL, or specifically the DATETIME data type that BAM will use if you use any time related metrics.

SQL does not handle date strings with an offset when converting into a DATETIME data type.  SQL 2008 introduced the DATETIMEOFFSET data type which does, but for some reason, this isn't being used in BAM (probably because it doesn't seem to get much love).

You can hack this simply by changing the bam_XXX_PrimaryImport stored procedure to use DATETIMEOFFSET as the data type on the parameter in question, and it works just fine and dandy, but this is hacking around with the underlying database which is not a production safe thing to do.  If you update your activity it'll go pop.

The only real solution to this is to transform the message in BizTalk to a format that BAM will handle natively.  I really don't like this either as it is inserting an overhead and specific code for something that is supposed to be applied over the top of business logic.

You can of course resort to the API and simply write data in via a BufferedEventStream, but that throws away the convenience of the Tracking Profile Editor (TPE).  I wish I had something more positive to say about this, but at this time I do not.