BizTalk Server: Using Global Variable in Mapping

Article
1/17/2024

Introduction

Mapping is one of the most important artifacts in BizTalk Server. It allows us to perform, in a simple and visual manner, transformations between XML messages.

When you create a map,as a BizTalk developer, you should make sure that you build it with a good and optimal solution.

Mapping sometimes could be one of the bottlenecks of your BizTalk solution.

In this article, we will explain the benefits of using global variable in one of the real-life scenarios.

Problem

As a BizTalk developer, you found out a problem in the current solution that when you upload a file with 10,000 records, it takes more that 15 minutes to process this file.

After investigation, you observed that the bottleneck is in mapping artifact. The map is shown in figure 1.

Figure 1. Bad solution map

In this map, it is calling the external assembly to pick up a value from the database.

In the first look, you may think that it is calling the external assembly for 1 time but it will call it for many times.

If you take a look to XSL file, you will find out that the calling external assembly is inside the for-each loop as shown in figure 2.

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var s0 ScriptNS0" version="1.0" xmlns:ns0="http://Biztalk.Mapping.GlobalVariable.Destination" xmlns:s0="http://Biztalk.Mapping.GlobalVariable.Source" xmlns:ScriptNS0="http://schemas.microsoft.com/BizTalk/2003/ScriptNS0">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/s0:Source" />
  </xsl:template>
  <xsl:template match="/s0:Source">
    <ns0:Destiation>
      <xsl:for-each select="SourceRepeatedRecords">
        <DestinationRepeatedRecords>
          <xsl:variable name="var:v1" select="ScriptNS0:GetLookupValue(string(../Key/text()))" />
          <Value>
            <xsl:value-of select="$var:v1" />
          </Value>
          <FieldA>
            <xsl:value-of select="FieldA/text()" />
          </FieldA>
          <FieldB>
            <xsl:value-of select="FieldB/text()" />
          </FieldB>
        </DestinationRepeatedRecords>
      </xsl:for-each>
    </ns0:Destiation>
  </xsl:template>
</xsl:stylesheet>

Figure 2. XSL file of bad solution map

If we run debug view to check how many times it call external assembly for the source that contains 3 records.

We will find out it will call it 3 times as shown in figure 3.

Figure 3. Debug View Event logs output for bad solution

Now it is clear where is the problem. If we have 10,000 records in source schema , then it will hit a database table for 10,000 times.

Solution

We need to call the external assembly to pick up value from a lookup table for 1 time instead of 10,000 times.

There are many solutions. One of these solutions is using a global variable. We can use a global variable to save a value within the first iteration and read the value from the global variable within the remain iterations as shown in figure 4.

Figure 4. Good Solution Map

In figure 4, The functoid number 1 is the value mapping of the external assembly when iteration index is equal 1

In ****functoid number 7, we save value in a global variable and return it to value as shown in figure 5

Figure 5. Setting DB value in global variable

In functoid number 8, we get the value of return the value of global variable which is in ****functoid number 6 when iteration is not equal 1.
****Functoid number 6 code is shown in figure 6
** **

Figure 6. Get the value from a global variable

If we test our solution, we will find out the external assembly was called only for 1 time instead of 3 times as shown in **figure 7 **

**Figure 7. **Debug View Event logs output for bad solution

So, without using a global variable if we have 10,000 records in the source schema it will hit the database table for 10,000 times. On the other hand, when we use the global variable, it will hit the database table for 1 time.

Sample Code

You can find the source code belonging to this article on MSDN Code Gallery: BizTalk Server: Using Global Variable in Mapping

Conclusion

In this article we illustrated how to use the global variable in BizTalk mapper and explained how global variable can improve the performance of a BizTalk solution.

Share via