BizTalk Message and BizTalk Message Context monitoring

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.

EDI/AS2 BizTalk Configuration Failed: DTS package BAM_DM_InterchangeStatusActivity already exists

During the Configuration of EDI/AS2 on BizTalk Server Configuration Wizard, sometimes you may encounter the following error, this is due to the BAM Activity, BAM Activity Definitations, BAM Indexes are already configured or available on the machine where you configuring the EDI/AS2 feature.

Error: The BAM deployment failed. DTS package BAM_DM_InterchangeStatusActivity already exists on server <SQL Server Name>.

To over come this issue, there are two options.

1) one you follow the Microsoft support page and download the fix (Refer Link#1).

2) Manually remove the BAM Activities, Denifitions and Indexes using the following command.


Use the BM.exe which will be located at the %btsinstallpath%\Tracking folder


Bm.exe remove-all -DefinitionFile:"C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\AS2ReportingActivityDefs.xml"

Bm.exe remove-all -DefinitionFile:"C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\EdiReportingActivityDefs.xml"

Bm.exe remove-all -DefinitionFile:"C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\EdiReportingActivityIndexes.xml"

After successful above command execution Re-Configure the EDI/AS2 features in BizTalk Configuration wizard and it will successful configure the EDI/AS2 Runtimes.


Link#1 https://support.microsoft.com/en-in/help/2269514/the-biztalk-edi-as2-runtime-configuration-may-fail-with-error-dts-pack


Hope this Helps!!!!

Accessing UNC Path folders like local drive

When there is need to access the UNC Path folders with local command prompt functionality we can achieve this with the help of NET USE command

following is the syntax of NET USE command

 NET USE
[devicename | *] [\\computername\sharename[\volume] [password | *]]
        [/USER:[domainname\]username]
        [/USER:[dotted domain name\]username]
        [/USER:[username@dotted domain name]
        [/SMARTCARD]
        [/SAVECRED]
        [[/DELETE] | [/PERSISTENT:{YES | NO}]]

NET USE {devicename | *} [password | *] /HOME

NET USE [/PERSISTENT:{YES | NO}]

Example : NET USE X: \\123.456.78.9\somesharedfolder\

after successfully execution of above command newly mapped drive X: is available to use it like a local machine drive.

Hope this Helps!!!

BizTalk Server Read-Only access

Once we move application to Production environment, it is become necessary to provide very tight security to BizTalk Administration Console as well as the users (Developers, Production Monitoring Users or Support Professionals) who need to monitor the application on regular basis.

To overcome the issue  out-of-box Biztalk provides the role BizTalk Operator who will have limited access to BizTalk Administration Console, though there are some area where out-of-box biztalk functionality needs some alteration.

One instance is Default BizTalk Operators Group users can Start/Stop Receive Locations, Send Port and Orchestrations, To maintain high level of security some client require us not to provide any update access to these areas as well.

We can achieve this by revoking Execute Permissions from 'BTS_Operators' database role on following stored procedures of BizTalkMgmtDB database.

[bts_ReceiveLocation_Status_Update]

[bts_SendPort_Status_Update]

[bts_SendPortGroup_Status_Update]


[bts_Orchestration_Status_Update]


Hope this Helps!!!!

MS-SQL Query to get count of Spool size filter by MessageType

Take a scenario you are in middle of Heavy message load flooded in BizTalk system on your Production environment and you want to find which type of message are heavily available in Spool table which need attention to be cleanup or process.

By using the following MS-SQL Query you will get the list of records separated by Message Type in Spool Table with Message Type name.

USE BizTalkMsgBoxDb

SELECT nvcMessageType, count(*) AS MessageCount 
FROM dbo.Spool (NOLOCK)
GROUP BY nvcMessageType
ORDER BY MessageCount DESC

Hope this Helps!!!!

Bulk copy files from one folder to another using powershell

Suppose you have a thousands of files in one folder and its sub folders and you have given a task to filter out few files which you already identified is available but to find each file and copy them to other folder is quite tedious one. especially urgency of files filter is very high.

to overcome this issue and resolve this situation very smoothly i have come up with given power shell command which i am hoping to helpful to you people as well.

$file_list = Get-Content C:\fileslist.txt
$search_folder = "C:\SourceFolder"
$destination_folder = "C:\Backup\DestinationFolder"

foreach ($file in $file_list) {
    $file_to_move = Get-ChildItem -Path $search_folder -Filter $file -Recurse -ErrorAction SilentlyContinue -Force | % { $_.FullName}
    if ($file_to_move) {
        Move-Item $file_to_move $destination_folder
    }
}

all you need to do is fill your list of files in filelist.txt file and run the below power shell command. after successful completion you will able to see the filtered files in your destination folder location.


Hope this Helps!!!! 

Know your right MS-DTEexc version using the Powershell

Some times when you encounter with some weird error stating the wrong version of MS-DTExec you are using, your first course of action should be to check the version you are calling is right or not.

Error:
   Code: 0xC000F427
   Source: Receive SS_SALES
   Description: To run a SSIS package outside of SQL Server Data Tools you must
install Receive SS_SALES of Integration Services or higher.


With the help of following Powershell script command you are able to identify list of all available/installed on your machine.

PS C:\Windows\system32> Get-ChildItem C:\Progra*\dtexec.exe -Recurse | %{
$_.FullName
$version = &$_;
$version -like "*Version*"
Write-Host
}

Hope this Helps!!!!