Accessing BizTalk Tracking data with SQL Views

You can turn on message tracking in BizTalk at the Receive Port and Send Port level (as well as various levels in the orchestration).

Here are the views provided by the BizTalk install.


use biztalkDtaDb
--select * from TrackingData 
--select 'btsv_Tracking_Fragments', * from btsv_Tracking_Fragments
select 'btsv_Tracking_Parts', * from btsv_Tracking_Parts 
select 'btsv_Tracking_Spool', * from btsv_Tracking_Spool 

To get the Send/Receive port or more info about the message, you need to join to dtav_MessageFacts. The idea for this join came from sample code here.


SELECT top 200
      a.[Event/Direction],
      a.[Event/Port],
    a.[Event/URL],
      CONVERT(VARCHAR(10), a.[Event/Timestamp], 111)   as [date],
      DATEPART(HOUR,a.[Event/Timestamp]) as [Hour], 
    imgPart, 
    imgPropBag
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID
<code>

Note: Later I added this line (not shown in picture).  You have to use DataLength() instead of Len() to get the size of an image field. 
<code>
DataLength(imgPart) as Length,

The body of the message is stored in imgPart of the btsv_Tracking_Parts view, but unfortuantely it’s in Hex.
The following site is one that can convert the hex to ascii (might also need one to go to Unicode). https://www.rapidtables.com/convert/number/hex-to-ascii.html. Paste the hex data in the top, click convert, and your text will appear in the bottom half. You won’t be able to see it all, but you can copy/paste to NotePad++ or some other editor.

While it’s nice to use the built-in features of BizTalk when possible, they will typically have limitations compared to custom options. In a few places where I worked, we implemented our own “Trace” that writes data to a SQL trace table. We had our own concept of promoted fields to identify the trace, such as the location where the trace was capture (pipeline, orchestrations, before/after map, etc), a user type key, and a correlation token that can tie together traces across an entire business process.

See also: 3 ways of programmatically extracting a message body from the BizTalk tracking database (Operations DLL, SQL, and WMI). This article explains how Biztalk compresses the data with BTSDBAccessor.dll.

Uncategorized  

Leave a Reply