I have an orchestration with a two-way send port to retreive one or many items from a database. I use WCF-SQL adapter, and successfully managed to debatch incoming result into separate StoredProcedureResultSet0 messages. However, orchestration only processes one message and ends.
Is there a way for the receiving orchestration to loop over returned messages before ending?
Error message I receive now for the unprocessed messages:
The instance completed without consuming all of its messages. The instance and its unconsumed messages have been suspended.
I am aware that some people have initiated debatching using pipeline from within the orchestration. I thought that it would be preferable to debatching as close to the source as possible.
Ideas?
Final solution was to do debatching directly in receiving pipeline. Using XPath in Orchestration have performance risks, and using correlation sets, like @Dijkgraaf answered, also comes with risks.
However, trying to implement choosen solution, I bounced into the following problem:
XMLReceive pipeline failed to debatch message if there were no results returned from the stored procedure. This is because WCF-SQL adapter omits the resulting element if no rows are returned, and debatching (Body XPath) requires it to exist. Also, tried to have the Body XPath to work even if there was no results using XPath function count(), but BizTalk does not allow "complex" XPath expressions (given by error message, no reference found).
So, I had to develop a receive decode custom pipeline component to add the element if missing. Then XML Disassmebler works and consumes the message even if element is empty.