Gobbledygooks by Richard Hallgren

I’ve doing a lot of EDI related work in BizTalk lately and I have to say that I’ve really enjoyed it! EDI takes a while to get used to (see example below), but once one started to understand it I’ve found it to be a real nice, strict standard – with some cool features built into BizTalk!

UNB+IATB:1+6XPPC+LHPPC+940101:0950+1'
UNH+1+PAORES:93:1:IA'
MSG+1:45'
IFT+3+XYZCOMPANY AVAILABILITY'
ERC+A7V:1:AMD'
IFT+3+NO MORE FLIGHTS'
ODI'
TVL+240493:1000::1220+FRA+JFK+DL+400+C'
...

There are however some things that doesn’t work as expected …

Promoting values

According to the MSDN documentation the EDI Disassembler by default promotes the following EDI fields: UNB2.1, UNB2.3, UNB3.1, UNB11; UNG1, UNG2.1, UNG3.1; UNH2.1, UNH2.2, UNH2.3.

There are however situation where one would like other values promoted.

I my case I wanted the C002/1001 value in the BGM segment. This is a value identifying the purpose of the document and I needed to route the incoming message based on the value.

The short version is that creating a property schema, promoting the field in the schema and having the EDI Disassembler promoting the value will not work (as with the XML Disassembler). To do this you’ll need to use a custom pipeline component to promote the value. Rikard Alard seem to have come to the same conclusion here.

Promote pipeline component to use

If you don’t want to spend time on writing your own pipeline component to do this yourself you can find a nice “promote component” on CodePlex here by Jan Eliasen.

If you however expect to receive lots and lots of big messages you might want to look into changing the component to use XPathReader and custom stream implementations in the Microsoft.BizTalk.Streaming.dll. You can find more detailed information on how to do that in this MSDN article.

Update 2010-04-13
Grant Samuels commented and made me aware of the fact that inline scripts might in some cases cause memory leaks. He has some further information here and you’ll find a kb-article here. 

I’ve posted a few times before on how powerful I think it is in complex mapping to be able to replace the BizTalk Mapper with a custom XSLT script (here’s how to). The BizTalk Mapper is nice and productive in simpler scenarios but in my experience it break down in more complex ones and maintaining a good overview is hard. I’m however looking forward to the new version of the tool in BizTalk 2010 – but until then I’m using custom XSLT when things gets complicated.

Custom XSLT however lacks a few things once has gotten used to have – such as scripting blocks, clever functoids etc. In some previously post (here and here) I’ve talked about using EXSLT as a way to extend the capabilities of custom XSLT when used in BizTalk.

Bye, bye external libraries – heeeello inline scripts

Another way to achieve much of the same functionality even easier is to use embedded scripting that’s supported by the XslTransform class. Using a script block in XSLT is easy and is also the way the BizTalk Mapper makes it possible to include C# snippets right into your maps.

Have a look at the following XSLT sample:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet
    version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:code="http://richardhallgren.com/Sample/XsltCode"
    exclude-result-prefixes="msxsl code"
    >
    <xsl:output method="xml" indent="yes"/>

    <xsl:template match="@* | node()">
        <Test>
            <UniqueNumber>
                <xsl:value-of select="code:GetUniqueId()" />
            </UniqueNumber>
            <SpecialDateFormat>
                <xsl:value-of select="code:GetInternationalDateFormat('11/16/2003')" />
            </SpecialDateFormat>
            <IncludesBizTalk>
                <xsl:value-of select="code:IncludesSpecialWord('This is a text with BizTalk in it', 'BizTalk')" />
            </IncludesBizTalk>
        </Test>
    </xsl:template>

    <msxsl:script language="CSharp" implements-prefix="code">
        //Gets a unique id based on a guid
        public string GetUniqueId()
        {
            return Guid.NewGuid().ToString();
        }

        //Formats US based dates to standard international
        public string GetInternationalDateFormat(String date)
        {
            return DateTime.Parse(date, new System.Globalization.CultureInfo("en-US")).ToString("yyyy-MM-dd");
        }

        //Use regular expression to look for a pattern in a string
        public bool IncludesSpecialWord(String s, String pattern)
        {
            Regex rx = new Regex(pattern);
            return rx.Match(s).Success;
        }
    </msxsl:script>
