Hi All,
A few days ago one of our client go live on production environment and our BizTalk production environment burst with number of messages received from client and in-out flows with BizTalk.
The Production support team is not BizTalk expert team and they want some utility which search a word inside the message and context.
To fulfil this requirement we have developed the simple dotnet utility which accepts the blob messages from BizTalkDTADb.dbo.Parts table, decompress the messages and context and search user provided text in to match in message and context.
I have followed below steps, to achive this requirement.
1) Make sure Global Tracking is enabled so that we are able to get tracked messages.
2) Two most important DLL reference need to add in console application
Microsoft.Biztalk.Interop.Agent.dll
Microsoft.BizTalk.Pipeline.dll
3) I have take the following query which poll the important column [Message],[MessageContext], [MessageID] and [ServiceInstanceId].
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]
,mioe.uidServiceInstanceId as [ServiceInstanceId]
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 ";
4) I used the GetDataSet() method to poll data from Microsoft BizTalk Server database.
5) For each Dataset row we need to decompress Message and MessageContext using the GetMessageStream() and ParseContextField() method.
6) Compare search word in each decompressed message and context and save the results in physical file in application current folder.
The result will be helpful in further analysis and monitoring purpose with BizTalk Server.
Given below is entire the code snippet of application.
using Microsoft.BizTalk.Agent.Interop;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Reflection;
using System.Text;
using System.Configuration;
namespace SearchText
{
class Program
{
static void Main(string[] args)
{
StringBuilder sb = new StringBuilder();
string strConn = System.Configuration.ConfigurationManager.AppSettings["btsMesBoxConn"]; //<add key="btsMesBoxConn" value="Data Source=YOURIPADDRESS;Initial Catalog=BizTalkDTADb;Integrated Security=True"/>
string strSQL = "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] " +
",mioe.uidServiceInstanceId as [ServiceInstanceId] " +
"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 ";
DataSet ds = GetDataSet(strConn, strSQL);
foreach (DataRow row in ds.Tables[0].Rows)
{
if (row["Message"] != System.DBNull.Value && row["MessageContext"] != System.DBNull.Value)
{
SqlBinary binData = new SqlBinary((byte[])row["Message"]);
// encoded imgPartField
MemoryStream stm = new MemoryStream(binData.Value);
Stream aStream = GetMessageStream(stm);//CompressionStreams.Decompress(stm);
//Decompress and decode binary stream
StreamReader aReader = new StreamReader(aStream);
string aMessage = aReader.ReadToEnd(); // Read message in plain format
string aContext = ParseContextField((byte[])row["MessageContext"]);
if (aMessage.Contains(args[0]) || aContext.Contains(args[0]))
{
sb.Append(string.Format("---------------------{0}ServiceInstance:{1} MessageID:{2}{3}---------------------{4}Message Context:{5} {6}Message Details:{7}{8}_____________________{9}", Environment.NewLine, Convert.ToString(row["ServiceInstanceId"]), Convert.ToString(row["MessageID"]), Environment.NewLine, Environment.NewLine, aContext, Environment.NewLine, aMessage, Environment.NewLine, Environment.NewLine));
}
}
}
System.IO.File.WriteAllText(Environment.CurrentDirectory + "\\" + System.Guid.NewGuid().ToString() + ".txt", sb.ToString());
}
internal static Stream GetMessageStream(Stream stream)
{
Assembly pipelineAssembly = Assembly.LoadFrom(string.Concat(Environment.CurrentDirectory, @"\Microsoft.BizTalk.Pipeline.dll"));
Type compressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
return (Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream });
}
internal static string ParseContextField(byte[] contxt)
{
string message = null;
//You can then walk through the context as follows:
//MemoryStream stream = new MemoryStream((byte[])reader["imgContext"]); // result of calling ops_LoadTrackedMessageContext
MemoryStream stream = new MemoryStream((contxt)); // 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());
//if (ctxProperties == null || ctxProperties.Contains(propName))
//{
// if (!bHideContextPropertyName)
// {
message += propName.ToString() + " = ";
//}
message += propValue.ToString() + "; " + Environment.NewLine;
//}
}
return message;
}
internal static DataSet GetDataSet(string ConnectionString, string SQL)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = SQL;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close();
return ds;
}
}
}
Here is the Syntax to run the utility from command prompt,
C:\SearchText> SearchText.exe "your search text"
And the Text result will looks like below.
A few days ago one of our client go live on production environment and our BizTalk production environment burst with number of messages received from client and in-out flows with BizTalk.
The Production support team is not BizTalk expert team and they want some utility which search a word inside the message and context.
To fulfil this requirement we have developed the simple dotnet utility which accepts the blob messages from BizTalkDTADb.dbo.Parts table, decompress the messages and context and search user provided text in to match in message and context.
I have followed below steps, to achive this requirement.
1) Make sure Global Tracking is enabled so that we are able to get tracked messages.
2) Two most important DLL reference need to add in console application
Microsoft.Biztalk.Interop.Agent.dll
Microsoft.BizTalk.Pipeline.dll
3) I have take the following query which poll the important column [Message],[MessageContext], [MessageID] and [ServiceInstanceId].
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]
,mioe.uidServiceInstanceId as [ServiceInstanceId]
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 ";
4) I used the GetDataSet() method to poll data from Microsoft BizTalk Server database.
5) For each Dataset row we need to decompress Message and MessageContext using the GetMessageStream() and ParseContextField() method.
6) Compare search word in each decompressed message and context and save the results in physical file in application current folder.
The result will be helpful in further analysis and monitoring purpose with BizTalk Server.
Given below is entire the code snippet of application.
using Microsoft.BizTalk.Agent.Interop;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.Reflection;
using System.Text;
using System.Configuration;
namespace SearchText
{
class Program
{
static void Main(string[] args)
{
StringBuilder sb = new StringBuilder();
string strConn = System.Configuration.ConfigurationManager.AppSettings["btsMesBoxConn"]; //<add key="btsMesBoxConn" value="Data Source=YOURIPADDRESS;Initial Catalog=BizTalkDTADb;Integrated Security=True"/>
string strSQL = "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] " +
",mioe.uidServiceInstanceId as [ServiceInstanceId] " +
"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 ";
DataSet ds = GetDataSet(strConn, strSQL);
foreach (DataRow row in ds.Tables[0].Rows)
{
if (row["Message"] != System.DBNull.Value && row["MessageContext"] != System.DBNull.Value)
{
SqlBinary binData = new SqlBinary((byte[])row["Message"]);
// encoded imgPartField
MemoryStream stm = new MemoryStream(binData.Value);
Stream aStream = GetMessageStream(stm);//CompressionStreams.Decompress(stm);
//Decompress and decode binary stream
StreamReader aReader = new StreamReader(aStream);
string aMessage = aReader.ReadToEnd(); // Read message in plain format
string aContext = ParseContextField((byte[])row["MessageContext"]);
if (aMessage.Contains(args[0]) || aContext.Contains(args[0]))
{
sb.Append(string.Format("---------------------{0}ServiceInstance:{1} MessageID:{2}{3}---------------------{4}Message Context:{5} {6}Message Details:{7}{8}_____________________{9}", Environment.NewLine, Convert.ToString(row["ServiceInstanceId"]), Convert.ToString(row["MessageID"]), Environment.NewLine, Environment.NewLine, aContext, Environment.NewLine, aMessage, Environment.NewLine, Environment.NewLine));
}
}
}
System.IO.File.WriteAllText(Environment.CurrentDirectory + "\\" + System.Guid.NewGuid().ToString() + ".txt", sb.ToString());
}
internal static Stream GetMessageStream(Stream stream)
{
Assembly pipelineAssembly = Assembly.LoadFrom(string.Concat(Environment.CurrentDirectory, @"\Microsoft.BizTalk.Pipeline.dll"));
Type compressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
return (Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)stream });
}
internal static string ParseContextField(byte[] contxt)
{
string message = null;
//You can then walk through the context as follows:
//MemoryStream stream = new MemoryStream((byte[])reader["imgContext"]); // result of calling ops_LoadTrackedMessageContext
MemoryStream stream = new MemoryStream((contxt)); // 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());
//if (ctxProperties == null || ctxProperties.Contains(propName))
//{
// if (!bHideContextPropertyName)
// {
message += propName.ToString() + " = ";
//}
message += propValue.ToString() + "; " + Environment.NewLine;
//}
}
return message;
}
internal static DataSet GetDataSet(string ConnectionString, string SQL)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlDataAdapter da = new SqlDataAdapter();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = SQL;
da.SelectCommand = cmd;
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close();
return ds;
}
}
}
Here is the Syntax to run the utility from command prompt,
C:\SearchText> SearchText.exe "your search text"
And the Text result will looks like below.
