Effective BizTalk

I'm still pretty amazed how often I see BizTalk developers and teams not use automated testing when building (or more importantly maintaining) their solutions. It's tragic. You take a wonderful platform like BizTalk and then let mission critical systems depend on someone's manual testing for development or enhancements. It is a sure fire recipe for trouble. Amazingly I see the more complex and critical systems relying less and less on automated testing. I think part of that is due to the difficulty in automating some testing activities. AS2 is a great example.

Today I want to present a dozen lines of code that take a handy little app from the SDK and turn it into the driver for your AS2 Unit Testing automation. The Sender sample (Microsoft BizTalk Server 2009\SDK\AS2 Tutorial\Sender) is a simple command line application that sends an AS2 request via an HTTP POST to the local host. The heavy lifting is in creating the HTTP Headers necessary for a valid AS2 request.

The first two statements in the main method in the only class file sender.cs in this project are listed here:

HttpSender TestSender = new HttpSender(http://localhost/Contoso/BTSHttpReceive.dll);

Stream sr = new FileStream(getBizTalkInstallPath() + @"SDK\AS2 Tutorial\X12_00401_864-Sync.edi", FileMode.Open, FileAccess.Read);

As you can see there is nothing terribly special here (other than the clever getBizTalkInstallPath method). Everything is pretty much hard coded, but it doesn't need to be. These few lines take this from an SDK curiosity into a vital testing tool. Simply replace the two lines above with these:

string url = "";

string payload = "";

    

foreach (string arg in args)

{

if(arg.StartsWith("-url="))

{

url = arg.Replace("-url=", "");

}

if (arg.StartsWith("-file="))

{

payload = arg.Replace("-file=", "");

}

}

if (url == "" || payload == "")

{

Console.WriteLine("Usage: sender -url=http://localhost/BTSHTTPReceive.dll -file=input.txt");

}

HttpSender TestSender = new HttpSender(url);

Stream sr = new FileStream(payload, FileMode.Open, FileAccess.Read);

Now we can test sending an AS2 request with one simple command: sender -url=http://localhost:10101/BTSHTTPReceive.dll -file=RawAS2File.txt

Better still this is dynamic on both the endpoint and the file, so you can test many samples.

Now we're ready to make this even more useful. We turn to our trusty friend BizUnit (which I am so into I'm actually considering a tattoo). BizUnit includes most of the test steps you will ever need, but not one like this. Instead of making a custom BizUnit test step (which honestly couldn't be much easier) we can fall back on the low tech approach and use the existing ExecuteCommandStep. This step is pretty self-explanatory, like most of BizUnit.

<TestStep assemblyPath="" typeName="BizUnit.ExecuteCommandStep">

    <ProcessName>..\..\sender.exe</ProcessName>

    <ProcessParams>-url=http://localhost:10101/BTSHTTPReceive.dll -file=TestData\External\RawAS2File.txt</ProcessParams>

    <WorkingDirectory>..\..</WorkingDirectory>

</TestStep>

This is admittedly low tech. A BizUnit step would avoid the overhead of creating a new process and certainly scale better. The point I am driving to here is that not everything always needs to be so fancy when it comes to automated testing. Sure there are more elegant ways to do these things, but this is Unit Testing not production code. Perhaps most importantly in less time than it took you to read this I had it working and even made several unit tests for it.

And there it is, a few lines of code added to an existing (and importantly widely distributed) piece of code and suddenly we have automated testing for our AS2 solutions.

I've spent a lot of time lately designing stateless services and the schemas to enable them to be both stateless and extensible (which will be another post), but in doing so I began to realize that very often people embrace BizTalk Orchestration as the closest analogy to Business Process and tend to over leverage it in an attempt to unify and track their business processes. There certainly are times when an Orchestration is a business process, but often business processes will span multiple Orchestrations. Fortunately BAM and the TPE allow for this.

So what's wrong with just making really large, long running Orchestrations anyway? I strongly believe that the less in flight instances of Orchestrations you have in your BizTalk servers the better. It's not that the product can't do it (scalability isn't really a big issue here when done right) or that it's that difficult to make the product do it well; side by side deployment of Orchestrations is simple in BizTalk and schema and artifact versioning strategies (yet another post) are widely established / embraced. It is really back to the idea of the business process as a whole and its constituent parts as stages. Again I am reminded of good programming practices: in general simple is good. If a method has too many lines of code it is difficult to understand and maintain, breaking it into smaller chunks is probably a good idea once this happens. This also promotes reuse.