</xsl:stylesheet>

All one has to do is to define a code block, reference the xml-namespace used and start coding! Say goodbye to all those external library dlls!

It’s possible to use a few core namespaces without the full .NET namespace path but all namespaces are available as long as they are fully qualified. MSDN has a great page with all the details here.

Update 2010-04-12
Seems like there is a patch coming that should fix all the bugs in SP1 … I’ve been told it should be public within a week or two. I’ll make sure to update the post as I know more. Our problem is still unsolved.

Late Thursday night last week we decided to upgrade one of our largest BizTalk 2006 R2 environment to recently released Service Pack 1. The installation went fine and everything looked good.

… But after a while we started see loads of error messages looking like below.

Unable to cast COM object of type ‘System.__ComObject’ to interface type ‘Microsoft.BizTalk.PipelineOM.IInterceptor’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{24394515-91A3-4CF7-96A6-0891C6FB1360}’ failed due to the following error: Interface not registered (Exception from HRESULT: 0×80040155).

After lots of investigation we found out that we got the errors on ports with the follow criteria:

• Send port
• Uses the SQL Server adapter
• Has a mapping on the port
• Has a BAM tracking profile associated with the port
  In our environment the tracking on the port is on “SendDateTime” from the “Messaging Property Schema”. We haven’t looked further into if just any BAM tracking associated with port causes the error or if only has to do with some specific properties.

Reproduce it to prove it!

I’ve setup a really simple sample solution to reproduce the problem. Download it here.

The sample receives a XML file, maps it on the send port to schema made to match the store procedure. It also uses a dead simple tracking definition and profile to track a milestone on the send port.

Sample solution installation instructions

1. Create a database called “Test”

2. Run the two SQL scripts (“TBL_CreateIds.sql” and “SP_CreateAddID.sql”) in the solution to create the necessary table and store procedure

3. Deploy the BizTalk solution just using simple deploy from Visual Studio

4. Apply the binding file (“Binding.xml”) found in the solution

5. Run the BM.exe tool to deploy the BAM tracking defintion.
   Should look something like:

   bm.exe deploy-all -definitionfile:<the path to the solution>\BAM\SimpleTestTrackingDefinition.xml

6. Start the tracking Profile editor and open the “SimpleTestTrackingProfile.btt“ that you’ll find in the solution and apply the profile

7. Drop the test file in the receive folder (“InSchema_output.xml”)

The sample solution fails on a environment with SP 1 but works just fine on a “clean” BizTalk 2006 R2 environment.

What about you?

I haven’t had time to test this on a BizTalk 2009 environment but I’ll update the post as soon as I get around to it.

We also currently have a support case with Microsoft on this and I’ll make sure to let you as soon as something comes out of that. But until then I’d be really grateful to hear from you if any of you have the same behavior in your BizTalk 2006 R2 SP1 environment.

Recently there’s been a few really good resources on streaming pipeline handling published. You can find some of the here and here.

The Optimizing Pipeline Performance MSDN article has two great examples of how to use some of the Microsoft.BizTalk.Streaming.dl classes. The execute method of first example looks something like below.

