A buddy of mine asked me this morning how to do debatching with the SQL Adapter. While I fully understand XML and flat file debatching, the SQL Adapter uses a generated XSD schema, and I wasn’t 110% sure of the best way to handle that. So, as usual, I figured I’d build it and see what happened.
[04/08/2010 Update: I’ve done a new post showing how to do this with the new WCF-SQL adapter. You can read that here.]
So let’s start with a database table and stored procedure. I created a simple “Customers” table and a procedure that grabs every customer flagged as “New” and then sets those values to “Existing” after pulling them.
Next, I constructed a BizTalk project, and did an Add –> Generated Items and chose to build a schema from an adapter. After picking the SQL adapter, I chose to use the stored proc built above. The auto-generated schema then looked like this …
Make sure you go back afterwards and remove the XMLDATA clause since it’s only used when you need to generate the schema. Next I built and deployed the project. Finally, I set up receive and send ports. The send port simply has a filter subscription pointing to BTS.ReceivePortName. The Receive Location uses the XML Receive pipeline and the SQL adapter, configured as such …
Remember that the out-of-the-box XML Receive pipeline will do the debatching for you if the schemas are set up right. If you use the Passthrough pipeline, nothing’s going to happen. So what happens when I enable the Receive Location and turn on the Send Port? I get a single message, holding all three records pulled. That’s the default behavior here.
So now I went back to my schema to convert it to a recognized “envelope” schema. You do this by setting the Envelope property to “Yes”, and setting the Body XPath on the root node. In my case, the Body XPath should point to the root, since we want everything under it (the TempCust node instances) to be yanked off. I also set the Max Occurs on the TempCust node to 1.
Now after deploying this updated project and resetting the database table, what do you expect will happen? If you said “you’ll get some beat error message” then you win.
See what happened there? Each message got debatched, but when trying to find a schema for the TempCust message type, BizTalk failed since no such schema exists. We only have a schema for the NewCustomers type.
So how do we fix that? Easy, create a schema for the TempCust body message. The trick is to not create any more work for ourselves than we have to. So, I created a brand new schema, and chose the Imports option. Here I pointed to the “Envelope” schema we created above.
Now I can reuse the previous schema without manually re-creating the TempCust format. After importing, I pointed to the root node of my new schema and set its Data Structure Type property to the TempCustType option in the drop down list. Immediately, the type gets loaded into my new schema. I changed the root node name to “TempCust” and set the Root Reference of the schema to the “TempCust” node (since we now have a multi-root schema). Now, when the BizTalk engine debatches the NewCustomers message and is looking for a schema that corresponds to the TempCust message, we’ve got one.
Nice! Now if I deploy, and reset my database, I see three individual messages get sent out of BizTalk, one for each row in the database table. This model works well because if any changes are made to the auto-generated schema, my “SingleCustomer” message also gets updated. I don’t have to keep two separate (but related) schemas manually in sync.
Also note that now you’ll want to be binding to the http://%5Bnamespace%5D#TempCust type, not the original schema generated by the SQL adapter. So an orchestration message would be of the above type, not the envelope. Or if you have a send port listening for message types, the http://%5Bnamespace%5D#TempCust is the type that matters, since the http://%5Bnamespace%5D#NewCustomers format no longer exists after the pipeline debatches the original message into the resulting individual messages.
There you go. Any other ways you folks handle this sort of thing?
Technorati Tags: BizTalk