C# solution to decompress BizTalk messages and their context
This documentation is derived from the original documentation as written in
As most of you will know BizTalk messages and context are stored in the MessageBox and the Tracking database as Image types. When we had an incident with quite some suspended (not resumable) instances, we needed to retrieve message content and context. This
information should be used to take appropriate actions for damage elimination.
After a search I found
this great article from Thiago Almeida. It even contains a sample solution! Good news though for an intermediate developer as me!
I've been working on the solution and made it use just the SQL method. My goal was to create functions in C#, that would decompress the fields that contains the message and the context and enable the user to use these functions in SQL queries they entered in
a textbox. It ended up in the screen below.
How it works
After selecting the database server which contains the BizTalk databases, you select the MessageBox database. In the Query textfield you can enter your SELECT query. As you might have noticed already, there are 2 non-SQL functions in the currently entered query,
@MessageToText() - used to decompress messages
@ContextToText() - used to decompress message context
These are the functions I designed to decompress the message content and context. Before the SQL query is fired at the database, the application will strip these functions (and their optional parameters) for later use, when the resultset is returned from the
So basically all you have to do is write a SQL query and when you have a field which contains message content or message context, surround that field with the appropriate function to have the message content or context returned from your MessageBox!
Since messages can be pretty large and you might not need the entire XML from it, or maybe you are not interested in all the context properties, I extended the functions to show just certain XML and/or certain properties from the message context.
Show just a part of the XML message
When you just need a certain part of the XML, you can enter a XPath query and the message will return just that XML. When the message doesn't contain the nodes, elements or attributes mentioned in th XPath query, an empty string is returned.
The @MessageToText() function than becomes called as follows:
@MessageToText(<field that contains the message>;"<XPath query>")
Show just certain context properties
When you just need certain context properties, you can select them by entering them in a semicolon seperated list, like this:
@ContextToText(<field that contains the message context>;<Context property>;<Context property>)
Example: @ContextToText(imgContext;MessageType; PortName; OrderType)
You can enter as many or little properties you need. Entering non existant (or typos) properties has no negative side effects.
Further I built a couple of small features.
- Just like in SQL Server Management Studio (SSMS), when you select a part of the query, only that part is fired at the database.
- When you have a decompressed a message and want to create a XPath query, you can copy the XML from the message, hit the 'Parse XML...' button, paste the XML in the dialog that appeared and experience with the XPath query. When you're done testing the XPath
query, you can copy it, close the dialog and paste the XPath at the appropriate position in the Query field.
- To get a 'clean' resultset, you can choose to hide the Outer XML tag and/or the name of the context properties.
- You can choose whether you want to see the field names in the resultset.
- For better readability you can add whitespace between the records in the resultset
- Word wrap is supported in the Output box
- To protect you and your MessageBox from heavy load, only the top xx number of records will be returned, depending on the value selected in the dropdown box.
Recommendations on accessing the BizTalk databases
There are a couple of guidelines when accessing the BizTalk databases:
- Only do this when no other options are available
- Just do SELECT queries
- Don't forget the WITH(NOLOCK) hint
- Never change existing BizTalk tables, indexes, triggers, SP's, etc...
If you call in Microsoft for help and they find modified BizTalk objects, you will be fully charged for fixing the problems.
The decompressing is done by the so called BTS Accessor. This standard BizTalk DLL needs to be installed on the machine where you run the decompress tool.
Also the following assemblies must be installed:
Enjoy the tool and if you have any questions or comments, don't hesitate!