public IBaseMessage Execute(IPipelineContext context, IBaseMessage message)
{
    try
    {
        ...
        IBaseMessageContext messageContext = message.Context;
        if (string.IsNullOrEmpty(xPath) && string.IsNullOrEmpty(propertyValue))
        {
            throw new ArgumentException(...);
        }
        IBaseMessagePart bodyPart = message.BodyPart;
        Stream inboundStream = bodyPart.GetOriginalDataStream();
        VirtualStream virtualStream = new VirtualStream(bufferSize, thresholdSize);
        ReadOnlySeekableStream readOnlySeekableStream = new ReadOnlySeekableStream(inboundStream, virtualStream, bufferSize);
        XmlTextReader xmlTextReader = new XmlTextReader(readOnlySeekableStream);
        XPathCollection xPathCollection = new XPathCollection();
        XPathReader xPathReader = new XPathReader(xmlTextReader, xPathCollection);
        xPathCollection.Add(xPath);
        bool ok = false;
        while (xPathReader.ReadUntilMatch())
        {
            if (xPathReader.Match(0) && !ok)
            {
                propertyValue = xPathReader.ReadString();
                messageContext.Promote(propertyName, propertyNamespace, propertyValue);
                ok = true;
            }
        }
        readOnlySeekableStream.Position = 0;
        bodyPart.Data = readOnlySeekableStream;
    }
    catch (Exception ex)
    {
        if (message != null)
        {
            message.SetErrorInfo(ex);
        }
        ...
        throw ex;
    }
    return message;
}

We used this example as a base when developing something very similar in a recent project. At first every thing worked fine but after a while we stared getting an error saying:

Cannot access a disposed object. Object name: DataReader

It took us a while to figure out the real problem here, everything worked fine when sending in simple messages but as soon as we used to code in a pipeline were we also debatched messages we got the “disposed object” problem.

It turns out that when we debatched messages the execute method of the custom pipeline ran multiple times, one time for each sub-messages. This forced the .NET Garbage Collector to run.

The GC found the XmlTextReader that we used to read the stream as unreferenced and decided to destoy it.

The problem is that will also dispose the readOnlySeekable-Stream stream that we connected to our message data object!

It’s then the BizTalk End Point Manager (EPM) that throws the error as it hits a disposed stream object when trying to read the message body and save it to the BizTalkMsgBox!

ResourceTracker to the rescue!

Turns out that the BizTalk message context object has a nice little class connected to it called the ResourceTracker. This object has a “AddResouce”-method that makes it possible to add an object and the context will the hold a reference to this object, this will tell the GC not to dispose it!

So when adding the below before ending the method everything works fine – even when debatching messages!

context.ResourceTracker.AddResource(xmlTextReader);

As all of you know the number one time consuming task in BizTalk is deployment. How many times have you worked your way through the steps below (and even more interesting – how much time have you spent on them …)

• Build
• Create application
• Deploy schemas
• Deploy transformations
• Deploy orchestration
• Deploy components
• Deploy pipelines
• Deploy web services
• Create the external databases
• Change config settings
• GAC libraries
• Apply bindings on applications
• Bounce the host instances
• Send test messages
• Etc, etc …

Not only is this time consuming it’s also drop dead boring and therefore also very prone – small mistakes that takes ages to find and fix.

The good news is however that the steps are quite easy to script. We use a combination of a couple of different open-source MsBuild libraries (like this and this) and have created our own little build framework. There is however the BizTalk Deployment Framework by Scott Colescott and Thomas F. Abraham that looks great and is very similar to what we have (ok, ok, it’s a bit more polished …).

Binding files problem

Keeping the binding files in a source control system is of course a super-important part of the whole build concept. If something goes wrong and you need to roll back, or even rebuild the whole solution, having the right version of the binding file is critical.

A problem is however that if someone has done changes to the configuration via the administration console and missed to export these binding to source control we’ll deploy an old version of the binding when redeploying. This can be a huge problem when for example addresses etc have changed on ports and we redeploy old configurations.

“- What!? If fixed that configuration issue in production last week and now it back …”

So how can we reassure that the binding file is up-to-date when deploying?

One solution is to do and export of the current binding file and compare that to one we’re about to deploy in a “pre-deploy”-step using a custom MsBuild target.

Custom build task

Custom build task in MsBuild are easy, a good explanation of how to write one can be found here. The custom task below does the following.