The same is true of Orchestration, only more so because the administrative aspects of Orchestration are more pronounced and visible. The Orchestration designer is a great tool and the HAT view of Orchestration is very useful for both tracking and debugging, but it is easy to get carried away. A good all around architectural principal is that you should not have to change your solution architecture simply to support features that aren't a core part of the solution itself. Build processes and tracking often fall into this category. If your build process causes you to change your solution perhaps it is not your solution that needs changing, but your build process. The same is true of tracking.

This ultimately calls for Business Activity Monitoring, which I having been working with more and more lately. I think BAM really is the analogy that best represents the business process that project sponsors understand and want to see. I personally am a big fan of the Orchestration Designer for Business Analysts for use in creating tracking definitions. This can serve not just as a lunch point for BAM, but for the solution that you are creating. This tool is allows business users to define the data and milestones that are important to them. The output, an XML file, can then be imported into Excel for completing the definition and defining views. Once I show businesses what BAM offers them (especially when they learn no custom database code or structures must be written to support it) they want to use it for everything. This isn't a bad idea, it scales really well and is probably better than most of us would be given the time to develop ourselves. Jesus Rodriguez wrote a great BAM article recently for MSDN here.

Importantly BAM was not as robust in previous versions of BizTalk so the 2006 releases really took this a lot further. The greatest advance was undoubtedly implementing multiple continuations in Tracking Profiles. I think this contributes largely to the use of Orchestration as the primary business process analogy. Allowing multiple continuations really allows the visibility business want in a much more elegant solution than large Orchestrations, which were really the only way to accomplish this in the past.

I recently let my mind drift a bit while working on my book and a few training lessons and began to contemplate this question: what are the core guidelines for a BizTalk solution? In many ways these tenets are the same for non-BizTalk solutions as well and can serve as general guidelines for all distributed application (i.e. Enterprise System) development.

An Effective BizTalk Solution is loosely coupled and operationally flexible. It has clear boundaries, carefully controlled dependencies, and is as simple as possible without sacrificing the previous tenets. The nonfunctional requirements are well understood upfront and documented. Automated tests exist at the Functional and Stress Testing level. Building and MSI construction can be performed with very few simple commands and MSI is the method for installation (that is to say deployment is a clean, self contained, and easily repeatable process).

I've been on a pretty serious WCF kick for the past few months and more and more I love it. I like even more how well BizTalk works with WCF. BizTalk is the application server you normally need to provide yourself to make WCF very useful. In Handling Binary Messages in Orchestrations I had mentioned that I used SQL Reporting Services via Web Services to run reports and do something with the output. As I had been experimenting with the solution I tried using both the SOAP Adapter and the WCF Adapter and found the WCF Adapter to be much more to my liking.

Using the WCF Http Basic binding you are able to call existing SOAP services but I did run into a slight problem with the SSRS services. These services require impersonation and by default WCF handles impersonation differently than ASMX in IIS did. At first I thought I needed to change the web.config on the reporting server, but eventually I stumbled onto Delegation and Impersonation with WCF on MSDN. More digging informed me what it was I needed to do, change the default client credentials (which I think was delegate) to impersonate. This lead me down a quite difficult path with WCF and configuration files that helped me understand the concepts more, but didn't really help me solve my issue. Fortunately after searching around the WCF Custom Adapter settings for a while I found the solution and it was a lot easier than hacking configuration files.

To make my life easier I used the basicHttpBinding as my Binding Type In the properties for the WCF Custom Adapter. Also in these properties, under the Behavior tab, select EndpointBehavior on the left. Then right click and go to the menu option Add Extension; selecting clientCredentials as the extension. Now go into the client credentials and under the Windows section there is a dropdown for allowedImpersonation. This is not only exactly what I needed, it looks like most of the WCF config options are here and best of all you can import and export to a single config file for the bindings, so if you're sick of doing WCF bindings by hand you can use BizTalk to do them with a nice GUI.

Soon I'll post about some things I like more about the WCF Adapter over the SOAP Adapter.

This post presents some useful suggestions for the Visual Studio Solution structure to use in BizTalk development. It is based on both my experiences as well as that of others within the BizTalk community.

Suggestion

Generally I like to break my solutions up into the seven projects show below. I find that this meets the needs of most solutions, but it is not a hard fast rule. Some solutions will have more, others less, but each project is explained below.

