Effective BizTalk

I recently started working with the DB2 Adapter for BizTalk Server and must admit I've been pretty happy with the results. DB2 not one of my specialties, but it is still out there and not going away anytime soon. I always enjoy getting the chance to work with new adapters when I can. The Adapter is reliable and fast and makes working with yet another platform a pretty simple experience for the BizTalk developer.

I did, however, have one unexpected experience with it having to do with Element Form Defaults (which I guess I'll write about more later). After generating schemas for receive locations I eventually figured out that the adapter was returning data with an Element Form Default of qualified even though the schema was set to unqualified. As I tested my maps it was clear from the validation failure messages that the namespace was an issue. This was pretty easy to fix (change the Element Form Default to Qualified). Easy enough.

Later I found myself doing sends to the DB2 Adapter and found some more interesting details. Request messages sent to the DB2 Adapter must Unqualified (that is their Element Form Default cannot be Qualified). Easy enough again. Unfortunately the response to these messages still follow the requirement of being Element Form Default Qualified. This presented a bit of a Chicken and Egg scenario. The solution is actually a good practice in my book: don't have different messages in the same schema. I broke the request and response into their own schemas and set the Element Form Default separately.

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.

I recently posted about Handling Binary Messages in Orchestrations and took a little time to reflect upon a key issue I saw recently (after I wrote that post) and that is working with non-XML documents within BizTalk in general. BizTalk is certainly an XML-centric product. This is a natural fit for any sort of integration platform; which as the XML name states is extensible. But often we're forced to cross out of the clean world of XML (yes, I think XML is clean, I've worked with it for almost nine years). This is where developers can get into trouble. Anytime you find yourself dealing with a non-XML artifact in BizTalk you should really convert it into XML as quickly as possible before it has a chance to get into the center of your solution.

An Example

A good example I saw is dealing with email. I ran across a solution that was handling inbound and outbound email and it was clear there had been challenges for the developers during its creation. For one as I already pointed out, BizTalk is really XML based and handling non XML in Orchestrations can be very tricky. This can also lead to unintentional dependencies that can limit the flexibility of the solution and complicate the testing.

On that testing note, I like to be able to substitute all external systems in my solutions during development. This can be a bit trickier for Request-Response ports, but it is still possible even if you're just using simple stubs or stand-ins. Further it is always good to be able to change adapters after solution rollout for operational reasons. Basically this means any One Way port should be replaceable with another. A great example would be SMTP or File; you should be able to swap these at anytime or switch to something else like FTP. This just makes a solution more flexible without requiring code changes and this is one of BizTalk's greatest strengths.

An Example Solution

At first the value of this may not be clear. Let's say for instance that your solution needs to receive emails and do something with them. When the POP3 adapter receives an email it creates a binary multipart message with parts for the subject, body, and attachments (one each). You would probably also want to know other information such as From and To. You could write a solution that uses POP3 properties directly from within an Orchestration like: EmailMessage(POP3.From), but this will be both harder to test and propagate adapter details into your Orchestration. Make no mistake; that is an external dependency.

I think a better way to handle this type of requirement is to define an XML message to represent everything you need from the email. Such a schema may look like this:

From here you would probably want to use a Pipeline to create your message. Since the Pipeline executes in the context of the Adapter, by the time the Message Box is reached this is no longer a binary multipart message, but a single part XML message. In this schema I've defined Attachment as Base64Binary so it can safely store any type of attachment.

