A few years back now (sheesh, that long already??) I wrote a post about debatching messages from the classic BizTalk SQL adapter. Since that time, we’ve seen the release of the new and improved WCF-based SQL adapter. You can read about the new adapter in a sample chapter of my book posted on the Packt Publishing website. A blog reader recently asked me if I had ever demonstrated debatching via this new adapter, and to my surprise, I didn’t found anyone else documenting how to do this. So, I guess I will.
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.
Has anyone done this yet? Any experiences to share? This used the TypedPolling mechanism, but hopefully it’s not too different with other polling mechanisms.