Debatching Inbound Messages From WCF SQL Adapter in BizTalk & Typed polling Debatching

Post by Richard Seroter

First off, I created a database table to hold “Donation” records.  It holds donations given to a company, and I want those donations sent to downstream systems.  Because I may get more than one donation during a WCF-SQL adapter polling interval, I need to split the collection of retrieved records into individual records.

After creating a new BizTalk 2009 project, I chose to Add New Item to my project.  To trigger the WCF-SQL adapter wizard, you choose Consume Adapter Service here.

After choosing the sqlBinding as the adapter of choice, I chose to configure my URI.  After setting a polling ID, server name and database name on the URI Properties tab, I switched to the Binding Properties tab and set the adapter to use Typed Polling.

Next, I set my PollingDataAvailableStatement to a statement that counts how many records match my polling query.  Then I set the PollingStatement value to look for any records in my Donation table where the IsProcessed flag is false.

With my URI configured, I connected to the database, switched to the Service contract type (vs. Client), I’m able to choose the TypedPolling operation for my database.

When I complete the wizard, I end up with one new schema (and one binding file) added to my project.  This project has a few root nodes which make up the tree of records from the database.

To make sure things work at this moment, I built and deployed this application.  I added the wizard-generated binding file to my BizTalk application so that I’d get an automatically configured receive location that matches the WCF-SQL adapter settings from the wizard.

After creating a send port that grabs all records from this new receive location, I started the application.  I put a new row into my database, and sure enough, I got one file emitted to disk.

That was easy.  If I create TWO records in my database, then I still get a single message/file out of BizTalk.

So, we want to split this up so that these two records show up as two distinct messages/files.  When using the old adapter, we had to do some magic by creating new schemas and importing references to the auto-generated ones.  Fortunately for us, it’s even easier to debatch using the WCF-SQL adapter.

The reason that you had to create a new schema when leveraging the old adapter is that when you debatched the message, there was no schema matching the resulting record(s).  With the WCF-SQL adapter, you’ll see that we actually have three root nodes as part of the generated schema.  We can confirm this by looking at the Schemas section in the BizTalk Administration Console.

So, this means that we SHOULD be able to change the existing schema to support debatching, and HOPEFULLY it all just works.  Let’s try that.  I went back to my auto-generated schema, clicked the topmost Schema node, and changed its Envelope property to Yes.

Next, I clicked the TypedPolling node (which acts as the primary root that comes out of the adapter) and set the Body XPath value to the node ABOVE the eventual leaf node.

Finally, I selected the leaf node and set its Max Occurrence from Unbounded to 1.  I rebuilt my project and then redeployed it to the BizTalk Server.  Amazingly enough, when I added two records to my database, I ended up with two records out on disk.

Pretty simple, eh?  When the record gets debatched automatically by BizTalk in the XML receive pipeline, the resulting TypedPollingResultSet0 message(which matches a message type known by BizTalk) gets put in the MessageBox and routed around.

Over coming  Problems with Above Approach

The problem is that when polling a database table for new data, we get a single message containing all matching rows. In most cases you probably want to debatch this message into separate message, one for each row, and it’s actually rather easy (see Richard’s post for the details).

There seems to be one problem though, namely that property promotions in the incoming schema (which is created automatically if you’re using typed polling) doesn’t work. One solution for that is to create a separate intermediate schema with the required promotions and transform the incoming messages into that schema using the “inbound maps” setting on the receive port. Note that the map’s source schema should be the top TypedPollingResultSet0 element of the generated schema, not the one below the TypedPolling element, since the transformation is done after debatching takes place.

As an added bonus you don’t mess up your message flows with “TypedPollingResultSet0″ message types, that are rather non-descriptive. A message type name of “RaindanceNotification” (or whatever you name the intermediate schema) is much more communicative.