Debatching Sql receive adapter resultsets

I spent Friday with a client advising them on some basic design principles, specifically around the use of the Sql receive adapter. It was a fairly simple scenario - extracting data from a SQL Server database, then processing the output messages with some very simple orchestrations. It seemed fairly obvious to me that the easiest way to process the data was to split the output from the receive location, then process each record individually, which is where it started to go awry.

Problem I
The easiest way to split out messages from a batch is to use an envelope schema within a receive port. However, if you create a schema using the SQL adapter wizard (as most people do when using the SQL adapter), the resulting auto-generated schema is not an envelope.
Solution: the solution to this is to map this schema to one that is an envelope.

Problem II
Maps are executed after receive pipelines; if you put the map in the receive port, by the time the map is executed, it is too late to debatch, and you would end up with the envelope in the messagebox, rather than the split documents.
Solution: use a loopback send port to get the envelope back into a receive port for debatching.

Problem III
As per the discussion here, it is not possible to map to an envelope schema in a send or receive port (fixed post-SP1?).
Solution: pass the original batch message into an orchestration, map to the envelope, then use a loopback to get the split messages into the messagebox.

Phew.

By now, the proposed solution was so complicated that, although it worked, it was hard to persuade anyone that it represented a 'best practice'.

A day on the train and a bit of free thinking time got me to the better solution - hacking the auto-generated schema.

Although the generated sql schema isn't initially marked as an envelope, there is nothing to prevent you from doing just that. This means that the direct output of the sql receive location will be split in the pipeline, and that all of the individual messages will be arrive at the messagebox. As a note, the documents that are split out conform to a partial schema, and so if you want to use these, or better still, map them to some common canonical form, then you will need to create a schema with the same namespace that matches this portion of the original sql schema. (If you do decide to do this, remember to mark each element 'Form' as "Qualified".)

I have a sample project that is available on request (I have nowhere to host files, so I'll have to email it out I'm afraid) that selects first and last names from a source table, debatches the output and then maps each document message to a canonical Fullname message. Two separate orchestrations then subscribe to the Fullname message, one that extracts the firstname and inserts into a second table, and another that does the same with the lastname. The sample includes the BizTalk project, sql scripts and a binding file.

STOP PRESS: after three years, I've finally gotten around to uploading the solution for all those who are interested. It's on Codeplex - http://www.codeplex.com/biztalkdebatch