Now when I want to build functional tests for my Orchestration using BizUnit (which is really a great tool and even trivial solutions should use it) I can just use a File Receive location instead of the POP3 Adapter (which is both faster than POP3 and doesn't require another external dependency: a POP3 server). Obviously you would want your Deployment Verification Tests to use the POP3 Adapter for true end to end verification and you would also want to test your Pipeline either using pipeline.exe or some of the techniques Michael Stephenson recommends here.

Ultimately this is the same as mapping at the Port, which should always be done to avoid external dependencies. Often doing something right in BizTalk requires me to rethink my initial approach in the context of how the product works. I have always had great results if I take the time to do this.

An Easy Way Out

The last thing I'll add is that creating a Pipeline to do this isn't really an easy task if you're new to BizTalk; so if you need a quicker method that you may be more comfortable with: simply receive the email into an Orchestration that just does the XML message creation for you reading from the message and the adapter properties. Then you just submit your message directly to the Message Box and your processing Orchestration can receive them directly as well (it will not know the difference between these two approaches: pipeline and receiving orchestration).

Being an extensible .NET based product it is certainly possible to make BizTalk do pretty much anything you want (even deal with non-XML documents ad nauseam), but you may find yourself fighting the product rather than leveraging it.

I've had a chance to get to work with the Sharepoint Adapter quite a bit recently and have come away from the experience relatively pleased. That said there were some lessons that could serve as a guide for others on this journey. First the BizTalk Adapter does work great, but it does not work like most of Sharepoint does. I spent a lot of time with Sharepoint and BizTalk separately so bringing them together presented some challenges for me. My ideas of how Sharepoint likes to work and is most often used were not the same as those of the BizTalk Adapter and this did end up having an influence on the shape of my solution structure.

Receiving with the Sharepoint Adapter

Versioning vs Archiving

Sharepoint has the concept of versioning deeply ingrained in the product. From the user perspective versioning is relatively seamless and the user never really sees it happening. The Sharepoint Adapter does not use versioning and will not work if a document library has versioning turned on. Also it's important to remember that the Sharepoint Adapter is only meant to work with documents not lists (despite the two pretty much being the same within Sharepoint).

The Sharepoint Adapter takes an all or nothing approach meaning that when it polls its target library (or view as we'll see later on) it removes all the documents it finds. More precisely it copies them to the archive location that you specify in the Adapter configuration. If you do not specify an archive location the documents are simply deleted. This at first may take a little time to get used to, but it makes sense. The documents must be removed because the adapter will pick them up multiple times if they are not and this is almost certainly not what you want.

The way I got around this (and read to) was to use a folder to copy the documents into that got them out of my pickup location, but kept them in Sharepoint for reference. The nice thing about the Adapter is that it will lock the document and try to make the archived copy first, before deleting the original file. If it fails to do so for some reason (such as a file name conflict) the document will stay in the pickup location (and the Adapter will write an error to the event log).

The configuration window below shows all the settings for the receive adapter, they are all pretty self explanatory and other than the archiving part do exactly what you expect them to do.

The only piece of warning I would give is that unlike many Adapters this one lets you set times in seconds. There is a trade off to be made between a longer polling interval and server traffic (i.e. latency vs. throughput). The Adapter is designed to pull multiple messages at ones from the view and this will make it scale extremely well, but lower that interval too much and your Sharepoint server will start experiencing undue load that could adversely affect its performance.

Views

As mentioned earlier the Adapter can use Views in Sharepoint to filter the pickup location. This can be very useful if you are building a solution where both users and Sharepoint will be performing updates on the documents. I added a Has Chanes (Yes/No) column to my list that allowed the solution to filter which documents required action from the Adapter. Anytime a change was made by a user the flag was set to "Yes" (true), the Adapter picked up the file, and an Orchestration (the child introduced below) immediately sent the document back updated and with the Has Changes column set to "No" (or false). This allowed me bidirectional updates on documents in a library. A child Orchestration (having been created earlier by a parent) listened for updates on an initialized correlation token (an ID in the document) and passed these documents to the parent Orchestration which was then free to send documents back to the library certain that it always had the latest version. By using a self-correlated port that the parent sent into the child it simply used a receive to get the latest document version without having to pull it from Sharepoint directly.

Sending with the Sharepoint Adapter

Much of sending with the Adapter is, as you would expect, similar to receiving. The most important settings to note here are the Namespace Aliases and Overwrite settings as these will impact both your xpath field mappings as well as your writing to document libraries. These same settings also exist in the receive configuration and for a good explanation visit the MSDN Documentation.

InfoPath

InfoPath is the final piece in the Sharepoint Adapter puzzle and can be used to great effect with Sharepoint and BizTalk as the primary User Interface. This is how I was able to control the setting of the Has Changes column that was vital to my bidirectional update solution. When using InfoPath with Sharepoint; templates can be setup to post to a document library and fields in that template can be mapped to columns in the document library. The Adapter works in the same way and can also be used to set any column in the list and even use content from within the message to set the fields without using InfoPath at all. If you're using InfoPath you don't have to do anything special to set these fields, but if you're not the option still exists for setting as many as sixteen fields in the document library.

Conclusion

Ultimately the Adapter makes working with Sharepoint easy, reliable, and scalable. These last two points, reliable and scalable, are something to not be overlooked because there is one thing I have seen a lot of experienced Sharepoint developers tend to do (and I used to as well): build their own Sharepoint Features for interacting with the outside world. The really important aspect of how this Adapter works with Sharepoint is that it is all completely asynchronous and isolated; a failure or outage at any one part will not result in lost documents. They will simply be queued for processing when the outage passes. On top of this it is configurable and centrally managed. It also avoids creating a tangled mess of point to point integrations with Sharepoint.

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.