This article describes how to rapidly download suspended messages in bulk from the BizTalk MessageBox database.


Problem


To resolve an incident in a medium-to-large sized BizTalk production site, there was a need to download some 50,000+ messages in order to resubmit them, as they could not be resumed for technical reasons (the orchestration design and underlying techniques in called web service made resume impossible). Many other scripts use the BizTalk WMI API, which is a slow method with this large number of suspended messages, but they work perfectly for a few hundred suspended messages.

Refresher on BizTalk Messages


BizTalk Message Parts


A BizTalk message has at least one part, the body part. The other parts are just referred to as Message Parts. Each message part can be of any arbitrary message type.

BizTalk Message Fragments


If a message part is larger than a certain threshold value, the part is split into fragments when stored in the Message Box. The database table that holds message parts is Parts. If the part's size is smaller than a certain threshold, the Parts row contains the complete message part. If the part's size is larger than the threshold, the Parts row contains the first fragment, and the following fragments are stored in the Fragments table.

BizTalk Message Compression


A message part in the message box is compressed; the part/first fragment in Parts as well as each fragment in Fragments is individually compressed. Note that each message part is first compressed before fragmentation, but if the compressed size requires more than 1 fragment, the message part is fragmented first and then each fragment is compressed individually.

Solution


The solution consists of four major parts: A SQL query, two Powershell scripts, and a small C# program. The first Powershell script runs the SQL query, retrieves the fragments, and stores them on disk in the BizTalk compressed format. The fragments are then decompressed using the small C# program, and re-assembled to their original form with the second Powershell script.

 This article shows how to retrieve the suspended messages from the database to disk, decompress them, and concatenate them into their original form.

Usage Overview


Refer to the Usage Details section below before actually following these instructions.
1. Save these files in a new location.
2. Compile the C# program.
3. Adjust the SQL and Powershell scripts.
4. Create a subfolder for data.
5. Run the Powershell script to retrieve the data.
6. Decompress the files using the compiled program.
7. Move away the compressed files.
8. Concatenate the fragments
9. Done!

Usage Details


1. Copy the files to a new folder:
  • This example uses E:\tmp\get-suspended-messages\.
  • Start powershell from that folder (otherwise the process' current directory will be the Windows' System32 directory).
cd /d E:\tmp\get-suspended-messages\
powershell

2. Compile the C# program:
  • Choose the same framework version as your BizTalk version requires, preferably compile it on the BizTalk server:
c:\Windows\Microsoft.NET\Framework64\v4.0.30319\csc.exe -out:biztalk-decompress-message.exe biztalk-decompress-message.cs

3. Edit the SQL and Powershell files:
  • Add where-conditions in the SQL query file, for example if you only want to select a certain message type. NULL for messages with no message type.
  • Set server name, instance name, port number, and database name in the Powershell script.
4. Create a subfolder 'data' for data files:

New-Item -Path . -Name data -Type Directory -Force

5. Run the Powershell script:

.\biztalk-get-suspended-messages.ps1 .\biztalk-get-suspended-messages.sql .\data

6. Decompress the files:
  • The syntax for the biztalk-decompress-message.exe program is:
    biztalk-decompress-message.exe filename.compressed [BizTalk program files folder]
  • The compressed file's name must end with .compressed.
  • The second argument is the folder name of your BizTalk installation (where Microsoft.BizTalk.Pipeline.dll resides), the program defaults to "D:\Program Files (x86)\Microsoft BizTalk Server 2013".
Get-ChildItem -Path .\data -Filter "*.compressed" | % { .\biztalk-decompress-message.exe $_.FullName }

7. Move the original compressed files to a subfolder, to get them out of the way for the concatenation:

New-Item -Path .\data\ -Name compressed -Type Directory -Force
Move-Item .\data\*.compressed -Destination .\data\compressed\
   
8. Concatenate the fragments to rebuild the files:

.\concatenate-fragments.ps1 .\data\

9. Done!
  • The concatenated, complete files have the extension .out.

Key parts in the scripts and the program

SQL Query


