Richard Seroter has a great post about how to debatch inbound messages from the WCF SQL adapter in BizTalk 2009/2010: http://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/
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.
3 thoughts on “Property promotion when debatching messages from the BizTalk WCF SQL adapter”
“property promotions in the incoming schema (which is created automatically if youâ€™re using typed polling) doesnâ€™t work”
Why doesnät it work?
Actually, I might have been wrong about that. I couldn’t get it working but if you read the comments to Richard Seroter’s blog post that I mentioned above, it seems that others were more successful. Perhaps I had another problem that went undiscovered.