1. Require a path to the binding file being deployed.
2. Require a path to the old deployed binding file to compare against.
3. Using a regular expression to strip out the time stamp in the files as this is the time the file was exported and that will otherwise differ between the files.
4. Compare the content of the files and return a boolean saying if they are equal or not.

public class CompareBindingFiles : Task
    {
        string _bindingFileToInstallPath;
        string _bindingFileDeployedPath;
        bool _value = false; 

        [Required]
        public string BindingFileToInstallPath
        {
            get { return _bindingFileToInstallPath; }
            set { _bindingFileToInstallPath = value; }
        } 

        [Required]
        public string BindingFileDeployedPath
        {
            get { return _bindingFileDeployedPath; }
            set { _bindingFileDeployedPath = value; }
        } 

        [Output]
        public bool Value
        {
            get { return _value; }
            set { _value = value; } 

        } 

        public override bool Execute()
        {
            _value = GetStrippedXmlContent(_bindingFileDeployedPath).Equals(GetStrippedXmlContent(_bindingFileToInstallPath));
            return true; //successful
        } 

        private string GetStrippedXmlContent(string path)
        {
            StreamReader reader = new StreamReader(path);
            string content = reader.ReadToEnd();
            Regex pattern = new Regex("<Timestamp>.*</Timestamp>");
            return pattern.Replace(content, string.Empty);
        } 

    }

Using the build task in MsBuild

After compiling the task above when have to reference the dll in <UsingTask> element like below.

<UsingTask AssemblyFile="My.Shared.MSBuildTasks.dll" TaskName="My.Shared.MSBuildTasks.CompareBindingFiles"/>

We can then do the following in out build script!

<!--
    This target will export the current binding file, save as a temporary biding file and use a custom target to compare the exported file against the one we’re about to deploy.
    A boolean value will be returned as IsValidBindingFile telling us if they are equal of not.
-->
<Target Name="IsValidBindingFile" Condition="$(ApplicationExists)=='True'">
    <Message Text="Comparing binding file to the one deployed"/> 

    <Exec Command='BTSTask ExportBindings /ApplicationName:$(ApplicationName) "/Destination:Temp_$(BindingFile)"'/> 

    <CompareBindingFiles BindingFileToInstallPath="$(BindingFile)"
                         BindingFileDeployedPath="Temp_$(BindingFile)">
        <Output TaskParameter="Value" PropertyName="IsValidBindingFile" />
    </CompareBindingFiles> 

    <Message Text="Binding files is equal: $(IsValidBindingFile)" /> 

</Target>

<!--
    This pre-build step runs only if the application exists from before. If so it will check if the binding file we try to deploy is equal to one deployed. If not this step will break the build.
-->
<Target Name="PreBuild" Condition="$(ApplicationExists)=='True'" DependsOnTargets="ApplicationExists;IsValidBindingFile">
    <!--We'll break the build if the deployed binding files doesn't match the one being deployed-->
    <Error Condition="$(IsValidBindingFile) == 'False'" Text="Binding files is not equal to deployed" /> 

<!--All other pre-build steps goes here-->

</Target>

So we now break the build if the binding file being deployed aren’t up-to-date!

This is far from rocket science but can potentially save you from making some stupid mistakes.

I’ll start this post by clarifying two important things

1. I am involved in the development and marketing of “BizMon”. Therefore I am biased and you have to decide for yourself if that affects the content of the post. As always it is best to try it for yourself and see if it is useful for you.
2. I have talked about BizTalk monitoring tools in a previous post and my goal then was then to start a an open source project. That did not happened and you can read why in the update to that post.

Why “monitoring” for BizTalk?

I have worked as a BizTalk developer for many years but it was not until I really got in to maintaining a large integration solution that I realized that the tools I really needed was not there. I found myself using the following “tools” and techniques over and over again.

1. Open the BizTalk Administration Console and query for suspended messages, running instances, routing errors etc, etc.

   But as I had to pull for this information it took time and discipline (two things I’m short of) to quickly find out when errors occurred.