01.SELECT
02.       s.[uidMessageID ] as [uidMessageID]
03.       ,p.uidPartID
04.       ,s.dtTimeStamp
05.       ,s.nvcBodyPartName
06.       ,mp.nvcPartName
07.       ,s.nvcMessageType
08.       ,mp.nBodyPart as [IsBodyPart]
09.       ,p.nPartSize           as [UncompressedLengthPart]
10.       ,DATALENGTH(p.imgPart) as [DatalengthPart]
11.       ,p.nNumFragments
12.       ,f.nFragmentNumber
13.       ,f.nFragmentSize       as [UncompressedLengthFragment]
14.       ,DATALENGTH(f.imgFrag) as [DatalengthFragment]
15.       ,p.imgPart
16.       ,f.imgFrag
17.FROM [Spool] s WITH(NOLOCK)
18.LEFT JOIN MessageParts mp WITH(NOLOCK) ON s.[uidMessageID ] = mp.[uidMessageID ]
19.LEFT JOIN Parts p WITH(NOLOCK) ON mp.uidPartID = p.uidPartID
20.LEFT JOIN Fragments f WITH(NOLOCK) ON p.uidPartID = f.uidPartID
21.WHERE
22.s.uidBodyPartID is not null -- orphans have null here
23.--and s.nvcMessageType = 'my-specific-message-type'

The SQL Query file is read by the Powershell script. It was developed for a BizTalk Server 2013 database (not R2). Noteworty is that the Spool table's uidMessageID column name actually has a terminating space, hence the need for bracketing its name as s.[uidMessageID ] on line 2. Note that there is no need for a sort clause, as BizTalk serves us with the fragment number.
The last line, line 23, is where you can edit if you want to download only a specific message type. Don't edit the existing column names without also edit the Powershell script.

Powershell Script: biztalk-get-suspended-messages.ps1


01.param
02.(
03.  [parameter (Mandatory = $true)] [string]$QueryFileName
04. ,[parameter (Mandatory = $true)] [string]$folderName
05.)
06.Push-Location
07.# 0x0100000 = 1 Mbyte
08.$RSmessages = Invoke-Sqlcmd -ServerInstance "servername\instancename,portnumber" -Database "BizTalkMsgBoxDb" -InputFile $QueryFileName -MaxBinaryLength 0x0100000
09.$RSmessages | % {
10.  if ($_.nvcMessageType -ne [System.DBNull]::Value)
11.  {
12.    $messageTypePart = $_.nvcMessageType.Replace(":", "_").Replace("/", "_")
13.  }
14.  else
15.  {
16.    $messageTypePart = "no_message_type"
17.  }
18.  $baseFileName = ($_.nvcBodyPartName.ToString() + "_" + $messageTypePart + "_" + $_.uidMessageID.ToString())
19.  $isCompressed = ""
20.  if ( (($_.nNumFragments -eq 1) -and ($_.UncompressedLengthPart -ne $_.DatalengthPart)) -or (($_.nNumFragments -gt 1) -and ($_.UncompressedLengthFragment -ne $_.DatalengthFragment)) )
21.  {
22.    $isCompressed = ".compressed"
23.  }
24. 
25.  $fragmentNumber = 0
26.  $fragmentNamePart = "_fragment" + $fragmentNumber.ToString("000")
27.  if (($_.nNumFragments -eq 1) -or (($_.nNumFragments -gt 1) -and ($_.nFragmentNumber -eq 1)))
28.  {
30.    $fileName = "$baseFileName$fragmentNamePart$isCompressed"
31.    $bytesToWrite = $_.DatalengthPart
32.    Write-Output "Writing imgPart $bytesToWrite bytes to $fileName"
33.    [System.IO.File]::WriteAllBytes("$folderName\$fileName", $_.imgPart[0..($bytesToWrite-1)])
34.  }
35.  if ($_.nNumFragments -gt 1)
36.  {
37.    $fragmentNumber = $_.nFragmentNumber
38.    $fragmentNamePart = "_fragment" + $fragmentNumber.ToString("000")
39.    $fileName = "$baseFileName$fragmentNamePart$isCompressed"
40.    $bytesToWrite = $_.DatalengthFragment
41.    Write-Output "Writing imgFrag $bytesToWrite bytes to $fileName"
42.    [System.IO.File]::WriteAllBytes("$folderName\$fileName", $_.imgFrag[0..($bytesToWrite-1)])
43.  }
44.}
45.Pop-Location