External Schemas (.xsd files)

This project contains all of the schemas that are sent or received by the BizTalk solution. Port level mapping ensures no dependencies are leaked into or out of the BizTalk implementation. This would include schemas generated by adapter wizards, SOAP references, etc. It is vital that these be treated as what they really are: external artifacts (i.e. external dependencies).

Schemas (.xsd files)

The Schemas project contains all the schemas used internally by a BizTalk solution. These are schemas that are never exposed to any other systems and would be used to define entities within the actual solution. Every external schema should have a corresponding internal schema or translate to part of a composite schema.

Maps (.btm files)

The Maps project contains all maps within the solution. It references the Schemas and External Schemas projects and nothing else (with the exception of custom functoid assemblies). This is the guardian that prevents external dependencies from permeating a BizTalk solution.

Pipelines (.btp files)

All pipeline components (assemblers and disassemblers) are grouped in this project to make testing and maintenance easier. This project should reference the External Schemas project and /or the Schemas project if needed. Alternatively if you want to provide slightly more robust isolation you could create internal and external pipeline projects, but I really only recommend this if you need to call a Pipeline from within an Orchestration to avoid leaking a dependency into your Orchestrations layer.

Orchestrations (.odx files)

This project contains all Orchestrations used in the solution. It references the Schemas project and possibly the Pipelines project (if you're using pipelines from within an Orchestration). Workflows can also exist in this assembly in BizTalk 2009.

Library (C#, resources, etc)

Any artifacts that are used by your solution should also be broken into their own projects. It is important to not let these bleed dependencies into your solution. If you have some custom components to do processing in an orchestration and some for custom functiods or pipeline components these should be broken into their own distinct projects.

Testing (.xml, .dtd, .cs files)

This project hosts all Unit and Functional tests and their supporting data. The only direct references should be to the utilities assembly and to the testing frameworks (e.g. NUnit and BizUnit). This also ensures a clean separation of tests and artifacts from the other parts of a solution.

Non Project Artifacts

There are also several Solution level folders I like to use to organize my solutions. These generally are:

• 3rdParty Assemblies – for storing any external assemblies and components the solution may need to utilize.
• Bindings – to store deployment bindings used in each of the environments you will have in your solution. I generally have Local Development, Integration, UAT, and Production.
• Build – used to hold build scripts.

Relative Path Locations

Always use relative path locations for all artifacts including:

• Keys used to sign assemblies
• References to other assemblies (i.e. project references not absolute references)
• Paths of test files

This will make it easier to build the solution on multiple machines and also make it easier to use a CI server to perform automated building of the solution.

Remember not all solutions will have these seven; some will have less others more, but keep in mind the separation of concerns brought up in the discussion about layers in a BizTalk solution. Later on I'll cover some specific examples of when to combine which parts of this guidance.

Reasoning

It is natural and healthy to ask why go through all this trouble (ignore that creating projects in a solution is very simple; even more so with a template). Earlier I mentioned the need to control dependencies and this is a certain way to do that, but there are other reasons as well. You may find yourself needing to update in-flight Orchestrations or Maps in a solution with such Orchestrations. Perhaps it's a simple fix and you haven't quite worked out you versioning strategy. With separate assemblies you are free to deploy just the changes necessary rather than all assemblies and artifacts at once.

Perhaps more importantly you are also free to create packages that are specific to the type of server you are deploying them to. If you are working in a large BizTalk environment you may have a dozen or more servers partitioned by their functional role. You may want to create deployment packages for each role. This goes back to the previous discussion about Layers vs. Tiers. Building solutions in a separable manner is a good practice that allows administrators and operators more control over the solution once it is out of development. This is critical to the success of any enterprise software system and even more so to BizTalk solutions.

This is going to be one of a series of posts on the topic I plan to release over the next several weeks. Part of this is driven by what I've seen as a weakness in the community: a lack of direct application of established design principles. Most .NET projects these days follow a now well established set of guidelines and principles that help ensure elegant easily maintainable solutions. These guidelines were established in the context of .NET development; borrowing from and building upon the experiences of older platforms such as Smalltalk, C++, and Java. In an effort to lead the community back to these guidelines I'd like to recover some of these concepts in the context of BizTalk solutions.

Separation of Concerns

BizTalk even more than most platforms is fundamentally involved with separation of concerns (being an integration solution), but often this idea is lost in the implementation of a BizTalk Solution. I think that developers can get so focused on being the separation for external applications that they neglect to keep this principle in mind in their own solutions and often inadvertently let dependencies and tight coupling permeate their solutions.

Layers of a BizTalk Solution

The proximity diagram below depicts how the layers in a BizTalk solution should interact. Only layers that actually touch each other should be interacting in any way. The intention from this should be quite clear; it is to isolate the layers in the solution and minimize their interaction points so as to create a more robust solution that does not allow changes to propagate throughout the system.

This layout will give us the ability to change the solution at its edges or in its core drastically without impacting other parts of the solution. A new external partner schema should have no impact on our Orchestration layer nor on our Internal Schemas.

This can also be expressed in a more detailed fashion in a UML diagram. The following diagram expresses the basic layers (or dependencies) in a BizTalk Solution that follows these guidelines.

Again the goal is flexibility by tightly controlling contact points and Visual Studio can actually help to enforce this architecture in your solution.

So why do this? Some would argue that you could just do a logical separation using solution folders and not need separate Visual Studio projects for each of the packages shown above. While there certainly is some merit to this argument, it overlooks some very important capabilities inherent in this architecture.

For one there is no chance of a dependency creeping throughout your solution, the Visual Studio references between projects will ensure that. Further you can also choose now to deploy you solution in a variety of arrangements of Tiers derived from the Layers presented above.

Introduction

In my post How BizTalk Identifies Types I explained that any message can be represented within an Orchestration as an instance of XmlDocument even if it is not a valid XML document (such as a binary file). In this post I will explain how you can handle binary documents in Orchestrations and walk through how I handled receiving reports from SQL Server Reporting Services (SSRS) and sending them to Sharepoint via the Sharepoint Services Adapter.

Often there are times in an integration solution where non XML documents must be operated on by a solution. One such example would be receiving PDF or image files and sending them to another location. In simple cases you may be able to do this in a messaging only scenario, in more complicated cases you may need to bring in Orchestration.

Sending and Receiving Binary Messages in Orchestration

Although any message can indeed be represented as an XmlDocument in an Orchestration performing any operation on the XmlDocument (i.e. the message or variable that is the message) will result in a runtime exception. If you're solution requires some sort of action to be performed on these documents that cannot be done via direct subscription you are limited to setting promoted properties on the message (i.e. Adapter / BizTalk properties). This may be all you need to do and if that's the case you may not need an Orchestration in the first place, but maybe a Pipeline.

Creating Binary Messages in Orchestration

At times you may find yourself required to do something a bit more complex, like retrieve the binary result of a Web Service call (like I did with SSRS) and doing something with the message payload. In my case I wanted to put the report in a Sharepoint site dynamically, which certainly could have been done with the SSRS Sharepoint integration, but I don't like that solution as it is a point to point rather than hub and spoke remedy.

There are several ways to create messages in Orchestrations and some are not very well documented, but the one recommended way (documented in the Sdk\Samples\Pipelines\ComposedMessageProcessor example) to create new Binary Messages is to use a send Pipeline called by a Message Assignment shape. Creating the Pipeline can be made much easier by using the BizTalk Server Pipeline Component Wizard which is a free Visual Studio plug-in that makes this a very simple process (we'll look at this later).

The main caveat here is something I pointed out in How BizTalk Identifies Types and that is that Pipelines and Orchestrations use different interfaces to represent messages. As a result, you must the use the supplied wrapper classes for executing this functionality. For one you will need an Orchestration variable of type Microsoft.XLANGs.Pipeline.SendPipelineInputMessages (which requires a reference to Microsoft.XLANGs.Pipeline.dll, in your BizTalk install location).

Calling the Pipeline

The basic steps are quite simple: add a message to an instance of this class via the Add method which takes one parameter: the message to be added; then call the Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteSendPipeline method from within a Message Assignment shape or an Expression shape before the Message Assignment.

Every example I've ever seen including Composed Message Processor always first sets the output message (the XLANGMessage that is the last parameter) to null before calling this method so it's probably a good idea to do so.

Microsoft.XLANGs.Pipeline.XLANGPipelineManager.ExecuteSendPipeline(

    Type, SendPipelineInputMessages, XLANGMessage);

Be sure the Type is the that of your .btp pipeline that uses your component, not the Pipeline component created by the Wizard that we discuss below.

After this your binary message is in the message you supplied for the XLANGMessage parameter which I generally make as type XmlDocument. From here you are free to do what you like with the message, but remember it is not really an XmlDocument so don't call any of its methods.

More about that Pipeline

After you've run the Pipeline Component Wizard you have a class that implements IComponent, IBaseComponent, IPersistPropertyBag, and IComponentUI. The single method Execute of IComponent is the one we're interested in, this is what will be called when ExecuteSendPipeline is invoked.

If your situation is like mine this is all slightly complicated by the fact that the binary data you want is in a Base64String typed element within a message you have received. This is where the Pipeline Component Wizard can really help by allowing you to specify properties that can be set for the Pipeline. I declared an xpath used to read out the payload element from the body, this way I can reuse my Pipeline or change message formats. You can also create a class that represents your original message (containing the binary element) and use the XmlSerializer to create an instance of this message and access the byte[] property containing your raw byes (the XmlSerializer handles the translation to and from Base64String for you). End result, don't forget to convert from Base64String to byte[] or your file will be corrupted.

Below is the code from my Execute method. The properties are created for you in the Wizard and you don't need to worry about the other parts of the class.

//This is required: create the message to return

XmlTextReader reader = new XmlTextReader(inmsg.BodyPart.Data);

IBaseMessageFactory factory = pc.GetMessageFactory();

IBaseMessage message = factory.CreateMessage();

IBaseMessagePart body = factory.CreateMessagePart();

//This uses more properties and reflection to process the message

System.Runtime.Remoting.ObjectHandle objectHandle = Activator.CreateInstance(_assemblyName, _typeName);

object underlyingObject = objectHandle.Unwrap();

XmlSerializer serializer = new XmlSerializer(underlyingObject.GetType());

underlyingObject = serializer.Deserialize(reader);

object result = underlyingObject.GetType().InvokeMember(PropertyName, System.Reflection.BindingFlags.Public |

System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.GetProperty, null, underlyingObject, null);

byte[] resultBytes = (byte[])result;

Importantly you use either the segment above or the one below, but not both.

//This avoids the relfection, but makes the pipeline only useful to one message type

XmlSerializer serializer = new XmlSerializer(typeof(RenderResponse));

RenderResponse response = (serializer.Deserialize(reader)) as RenderResponse;

byte[] resultBytes = response.Result;

//This also is common to both approaches

MemoryStream memStream = new MemoryStream();

memStream.Write(resultBytes, 0, resultBytes.Length);

memStream.Position = 0;

body.Data = memStream;

pc.ResourceTracker.AddResource(memStream);

StreamReader sr = new StreamReader(body.Data);

message.AddPart(inmsg.BodyPartName, body, true);

return message;

At some point you'll probably want to populate some properties for the Adapter you're using either in the Message Assignment shape in your Orchestration (which I did) or in the Pipeline itself.

Once you have your pipeline component you add it to your \Microsoft BizTalk Server 2006\Pipeline Components directory and you are free to use it when creating .btp files. At this point you simply add the Pipeline to your BizTalk Toolbox (by picking Choose Items) and then drag it into the encode stage.

If you've used properties to make your Pipeline configurable you would set them here as well. This is the Type that you will specify in the call to ExecuteSendPipeline in the Message Assignment shape.

Improvements

I know there must be a way to stream this and it would be a good idea. Loading this message into an XmlDocument or using the XmlSerializer will load the entire message into memory; this can cause problems with very large messages. The entire BizTalk engine is designed to use streaming and it would be nice to incorporate it here as well. If you can think of a way to do this, please let me know.

Overview

BizTalk Server is a great tool that can aid in the development in integration and SOA solutions. It is a force multiplier that allows one developer to perform the work of many. It is reliable and scalable. It is also a little difficult to get your head around at first. One of the greatest difficulties developers have starting out with BizTalk is to understand how types work in BizTalk Server. This is a pretty straightforward concept in .NET so the answer can throw off some people.

Types of Types

BizTalk primarily has two major types of types: Message Types and .NET Types. Everything in BizTalk is one of these and some things are both. The .NET types are simple enough, but we'll discuss some of their implications shortly. The Message Types are less so. Being a Messaging Platform everything that flows into or out of BizTalk Server is a message. Binary, XML, Text they are all messages. BizTalk identifies XML Message Types based on the namespace and root node of the XML instance itself. Since XML legally only allows one root node in a document this is a pretty standard identification. An example would be "http://novaenterprisesystems.com/schemas/sql#SqlRequest" where "SqlRequest" is the root element of the message and the schema is "http://novaenterprisesystems.com/schemas/sql". This works fine for XML messages, but other messages don't really work the same.

Types in Contexts

All messages in BizTalk's Messaging Engine are represented as an instance of Microsoft.BizTalk.Message.Interop.IBaseMessage and in a Pipeline the same holds true (as Pipelines are messaging components). Once in an Orchestration a message is represented as an instance of Microsoft.XLANGs.BaseTypes.XLANGMessage and can be sent to or from a user component as such. Interestingly enough these types cannot be converted between them so you'll generally use IBaseMessage in Pipeline Components and XLANGMessage exclusively in Orchestrations.

To make things a little more complicated any message in an Orchestration it is implicitly understood to be an instance of System.Xml.XmlDocument. It is not so much that these classes are all related, but they are functionally and have both practical and historic reasons for this arrangement. One is that it allows you a simple way to deal with non XML messages in orchestrations, you can specify their type as XmlDocument and handle just about any type of message, even binary. That said if you have a binary message and try to use any of the members of XmlDocument on it you will receive an exception from the Orchestration Engine.

These different interfaces allow different types of interactions with messages in their own contexts' which can sometimes be challenging until you understand the lay of the land so to speak.

To sum it up in these contexts messages are these types:

Messaging Engine: IBaseMessage

Orchestration: XLANGMessage or XmlDocument

Later I will post on how to create messages in the different parts of BizTalk Server.

Earlier I wrote about how to use one SQL Port for multiple operations within a BizTalk Application / Orchestration. Now I would like to address another issue I often see handled in a manner I think is not the most appropriate. The SQL Adapter is a great tool, but may be a little too Black Box for many developers to understand the proper uses of. Perhaps the most common is how to handle multiple calls to a SQL Service (be it stored procedure or updategram).

Often the scenario looks like this: A message arrives with a repeating element in it and each element must be inserted into a database. Like many things in BizTalk there are actually several ways to tackle this problem and at least one doesn't even involve Orchestration, but usually the solution I see looks something like this.

Usually the loop iterates over an xpath expression extracting each message and assigning it to another that will then be sent to the SQL Port in question. This does work and does sort of do what the author intended. There are, however, a few potentially serious issues with this. For one there is not transactional integrity between the sends to the SQL Adapter. The last one could fail and the others would already have been sent and committed to the database. You could add compensation logic, but that sort of just piles on more mess on an existing mess.

What many do not realize is that a request can be made much simpler. The schema for the SQL send will probably look something like this:

-

With a instance like:

<ns0:GetDataRequest xmlns:ns0="http://nova/biztalk/sql">

<ns0:ReadSecurityIntervalData LastTime="1143003000" SecurityId="1" TimeFrameId="2" />

</ns0:GetDataRequest>

By default the generated schema does not have the Min Occurs or Max Occurs properties set for an request message (it does however set them to zero and unbounded for response messages). If you simply set these as the response elements are set (zero and unbounded) you can send multiple requests to the SQL Adapter at a single time.

<ns0:GetDataRequest xmlns:ns0="http://nova/biztalk/sql">

<ns0:ReadSecurityIntervalData LastTime="1143003000" SecurityId="1" TimeFrameId="2" />

<ns0:ReadSecurityIntervalData LastTime="1143006000" SecurityId="1" TimeFrameId="2" />

<ns0:ReadSecurityIntervalData LastTime="1143009000" SecurityId="1" TimeFrameId="2" />

<ns0:ReadSecurityIntervalData LastTime="1143012000" SecurityId="1" TimeFrameId="2" />

</ns0:GetDataRequest>

This not only saves creating an indeterminate number of messages, it ensures that they are all sent to the SQL Server in one atomic transaction (regardless of the transaction type in the Orchestration, which can't use an Atomic Scope with Send-Receive ports in any case) and as a single batch which reduces the number of trips to the message box and increases the throughput of a solution. The SQL statements will all be performed in the same DTC transaction in an all or nothing fashion which may be more in line with the business requirement in the first place.

This can be extended even further and combined with my original posting about SQL Server Adapter Port (Ab)Use. Not only can you call multiple operations through a single port, you can combine them through a single request and all in one operation. Suppose you had a series of stored procedures that all needed to be called and succeed or all rollback in a single transaction. Most people don't know that this is even possible with the SQL Adapter. Not only is it possible it's also pretty easy. This time you're request element (the root node you used for all your SQL Wizard-ry) will be made to use types defined for the different operations. You'll want to use a map to create your new request message and fill in all the elements that make up the operations you wish to call. This will result in a message like the following.

<ns0:SqlRequest xmlns:ns0="http://nova/biztalk/sql">

<ns0:ReadSecurityIntervalData LastTime="1143003000" SecurityId="1" TimeFrameId="2" />

<ns0:GetLastTrade TimeFrameId="2" />

</ns0:SqlRequest>

As can be seen here the request message contains multiple SQL Operations in a single message. I have gotten this to work in calling multiple stored procedures of different types, but so far I'm only getting the result of the first query. I will post on that solution after I find it.

One of the features that make BizTalk a great tool to work with is Adapters. Although they can take a while to learn the Adapter in BizTalk Server is the gateway to other systems you integrates in a BizTalk solution and is thus vital to BizTalk solutions.

Like many features in BizTalk there is great beginning documentation for the SQL Adapter, but shortly after that it sort of drops off and it can be difficult to find more advanced information.

One of the common complaints I see on the USENET groups I take part it (Microsoft.public.biztalk.*) is having to create multiple SQL Ports to call different stored procedures or updategrams on the same database. The Add Generated Items wizard for the SQL Adapter certainly contributes to this and it also does a few things I don't particularly care for.

For one it creates a new orchestration containing the multipart message types and the port that you can use for calling the Adapter. This in itself is not that bad (see my post about multipart messages in Orchestrations to see why). But most of us either call more than one SQL Service or are adding it to an existing orchestration (or not using orchestration at all). I don't like this new orchestration just to hold two multipart message types and a port that take very little time to create manually and in the location and manner you prefer. Worse still the orchestration is in the same Visual Studio Project as the schema which violates one of my guidelines for solution structure in BizTalk (see post)

Secondly it gives some terrible names to the Port, Orchestration, and schema file/ type. You should always change those names because they are meaningless by default (doing so, however, will break the orchestration that the wizard created for you since it will no longer find the types it wants).

This is the downside to wizards. To be more useful in the majority of cases they are less so in the minority of cases.

After generating the artifacts for your SQL use you will be left with a schema that looks like the following.

As you can see the schema has two root elements in it, one for your request and the other for your response.

The real issue comes when it is time to bind your orchestration ports. If you used the wizard and call four different stored procedures on the same database by default you will have four different send ports because each requires a namespace and root element of the returned message. This is actually where we can make a few changes to avoid having to create a separate port for each SQL call (even across orchestrations within the application). By this I mean we can share a port that connects to one database to call whatever operations on it we like. And it's not nearly as much work as I thought it would be.

Steps

Step 1

First when creating your schemas using the wizard be sure to use the same namespace every time. This really makes sense since you're doing the same thing, calling a database, in the same application. This is important because BizTalk identifies message types as a combination of namespace and root node name (for more info see: How BizTalk Identifies Types).

Next also in the wizard name your root response element the same such as SqlResponse. The Adapter actually puts whatever SQL Server returns under this node so it will not interfere with your returned data (you can skip this step if you like, but it might save you some headache down the road).

Step 2

Now comes the tricky part, by naming all of your response elements the same you're about to break BizTalk, so you need to combine them all into one schema. Either create a new schema for them (using the same namespace again and same root node name) or pick one of your existing ones to build off of. I've never found a friendly way to do this moving in Visual Studio so I just fire up TextPad and do it manually. Put each of these child elements under the root node you've designated as your Response Document Root Element (from the wizard in the previous step). After you put them there specify that they are Nilable and set their Min Occurs to zero. Don't worry this is the worst part and it's still not that bad. Also note if you were already doing things with these messages before consolidation (as I was when I wrote this article) you may see your orchestrations no longer able to identify the message types you just moved. If this happens it generally is resolved by closing Visual Studio and reloading the project.

Now in your Orchestration (if you're using one) just add operations to the one SQL Port you have. This can be done in the Orchestration View under Types -> Port Types. Technically I think you can get away without doing this, but it's still a good idea. It conveys intent and it allows for better message routing.

That's it, now you have one SQL Port that can be used for an unlimited number of operations in your BizTalk Application. If you want to be even bolder you can make this port public and allow other applications to use it, but I generally shy away from this for dependency reasons.