2. I used the HAT to try and find out when the last messages was sent and received on the different applications. This gave me a “guarantee” that things worked as I accepted and that the solution had a “pulse” – messages at least moved back and forward. 

   The problem is that the HAT tool is bad and it is hard to find what one is looking for (It is a bit better in BizTalk 2009 but it is still tricky to get useful information out of it.)

3. Some of the integrations in our environment used BAM to track messages and their state.

   The problem was that all solutions was developed by either myself or different consultants. This made it hard to get everyone to use the same tracking. It was also hard to convince management to go back and try and “instrument” old working integrations with BAM tracking.

At the same time as we had the “tools” and techniques mentioned above available, management had the following requirements for us.

1. Start working on fixing an error within 10 minutes after it occurred 24/7 all 365 days …
2. Be able to delegate simple monitoring task to support personnel (a help desk).
3. Not have to actively “pull” for information but be quickly altered of errors and get the information “pushed” to us.

   The idea was that this would would save time as people don’t have to look for errors when everything is working fine. Time that people can use for other tasks …

4. Enable reporting so we can provide systems owners and other interested people with information on how much data has been sent received to the systems and parties they care about.

All the above lead up to the realization that we needed some sort of tool.

What are the existing options for BizTalk monitoring tooling?

At the time we started looking for options all we could find was System Center Operations Manager (SCOM). We looked at SCOM BizTalk Management Pack and decided that for us this was not the right solution. It was too big, too complicated and it would be to hard to get it to the what we wanted to do.

The decision to not use SCOM I think was right for us. We wanted something leaner and more specialized. I am however not saying that it is the right decision for you.

If you are successfully suing SCOM to monitor BizTalk I would love to hear about it!

What we ended up with

We ended up building BizMon. It does what we need and our help desk can now basically monitor about 100 different BizTalk application themselves. At the same time they do all the other support task they have to do. When something happens (and it does …) they are the first to know. Some easy tasks they can solve themselves, otherwise they make sure to notify the users and quickly call the developer that knows more and can help them.

Support personnel can now also setup custom reports that users can subscribe to, all based on BAM that they now easily can interject tracking points in existing solutions – both new and old ones.

As I said. This worked out out good and helped us. If you think that it could work for you as well – give it a try.

I am also really interested to how you have solved similar requirements as we had with your own tool or other solutions.

What else is there?

Recently FRENDS released a beta version of their FRENDS Helium product that looks promising could potentially solve a lot of the same issues that BizMon does and that I have discussed in this post.

Check it out and let us know what you think.

Traditional SOAP Web Services might feel kind of old as more and more people move over to WCF. But a lot of integration projects still relay heavily on old fashion SOAP Web Services.

Using BizTalk generated Web Services however has a few issues and one needs to add a few extra steps and procedures to make them effective and easy to work with. This post aims to collect, link and discuss all those issues and solutions.

1. Building and deploying

BizTalk Server includes the “BizTalk Web Services Publishing Wizard” tool that integrates with Visual Studio. This is basically a tool to generate a DSL based script for generating web services.

The wizard collects information about what schema or a orchestration to expose, namespaces, names of service and method, where on IIS to publish the service etc, etc.

The output of the tool is then a xml file (a “WebServiceDescription” file) that has all the collected values in it.

As a final step Visual Studio uses the newly created description file as input to a class called WebServiceBuilder in the .NET Framework. It is this class that is responsible for interpreting the description, configure and generate the service.

A common procedure is to use the wizard and click thru it and input values for every single deployment. This is of course slow, error prone and stupid.

What is much better is to take a copy of the generated “WebServiceDescription” file, save it to your source control system and then programmatically pass the file to the WebServiceBuilder class as part of your deployment process. Possible changes to the details of the service can then be done directly in the description file.

I have seen this approach save lots of time and problems related to deployment.

• Paul Petrov has a great post on how to call the “WebServiceBuilder” class and pass the description file using C#.
  http://geekswithblogs.net/paulp/archive/2006/05/22/79282.aspx