The first Powershell script loads and runs the query, and reads the data and writes them to disk. The file name is built with message part name, message type, message ID, and fragment number. The first fragment, number 000, is always the data from the [Parts] table.
Lines 10 – 17: If there is no message type, for example when the message is sent through the PassThru pipeline, the Spool table column contains NULL, and the script then sets a hard-coded message type "no_message_type".
Lines 19 – 23: If the data is compressed, the file extension is hard-coded to ".compressed". This is expected from the decompress program.

C# Program: biztalk-decompress-message.cs


01.using System;
02.using System.IO;
03.using System.Reflection;
04. 
05.public class MyClass
06.{
07.    public static void DecompressFile(string inputFilename, string outputFilename, Type compressionStreamsType)
08.    {
09.        var compressedInputStream = File.OpenRead(inputFilename);
10.        var decompressedStream = (Stream)compressionStreamsType.InvokeMember("Decompress", BindingFlags.Public | BindingFlags.InvokeMethod | BindingFlags.Static, null, null, new object[] { (object)compressedInputStream });
11.         
12.        int decompressedLength = (int)decompressedStream.Length;
13.        BinaryReader reader = new BinaryReader(decompressedStream);
14.        var messageBody = reader.ReadBytes(decompressedLength);
15.        FileStream outputFS = File.OpenWrite(outputFilename);
16.        outputFS.Write(messageBody, 0, decompressedLength);
17.        outputFS.Flush();
18.        outputFS.Close();
19.        WL(String.Format("Wrote {0} bytes to {1}", decompressedLength, outputFilename));
20.        compressedInputStream.Close();
21.    }
22.     
23.    #region Helper methods
24.     
25.    public static int Main(string[] args)
26.    {
27.        try
28.        {
29.            WL("Decompressing BizTalk message parts/fragments");
30.            if (args.Length < 1 || args.Length > 2)
31.            {
32.                WL("Syntax: biztalk-decompress-message.exe filename.compressed [BizTalk program files folder]\r\nThe compressed-filename must end with .compressed");
33.                return 1;
34.            }
35.            string inputFilename = args[0];
36.            string extension = Path.GetExtension(inputFilename);
37.            if (extension != ".compressed")
38.            {
39.                WL("Supplied file does not end with .compressed");
40.                return 2;
41.            }
42.            if (!File.Exists(inputFilename))
43.            {
44.                WL("Supplied file does not exist: " + inputFilename);
45.                return 3;
46.            }
47.            string outputFilename = Path.GetFileNameWithoutExtension(inputFilename);
48.            if (String.IsNullOrEmpty(outputFilename))
49.            {
50.                WL("Supplied file was only extension");
51.                return 4;
52.            }
53.            outputFilename = Path.Combine(Path.GetDirectoryName(inputFilename), outputFilename);
54.             
55.            string BizTalkFolder = @"D:\Program Files (x86)\Microsoft BizTalk Server 2013";
56.            if (args.Length == 2)
57.            {
58.                BizTalkFolder = args[1];
59.            }
60.             
61.            Assembly pipelineAssembly = Assembly.LoadFrom(Path.Combine(BizTalkFolder, "Microsoft.BizTalk.Pipeline.dll"));
62.            Type compressionStreamsType = pipelineAssembly.GetType("Microsoft.BizTalk.Message.Interop.CompressionStreams", true);
63.             
64.            DecompressFile(inputFilename, outputFilename, compressionStreamsType);
65.            return 0;
66.        }
67.        catch (Exception e)
68.        {
69.            string error = string.Format("---\nThe following error occurred while executing the program:\n{0}\n---", e.ToString());
70.            Console.WriteLine(error);
71.            return 9;
72.        }
73.        finally
74.        {
75.        }
76.    }
77. 
78. 
79.    private static void WL(object text, params object[] args)
80.    {
81.        Console.WriteLine(text.ToString(), args);  
82.    }
83. 
84.     
85.    private static void RL()
86.    {
87.        Console.ReadLine();
88.    }
89. 
90.     
91.    private static void Break()
92.    {
93.        System.Diagnostics.Debugger.Break();
94.    }
95. 
96.    #endregion
97.}

