<< PLEASE READ THE COMMENTS SECTION FOR MORE ACCURATE DETAILS >>
I know often people run into the need to extract a message from the BizTalk tracking database. I thought I’d write a post on the 3 methods I’ve found so far over time.
As some of you know the body and context of messages in BizTalk are compressed, and rightfully so. The actual compression and decompression code is hidden inside a dll called BTSDBAccessor.dll. This DLL, the “BizTalk Database Acessor”, is unmanaged and does a lot of work for BizTalk including accessing the BizTalk databases to send and receive messages.
Based on blog sites and forums I’ve run into over time I know of three ways we can programmatically get a message body out of the tracking database: Operations DLL, SQL, and WMI. I’ve created a little test C# Windows application that uses all three.
The application has only one form (print-screen below), and expects the following parameters:
– The message guid of the message you want to extract.
– The extraction type (Use Operations DLL, Use SQL, Use WMI)
– Tracking DB server (the BizTalk server name)
– Tracking DB name (the BizTalk Tracking database name)
Here is the code for the “Get Message” button:
private void btnGetMessage_Click(object sender, EventArgs e)
{
txtMessage.Clear();
txtMessage.Refresh();
GuidmessageGuid;
try
{
messageGuid = new Guid(txtGuid.Text);
}
catch (ExceptionexGuid)
{
txtMessage.Text = "Please enter a valid Guid. Error: "+ exGuid.Message;
return;
}
switch(cboGetType.SelectedIndex)
{
case 0:
txtMessage.Text = GetMessageWithOperations(messageGuid);
break;
case 1:
txtMessage.Text = GetMessageWithSQL(messageGuid);
break;
case 2:
txtMessage.Text = GetMessageWithWMI(messageGuid);
break;
default:
break;
}
}
Now, let’s finally see the three ways of getting to the message body, shall we?
1. My favourite, use the Microsoft.BizTalk.Operations dll. This is pretty straight forward, you add a reference to Microsoft.BizTalk.Operations.dll and use the GetTrackedMessage of the BizTalkOperations class. You can also get to the message context using this method. This is only for BizTalk 2006 and later. Here is the code:
//Retrieves the message using the operations DLL - Add Microsofr.BizTalk.Operations.dll to references
public string GetMessageWithOperations(GuidMessageInstanceId)
{
try
{
TrackingDatabasedta = new TrackingDatabase(txtTrackingDBServer.Text, txtTrackingDBName.Text);
BizTalkOperations operations = new BizTalkOperations();
IBaseMessagemessage = operations.GetTrackedMessage(MessageInstanceId, dta);
string body = string.Empty;
using (StreamReaderstreamReader = new StreamReader(message.BodyPart.Data))
{
body = streamReader.ReadToEnd();
}
return body;
}
catch (ExceptionexOp)
{
return "Failed to get message with id "+ MessageInstanceId.ToString() + " from tracking database: "+ exOp.Message;
}
}
Here’s the great post mentioning this method by Richard Hallgren:
http://www.richardhallgren.com/reading-the-message-body-and-context-from-the-biztalkdtadb-using-operations-library-in-biztalk-2006/
2. Use the WMI MSBTS_TrackedMessageInstance.SaveToFile method to save the instance to disk. This was the popular method in BizTalk 2004 since there was no operations dll then. Here is the code:
//Uses WMI to save the tracked message out to a file folder using MSBTS_TrackedMessageInstance class
public stringGetMessageWithWMI(GuidMessageInstanceId)
{
try
{
// Construct full WMI path to the MSBTS_TrackedMessageInstance using the message guid (NOTE: MessageInstanceID string value must be enclosed in {} curly brackets)
string strInstanceFullPath = "\\\\.\\root\\MicrosoftBizTalkServer:MSBTS_TrackedMessageInstance.MessageInstanceID='{"+ MessageInstanceId.ToString() + "}'";
// Load the MSBTS_TrackedMessageInstance
ManagementObject objTrackedSvcInst = new ManagementObject(strInstanceFullPath);
// Invoke "SaveToFile" method to save the message out into the specified folder
objTrackedSvcInst.InvokeMethod("SaveToFile", new object[] {Application.StartupPath});
//Get all files in the directory starting with this messageid
string[] files = Directory.GetFiles(Application.StartupPath, "{"+ MessageInstanceId.ToString() + "*.*");
string message = "";
foreach (string file in files)
{
if(file.EndsWith(".out"))
{
using (StreamReadersr = new StreamReader(file))
{
message = sr.ReadToEnd();
}
}
}
foreach (string file in files)
{
System.IO.File.Delete(file);
}
if (files.Length == 0)
{
throw new Exception("No files found on folder that match the GUID");
}
return message;
}
catch (ExceptionexWMI)
{
return "Failed to save tracked message with id "+ MessageInstanceId.ToString() + " into folder " + Application.StartupPath + ": "+ exWMI.Message;
}
}
http://kentweare.blogspot.com/2007/05/biztalk-retrieving-tracked-messages.html
3. The bts_GetTrackedMessageParts stored procedure inside the tracking database expects the message GUID and will return the compressed message data back. We can then use reflection to invoke the Decompress method of the Microsoft.BizTalk.Message.Interop.CompressionStreams class inside Microsoft.BizTalk.Pipeline.dll to decompress the data returned from SQL. Here is the code:
//Calls BizTalk stored procedure to retrieve compressed message and decompresses it
public stringGetMessageWithSQL(GuidMessageInstanceId)
{
try
{
//Connection to DTA database on localhost
SqlConnection con = new SqlConnection("Data Source=" + txtTrackingDBServer.Text + ";Initial Catalog=" + txtTrackingDBName.Text + ";Integrated Security=True");
string message = "";
try
{
SqlCommandcmd = new SqlCommand();
SqlDataReader reader;
//Build execution of stored procedure bts_GetTrackedMessageParts
cmd.CommandText = "bts_GetTrackedMessageParts";
cmd.CommandType = CommandType.StoredProcedure;
SqlParameterguidParameter = new SqlParameter("@uidMsgID", SqlDbType.UniqueIdentifier);
guidParameter.Value = MessageInstanceId;
cmd.Parameters.Add(guidParameter);
cmd.Connection = con;
con.Open();
reader = cmd.ExecuteReader();
//Get the reader to retrieve the data
while (reader.Read())
{
//Use memory stream and reflection to get the data
SqlBinarybinData = new SqlBinary((byte[])reader["imgPart"]);
MemoryStream stream = new MemoryStream(binData.Value);
AssemblypipelineAssembly = Assembly.LoadFrom(string.Concat(@"C:\Program Files\Microsoft BizTalk Server 2006", @"\Microsoft.BizTalk.Pipeline.dll"));
TypecompressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
StreamReader st = new StreamReader((Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream }));
message = st.ReadToEnd();
}
}
finally
{
con.Close();
}
return message;
}
catch (ExceptionexSQL)
{
return "Failed to get message with id "+ MessageInstanceId.ToString() + " from tracking database: "+ exSQL.Message;
}
}
This is the post that I ran into that helped me with this method:
http://www.tech-archive.net/Archive/BizTalk/microsoft.public.biztalk.general/2007-05/msg00124.html
Hope this helps someone! Post a comment (and fill out the email address field) or use the “Contact” page if you want the source code, but it’s pretty much all here in this post.
Hello Thiago!
I tried to use your code from the first example and I had an error: System.ArgumentNullException was unhandled by user code
Message=”Value cannot be null.rnParameter name: dbName”
Source=”Microsoft.BizTalk.Operations”
ParamName=”dbName”
StackTrace:
at Microsoft.BizTalk.Operations.BizTalkDatabase.CheckInputParams(String dbServer, String dbName)
…
Would you comment it?
Hi,
You have to make sure you type the correct Tracking DB server and Tracking DB name in the appropriate boxes. Are you using BizTalk 2004? In 2006 R2 it shows an “invalid input parameter Parameter name: dbName” error.
I’ve found some problems using two of the methods:
1. Using the operation dll:s, sql connections used are not closed when message has been received. If you for instance invokes the gettrackedmessage 1000 times in a row, you will have 1000 open sql connections until the application is closed.
2. Using WMI to retrieve the message, you will have problem in a clustered environment if you try to receive messages sometimes. Out of 1000 messages, only a few percent is retrieved correctly.
Any solutions to the two problems?
Another question 😉
Does anyone know how to retrieve the message property bag / context using the last example?
The message body works fine, and I assume the context is in the imgPropBag field.
But I get exceptions when trying to decompress that.
You are partially right. I had Biztalk Server 2006, not R2. With R2 it works fine. Thank you!
Daniel,
I’m not sure you will be able to get the message context using the third method. See the “What used to be the problem?” section of Richard’s post here: http://www.richardhallgren.com/reading-the-message-body-and-context-from-the-biztalkdtadb-using-operations-library-in-biztalk-2006/
I suppose to release the connection to the tracking database you would have to dispose of the BizTalkOperations or the TrackingDatabase object – I will investigate some more tomorrow. This issue has been found before with not many comments http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3896987&SiteID=17 so it might be worth spending some time on it!
Regards, Thiago
Hi Daniel,
I’ve investigated this further (and noticed your post on the forum!). It indeed doesn’t release the connections straight away. I does release all of them when you close the application though.
It eventually releases them however: I tested calling it 226 times to save all the tracked messages I had on my deve machine. It took about 11 minutes to release all the connections if I left the application open.
Calling dta.Dispose() for the TrackingDatabase passed to the GetTrackedMessage method does not help, and even refactoring BizTalkOperations to get access to the OperationsGroup object exposed by the private _biztalkGroup property then calling it’s dispose method does not release them.
You might have to either rely on the connections getting slowly released or the “create an app domain” method you described on the forums post.
Regarding the message context, it is actually possible to get this from the database. I’ve never found it documented anywhere, but by working back through the BizTalk code using Reflector I was able to figure it out:
First add references to Microsoft.BizTalk.Interop.Agent.dll and Microsoft.BizTalk.Pipeline.dll and use the following namespaces:
using Microsoft.BizTalk.Agent.Interop;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;
Then call the stored proc ops_LoadTrackedMessageContext and then get the result into a MemoryStream as in the code above.
You can then walk through the context as follows:
MemoryStream stream = … // result of calling ops_LoadTrackedMessageContext
IBaseMessageContext context = ((IBTMessageAgentFactory)((IBTMessageAgent)new BTMessageAgent())).CreateMessageContext();
((IPersistStream)context).Load(stream);
for (int i = 0; i < context.CountProperties; ++i)
{
string propName;
string propNamespace;
object propValue = context.ReadAt(i, out propName, out propNamespace);
System.Console.Out.WriteLine(propNamespace + “, ” + propName + “: ” + propValue.ToString());
}
Nice Alister, great work on the Reflector, thanks for sharing!
Thank you Alister, great example. Works like a charm.
However, the loadtrackedmessagecontext procedure requires the spool as input, is there
a good way to retrieve this?
There’s probably a better way of finding out which tracking spool table holds your message. But one way is to call loadtrackedmessagecontext twice, first passing 1 as the spool id and then 2 as the spool id. Of course you only need to call the second one if the first one didn’t find it.
Sup Man, It works fine to me… just I need to know..
What Message Property is related to Message guid (field uidMessageId from btsv_Tracking_Parts view)? ‘coz I tried with the BTS.MessageID value but this not appear inside the view btsv_Tracking_Parts
Just thought I would add my experience to this.
I use a combination of the third method, Alister’s method to get the context (modified to create XML) and my own SQL addition. I make only one call to the database and close it immediately after. This is much more acceptable for us as our production database is pounded with transactions and having too many SQL connections open is a bad thing.
1. I use the following SQL in a stored proc with a dynamic WHERE clause (the following SQL is modified to not be dynamic) and fill a DataTable with the results.
2. I iterate through the rows, saving the message and the context where they are not DBNULL.
This could probably be further optimized, but as we don’t use this often (maybe once in four months), it works for us.
SQL:
SELECT
sf.[Service/Name] AS [Name]
,sf.[ServiceInstance/StartTime] AS [StartTime]
,sf.[ServiceInstance/EndTime] as [EndTime]
,sf.[ServiceInstance/State]
,mioe.uidMessageInstanceId AS [MessageID]
,tp.imgPart AS [Message]
,ts.imgContext as [MessageContext]
FROM
[BizTalkDTADB]..dtav_ServiceFacts sf WITH (READPAST)
LEFT JOIN [BizTalkDTADB]..dta_MessageInOutEvents mioe WITH (ROWLOCK READPAST)
ON sf.[ServiceInstance/InstanceID] = mioe.uidServiceInstanceId
AND sf.[ServiceInstance/ActivityID] = mioe.uidActivityId
LEFT JOIN [BizTalkDTADB]..btsv_Tracking_Parts tp WITH (READPAST)
ON mioe.uidMessageInstanceId = tp.uidMessageID
LEFT JOIN [BizTalkDTADB]..btsv_Tracking_Spool ts WITH (READPAST)
ON tp.uidMessageID = ts.uidMsgId
Alister’s code slightly modified:
MemoryStream ms = new MemoryStream(binContext.Value);
IBaseMessageContext mc;
mc = ((IBTMessageAgentFactory)((IBTMessageAgent)new BTMessageAgent())).CreateMessageContext();
IPersistStream per = ((IPersistStream)mc);
per.Load(ms);
StringBuilder xml = new StringBuilder();
xml.AppendLine(“”);
xml.AppendFormat(“”, mc.CountProperties);
xml.AppendLine();
string name;
string ns;
string value;
for (int i = 0; i < mc.CountProperties; i++)
{
mc.ReadAt(i, out name, out ns);
value = mc.Read(name, ns) as string;
xml.AppendFormat("”,
name, ns, value);
xml.AppendLine();
}
xml.AppendLine(“”);
xml.Append(“”);
Hmm, it appears the code didnt’ make it through the post the way it should (forgot to replace angle brackets with html code…), so I’ll try again.
StringBuilder xml = new StringBuilder();
xml.AppendLine("<MessageInfo>");
xml.AppendFormat("<ContextInfo PropertiesCount="{0}">", mc.CountProperties);
xml.AppendLine();
string name;
string ns;
string value;
for (int i = 0; i < mc.CountProperties; i++)
{
mc.ReadAt(i, out name, out ns);
value = mc.Read(name, ns) as string;
xml.AppendFormat("<Property Name="{0}" Namespace="{1}" Value="{2}" />", name, ns, value);
xml.AppendLine();
}
xml.AppendLine("</ContextInfo>");
xml.Append("</MessageInfo>");
Hi Steven, thanks for sharing!
It appears that the issue with the connections not being released when using the Microsoft.BizTalk.Operations API was fixed in BizTalk 2010. The following code that I tested does not leave any connections open in the SQL Server. Though, if the using statements are removed from the code then indeed all the connections stay open.
public IBaseMessage GetTrackedMessage(Guid messageInstanceId)
{
IBaseMessage message = null;
for (int i = 0; i < 1000; i++)
{
using (TrackingDatabase trackingDB = new TrackingDatabase(_trackingDbServer, _trackingDatabase))
{
using (BizTalkOperations operations = new BizTalkOperations())
{
message = operations.GetTrackedMessage(messageInstanceId, trackingDB);
}
}
}
return message;
}
–Vlad
Thanks for the update Vlad – we have been using the operations dll with a using statement to get the message too lately, it’s much easier. I should add an update to the post to mention that.
I used the SQL approach as we are archiving thousands of messages a day and the open connections was an issue, and I couldn’t use “using (BizTalkOperations operations = new BizTalkOperations())”, as there was an issue using the “using” approach with no Dispose interface on BizTalkOperations.
This lead me to use the SQL approach, which seemed faster as well. However, I discovered large messages were truncated at around 128K. If you have messages larger than this you will also need to call bts_GetTrackedMessageFragments to get the addition parts of the message. When you call bts_GetTrackedMessageParts for the first part of the message, it also returns uidPartId. Call bts_GetTrackedMessageFragments passing uidPartId as the @uidMsgPartID parameter. This procedure will return a record for each addtional part with the data in imgFrag, you can decompress it using the same approach as used with imgpart from the GetTrackedMessageParts call, concatenate all the decompressed messages to get the full message.
Thanks Kevin,
I have also run into the limitation of 128kb and noticed that the message fragments needed to be called as well to get the whole message. Thank you for sharing the approach here.
I changed back to using BiztalkOperations with a try… finally like the following:
Microsoft.BizTalk.Operations.BizTalkOperations btop = new BizTalkOperations();
try
{
Trace.WriteLine(“[Helpers.DocTracking.GetTrackedMessage] Getting tracked message with BizTalkOperations class”);
IBaseMessage msg = btop.GetTrackedMessage(guid); //guid of the tracked message
//Use msg.BodyPart.GetOriginalDataStream() to get to original body part’s stream
//Use msg.Context to get to original context
}
catch (Exception ex)
{
Trace.WriteLine(“[Helpers.DocTracking.GetTrackedMessage] Exception while getting tracked message with message guid ” + guid.ToString());
Trace.WriteLine(“[Helpers.DocTracking.GetTrackedMessage] Exception: ” + ex.ToString());
btop = null;
}
finally
{
btop = null;
}
Regards,
Thiago
Hi,
We have created a windows service to get the BizTalk suspended messages and post it to the the ESB portal. This service subscribes to WMI Suspended events and WMI Message Instance without consuming the suspended message in the BizTalk Suspended queue. It extracts the metada/context of the suspended message as well as the fault details then logs them to ESB Portal using ESB 2.0 Framework API. We were able to log most of the context as well as some of the fault details to ESB although we are having issues left for getting the data from BizTalk for the following.
Fault Details
Exception Details
Fault Code:
Stack Trace:
Fault Description:
Inner Exception Message
Failure Category:
Source:
Fault Severity:
Type:
Exception Type:
Target Site
Error Type:
Application:
Application Scope:
Fault Generator:
Service Name:
From this list, the critical information for us to get is the “Application Name”. We tried using WMI on this but when we checked the available WMI classes, they do not have any method/object that would provide us these info.
Great work Thiago, I like the way your examples just contain the minimum required to work, which makes it easy to understand exactly what is going on.