• Michael Stephenson has a good post on how to used the “WebServiceBuilder” class via MSBuild.
  http://geekswithblogs.net/michaelstephenson/archive/2006/09/16/91369.aspx

2. Fixing namespace

Another annoying problem (I’d would actually go so far as calling it a bug) is the problem with the bodyTypeAssemblyQualifiedName value in the generated Web Service class.

This causes BizTalk to skip looking up the actual message type for the incoming message.  As no message type exists for the message is in BizTalk mapping and routing on message types etc will fail.  It is a know problem and there are solutions to it. I would also recommend take the extra time need to make this small “post process step” be part of your deployment process (see how here).

• Saravana Kumar discusses the problem and solutions in this great post.
  http://www.digitaldeposit.net/saravana/post/2007/08/17/SOAP-Adapter-and-BizTalk-Web-Publishing-Wizard-things-you-need-to-know.aspx
• Richard Seroter has a short and well written post published.
  http://blogs.msdn.com/richardbpi/archive/2006/09/15/Fixing-_2200_SOAP-_2F00_-Envelope-Schema_2200_-Error-In-BizTalk.aspx
• I have a post on how to solve the issue using MSBuild.
  http://www.richardhallgren.com/handle-the-bodytypeassemblyqualifiedname-soap-adapter-bug-in-msbuild-as-a-regex-ninja/

3. Pre-compiling

By default the “WebServiceBuilder” class generates a web service without pre-compiling it. Usually this is not a problem. But in some cases were one really on the web service being online and give a quick response-message the performance problems in this approach can be a huge problem.

When generating the web service without pre-compiling it IIS has to compile the service and then keep the compiled service in memory. That means that when IIS releases the service from memory there is a latency before IIS re-compiled the service, loaded it into memory and executed it. This is a known problem and I have seen this “slow first hit” issue been a frequent question the different forums.

The solution is to use the aspnet_compiler.exe tool and pre-compile the service and the use those pre-compiled dlls as the service. IIS then never has to recompile it and will serve initial hits much faster.

Here is an example of how we defined a target to do this as part of our deployment process  using MSBuild.

1. Pre-compile the service into a new folder
2. Clean out the “old” not compile service folder.
3. Copy the pre-complied service into the service folder

<Target Name="CompileWeb">
    <Message Text="Uses aspnet compiler to compile the service into a new folder. Then copies the compiled content back into its original place" />
    <AspNetCompiler
        PhysicalPath="$(WebSiteServicePath)InitiateProjectService\"
        VirtualPath="/WebServiceName"
        TargetPath="$(WebSiteServicePath)$(WebServiceName)Compiled\"
        Force="true" />

    <Folder.CleanFolder Path="$(WebSiteServicePath)$(WebServiceName)\"/>

    <Folder.CopyFolder
                Source="$(WebSiteServicePath)$(WebServiceName)Compiled\"
                Destination="$(WebSiteServicePath)$(WebServiceName)\" />

</Target>

• Paul Petrov has two different articles here describing the process and also a different way that above on how to include the pre-compilation in you build process.
  
  http://geekswithblogs.net/paulp/archive/2006/03/30/73900.aspx, http://geekswithblogs.net/paulp/archive/2006/04/19/75633.aspx

There are integrations which only purpose is to move a file just as it is. No advanced routing. No orchestration processing. No transformation. Just a simple port-to-port messaging scenario.

It is however still a good idea to monitor these just as one would monitor a more complicated integration. We use BAM to monitor all our integrations and to measure how many messages that has been processed in a integration. Using BAM monitoring in a simple solution as the above however has its issues …

Setting up a simple test solution

1. The solution will move a XML file between two port called “SimpleTrackingReceivePort” and “SimpleTrackingSendPort”.
2. Both port have PassThru pipelines configured.
3. The XML file does not have a installed schema. Remember we are just moving the file not actually doing anything with it.
4. A BAM tracking definition with one milestone called “StartPort” will be used. This will be mapped to the “PortStartTime” property on both the receiving and sending port . 