The decompression uses the BizTalk classes in Microsoft.BizTalk.Message.Interop.CompressionStreams, found in assembly Microsoft.BizTalk.Pipeline.dll. The program needs to know the folder from where it can load this assembly; there is a hard-coded default value if it isn't supplied on the command line. The most part of the Main() function is command line parsing and validation.
To compile this program, you should use the C# compiler supplied with the .Net Framework used by your BizTalk version. The program is simple and has no static dependencies other than the mscorlib.dll.
Lines 7 – 21: The core decompression function. It calls the Decompress() method in the CompressionStreams library.
Lines 61 – 62: Loads the assembly and retrieves the CompressionStreams type from the assembly.

Powershell Script: concatenate-fragments.ps1


01.param
02.(
03.  [parameter (Mandatory = $true)] [string]$folderName
04.)
05. 
06.# First, create the main file with the first (and possibly only) fragment:
07.Get-ChildItem .\data\*_fragment000 | ForEach-Object {
08.  $dataDir = $_.Directory
09.  $isMatch = $_.Name -match '(.*)_fragment000'
10.  $mainName = $Matches[1].ToString() + ".out"
11.  Write-Host "First fragment of $dataDir\$mainName"
12.  Copy-Item $_.FullName -Destination $dataDir\$mainName
13.}
14.# Then, concatenate the other possible fragments in order:
15.Get-ChildItem .\data\*_fragment??? -Exclude *_fragment000 | Sort-Object | ForEach-Object {
16.  $dataDir = $_.Directory
17.  $isMatch = $_.Name -match '(.*)_fragment(\d{3})'
18.  $mainName = $Matches[1].ToString() + ".out"
19.  $fragmentNo = $Matches[2].ToString()
20.  Write-Host "Adding fragment $fragmentNo to $dataDir\$mainName"
21.  Get-Content $_.FullName -Raw -Encoding Byte | Add-Content $dataDir\$mainName -Encoding Byte
22.}

The second Powershell script concatenates the decompressed fragments into complete files. First, it just copies the first fragment (number 000) to the resulting .out file. Then, any succeeding fragments are appended in fragment number order to the resulting .out file. The main file name is everything up to the _fragmentNNN part, using a regular expression.
Line 7: Selects only the fragment000 files
Line 12: Creates the resulting .out file using Copy-Item instead of Set-Content. If you use Get-Content and pipe to Set-Content, the latter needs -NoNewLine to not add a terminating new-line. That option was added in Powershell 5.0, so the safest and simplest way here is to use Copy-Item instead.
Line 15: Selects all fragment files, excluding the 000 files as they were already copied in the first step, then sorts them in order.
Line 21: Get-Content piped to Add-Content is used here to append to the file, using Byte encoding. Add-Content does not suffer from any new-line insertions.

Caveats


The current file naming scheme will be confusing for us humans with several multi-part messages of the same type, since the first file name part is the message part name, followed by message type name.

Source Code


The code is available on GitHub:
Web link: https://github.com/PeterLindgren-se/biztalk-get-suspended-messages-sql
Repository link: https://github.com/PeterLindgren-se/biztalk-get-suspended-messages-sql.git

Improvements


A big improvement would be to incorporate the decompression into the Powershell script. Regardless, a wrapper script that performs these steps would also be a welcome improvement.


See Also


Web resources:
TechNet resources:
Microsoft documentation:

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself.
The best entry point is 
BizTalk Server Resources on the TechNet Wiki.