BizTalk Pipeline Components Extensions Utility Pack: SQL Server Polling Debatch Message By Grouping Filter Pipeline Component

by Sandro Pereira | Dec 17, 2019 | BizTalk Community Blogs via Syndication

The BizTalk Pipeline Components Extensions Utility Pack community project for BizTalk Server 2016, once again, got a new update! It now has a new component that you can use in your custom BizTalk Server pipelines: SQL Server Polling Debatch Message By Grouping Filter Pipeline Component.

Debatching messages, which are received from WCF-SQL Adapter, is quite a simple task to do. You need to:

• Specify that the message has an envelope by setting the Envelope property to Yes

• Specify the Body XPath property

• And finally, set the Max Occurs property to 1

Next, the default XMLReceive pipeline will do the rest. However, what happens if you want to extend and customize this behavior? In my case, let’s imagine I have the following row samples:

We will poll all the data from the SQL Database (not all, but let’s say the first 20 lines), and we want to split or debatch the incoming message based on the value of the MsgIdentifier property. So, after passing the receive pipeline, we will get, based on the previous example, 3 different messages:

The main question is: How do we handle scenarios where debatching is done based on business logic?

The answer is that we don’t have anything out-of-the-box that allows us to do these kinds of tasks. Most of you will think about debatching these kinds of messages inside an orchestration.

However, the best way for us to achieve this goal is to develop a custom Disassemble pipeline component.

SQL Server Polling Debatch Message By Grouping Filter Pipeline Component

The reason for choosing the Disassemble stage is simple; it is here that normally the process of breaking up a large interchange message into smaller messages happens, by removing the Envelopes, which is often called “debatching”. So, this should indeed be the place for us to create our custom Disassemble pipeline component.

The SQL Server Polling Debatch Message By Grouping Filter pipeline component is a custom disassemble pipeline component that can be used to debatch incoming messages from the WCF-SQL adapter, by filtering from a specific element of the message. It will provide the following capabilities:

• It allows you to define the grouping element
  • This will be an integer that will define the position of the element inside the message after the source code

Note: This custom pipeline component can be used with other LOB adapters but it was never tested before.

public void Disassemble(Microsoft.BizTalk.Component.Interop.IPipelineContext pc, Microsoft.BizTalk.Message.Interop.IBaseMessage inmsg)
{
    string originalDataString;

    try
    {
        //fetch original message
        Stream originalMessageStream = inmsg.BodyPart.GetOriginalDataStream();
        byte[] bufferOriginalMessage = new byte[originalMessageStream.Length];
        originalMessageStream.Read(bufferOriginalMessage, 0, Convert.ToInt32(originalMessageStream.Length));
        originalDataString = System.Text.ASCIIEncoding.ASCII.GetString(bufferOriginalMessage);
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Error in reading original message: " + ex.Message);
    }

    XmlDocument originalMessageDoc = new XmlDocument();
    StringBuilder messageString;
    string msgBatchId = string.Empty;

    try
    {
        //load original message
        originalMessageDoc.LoadXml(originalDataString);
                
        //fetch namespace and root element
        string namespaceURI = originalMessageDoc.DocumentElement.NamespaceURI;
        string rootElement = originalMessageDoc.DocumentElement.Name;

        //start batching messages
        messageString = new StringBuilder();
        messageString.Append("<" + rootElement + " xmlns:ns0='" + namespaceURI + "'>");
        string rowId = string.Empty;

        foreach (XmlNode childNode in originalMessageDoc.DocumentElement.ChildNodes)
        {
            messageString.Append("<" + childNode.Name + ">");
            foreach (XmlNode rows in childNode.ChildNodes)
            {
                rowId = rows.ChildNodes[this.GroupingElement].InnerText;

                if (msgBatchId == string.Empty)
                    msgBatchId = rowId;

                if (msgBatchId != rowId)
                {
                    messageString.Append("</" + childNode.Name + ">");
                    messageString.Append("</" + rootElement + ">");

                    //Queue message
                    CreateOutgoingMessage(pc, messageString.ToString(), namespaceURI, rootElement);

                    msgBatchId = rowId;

                    messageString.Remove(0, messageString.Length);
                    messageString.Append("<" + rootElement + " xmlns:ns0='" + namespaceURI + "'>");
                    messageString.Append("<" + childNode.Name + ">");
                    messageString.Append(rows.OuterXml);
                }
                else
                {
                    messageString.Append(rows.OuterXml);
                }
            }
            messageString.Append("</" + childNode.Name + ">");
        }

        messageString.Append("</" + rootElement + ">");

        CreateOutgoingMessage(pc, messageString.ToString(), namespaceURI, rootElement);
    }
    catch (Exception ex)
    {
        throw new ApplicationException("Error in writing outgoing messages: " + ex.Message);
    }
    finally
    {
        messageString = null;
        originalMessageDoc = null;
    }


    // _msgs.Enqueue(inmsg);
}

The source code shown here is just a snippet. To be able to use this, you can download the latest version from the BizTalk Pipeline Components Extensions Utility Pack.

What is BizTalk Pipeline Components Extensions Utility Pack?

The BizTalk Pipeline Components Extensions Utility Pack is a set of custom pipeline components (libraries) with several custom pipeline components that can be used in receive and send pipelines, which will provide an extension of BizTalk out-of-the-box pipeline capabilities.

The project is available on the BizTalk Server Open Source Community repository on GitHub (https://github.com/BizTalkCommunity). Everybody can contribute with new pipeline components that can be used to extend or improve the existing BizTalk Server capabilities.

At the moment, it is only available for BizTalk Server 2016, but it will soon be compiled and available for previous versions of the product.

Where to download it?

You can download BizTalk Pipeline Components Extensions Utility Pack from GitHub here: BizTalk Pipeline Components Extensions Utility Pack.

The post BizTalk Pipeline Components Extensions Utility Pack: SQL Server Polling Debatch Message By Grouping Filter Pipeline Component appeared first on BizTalk360.