Our tracking profile configuration will like below. Dead simple.

So – what’s the problem?

Let us drop a XML message looking some like this.

<?xml version="1.0" encoding="UTF-16"?>
<SimpleTest>
    <SimpleContent Text="This is a test" />
</SimpleTest>

Remember that there is not a schema installed so we do not really have to worry about the structure of the file. It should just be “a file” to BizTalk and everything should be transferred between the ports. Even if we drop a executable or whatever – it should just be transferred. Nothing should read or examine the file as it’s just a pass thru!

As soon as BAM tracking is configured on a port that is however not the case. Lets take a look at the file we receive on the other end of our integration.

<SimpleTest>
    <SimpleContent Text="This is a test" />
</SimpleTest>

BizTalk now removed our XML declaration! Basically it treated the message as a XML message and parsed the message as such while tracking it. It’ will also add the dreaded Byte-Order-Mark and fail any non-valid XML messages. The problem is that this is not the behavior what one expects and causes receiving systems that rely on the XML declaration to fail!

As we also don’t have a installed schema it is not possible to use a XMLTransmit pipeline to add the XML declaration and remove the BOM.

What to do?

If you’d like to track a XML based message using BAM make sure you have the schema installed … Even if you are just using PassThru.

Is it a bug or just something one should expect? In my opinion it is at least very annoying!

I attended a session the other day at TechDays here in Sweden with Microsoft Escalation Engineer Niklas Engfelt. The session was about troubleshooting BizTalk and Niklas of course showed the wonderful MsgBoxViewer (MBV) tool by Jean-Pierre Auconie. If you haven’t tested and looked deeper into this tool you need to do so. It’s great!

I worked with the tool before but now I wanted to schedule the tool and to have MBV-reports e-mailed to relevant persons within the company on a weekly basis. This is quite easy to accomplish as MBV comes in two version. One GUI-based (shown below) version and one command-line based.

The command-line version is of course perfect for scheduling using the Windows Task Scheduler.

If you feel uncomfortable running all the queries (there is a lot of them) on a schedule you can pick some you find important and configure the tool to only run those. Jean-Pierre has a post on how to do just that here.

After MBV has completed all its queries and done its magic it will produce a html-report in the working folder (that’s the folder in the “Start in” field in the scheduled task example above).

We then use a tool called AutoMailer NT (cost €20 – there is a 30 days trial) to:

1. Poll the working folder for a *.html report file.
2. Compress the file (using zip).
3. Send the report to a configured list of recipients.
4. Delete the report file.

The AutoMailer NT installation is a bit rough (don’t miss to the separate download (!) of the trial certificate). But once you have everything working it’s great to have a fresh MBV report in you inbox every Monday telling you how your BizTalk environment is doing and possible issues to attend to.

During my years of BizTalk development I’ve been warned of a couple of scenarios that the product wouldn’t handle very well. Yesterday another of those scenarios turned out to kind of false and, if done right, not really a problem at all.

The scenario I’m talking about is a batch import of data to SQL Server using the SQL adapter. In my case the data is received as a flat text file containing a large number of rows. These rows should the be places inside a database table as one table-row per row in the flat file.

The common way of dealing with batch incoming data like this is to split (aka disassemble) it in the receive port using the Flat File Disassembler pipeline component (for a good example – look here). Disassembling the data when receiving it is usually good practice to avoid running into OutOfMemoryException when dealing with big messages.

Sometimes the requirements also forces one into reading each row to a separate message to be able to route and handle each messages in a unique way depending of it’s content. If that so – this is a not a good post for you. In this post I’ll discuss the scenario were all the data just needs to go as fast as possible from the text file into a single database table. No orchestration or anything, just a simple batch import.

So, what’s The problem with the batch import scenario?

When I implemented similar batch import scenarios in the past I tried to practice good practice and split the data into separate files that I then filtered to the SQL adapter send port, one by one.

1. The received flat file files has been split into thousands of small little message that one by one are sent to the SQL adapter send port.
2. The SQL adapter then parses each message into a SQL script that executes a store procedure and the message is finally inserted to the target database.

 “So what’s the problem?” you then ask? It’s slow! It’s very slow! Each message gets stored a couple of times in the BizTalk database and each message is sent inside it’s own DTC transaction against the target database. And all this adds up …

And after reading this this interview by Richard Seroter with Alan Smith I also felt I was the only one having the problem either …

There are quite a few people asking about using BizTalk for heavy database integration, taking flat files, inserting the data in databases and processing it. SQL Server Integration Services (SSIS) does a much better job than BizTalk at this, and is worth looking at in those scenarios. BizTalk still has its uses for that type of work, but is limited be performance. The worst case I saw was a client who had a daily batch that took 36 hours to process using BizTalk, and about 15 minutes using SSIS. On another project I worked on they had used BizTalk for all the message based integration, and SSIS for the data batching, and it worked really well.

Note: As I’ll described later in this post my import scenario went from something like 3-4 hours to 2 minutes (importing 10 MB). Alan talks about a 36 hours (!) import. I don’t know anything more about the scenario he mentions and it might not even be solved using the technique discussed below. Hopefully Alan might comment on the post and give us more details.

How can we get better performing imports using BizTalk?

As the import scenario we described doesn’t involve any orchestration but is a pure messaging scenario and we do all the transformation on the ports we don’t really have to worry about OutOfMemoeyExceptions even though the message is quite big.

Large message transformation. In previous versions of BizTalk Server, mapping of documents always occurred in-memory. While in-memory mapping provides the best performance, it can quickly consume resources when large documents are mapped. In BizTalk Server 2006, large messages will be mapped by the new large message transformation engine, which buffers message data to the file system, keeping the memory consumption flat. (Source)

Another reason for splitting the message was for it to work with the SQL adapter. When setting up the SQL adapter to work with a store procedure the adapter expects a message that looks something like the below.

<ns0:ImportDataSP_Request xmlns:ns0="http://FastSqlServerBatchImport.Schemas.SQL_ImportDataSP">
    <ImportData Name="Name 1" Value="1"></ImportData>
</ns0:ImportDataSP_Request>

This tells us that the store procedure called is “ImportData” with “Name 1″ as the value for the “Name” parameter and “1″ as the value for the parameter called “Value” in the stored procedure. So each little separate message would get mapped on the send port into something like this.

What I however didn’t know until I read this post was that the message I send to the SQL adapter port just as well could look like this!

<ns0:ImportDataSP_Request xmlns:ns0="http://FastSqlServerBatchImport.Schemas.SQL_ImportDataSP">
    <!-- TWO rows!!! -->
    <ImportData Name="Name 1" Value="1"></ImportData>
    <ImportData Name="Name 2" Value="2"></ImportData>
</ns0:ImportDataSP_Request>

So basically we can have as many store procedure calls as we want in one single file that then can send to the SQL adapter send port!

Eureka! This means that we don’t have to split the incoming file! We can keep it as one big single file and just transform it to a huge file containing separate nodes that we send to the SQL Adapter send port! The SQL adapter will then parse this into separate store procedure calls for us.

Is it really any faster?

As the technique above drastically reduced the amount of database communication needed I knew it’d be much faster. Some initial testing shows that an import of a file containing somewhere around 55 000 rows (about 10 MB) into our article database went from 3-4 hours to under two minutes!

See for yourself!

In this sample solution I have a text file containing 2 600 rows. I’ve then created two separate projects in a solutions. One that splits the messages into separate messages (called “SlowImport”) and one that just transforms it and send it as one message to the send port (called “FastImport”). One takes 1:50 minutes and 2 seconds on my development machine … I won’t tell you which one is the faster one …

Test it for yourself and let me know what you think.