Gobbledygooks by Richard Hallgren

Get row identity from SQL Adapter response

Recently I had to insert a record using a stored procedure and the SQL Adapter in BizTalk 2006. There are lots of examples on both how to insert records and how to select a number of record using this adapter. However I had problems finding how to insert a record and receiving the new id of the inserted row in return (the SCOPE_IDENTITY()). In my scenario I needed the id to insert into another database further down in the orchestration.

I ended up with a stored procedure looking like the below.

ALTER PROCEDURE [dbo].[TestInsertParty]
    @partyName nchar(30) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    Insert Into Party ([name], chain_idx) Values(@partyName, NULL)
    Select Scope_Identity() As Id For Xml Raw ('Response')
END

The trick was to use the XML RAW Mode. This mode transforms the result set into a generic identifier as <row>. It is however possible to provide a element name, as <Response>.  Basically this will insert the new value and return something like this from the stored procedure.

<Response Id="1054" />

After return via the send port the orchestration will receive something like the below.

<TestInsertResponse xmlns="TestInsert">
    <Response Id="1054" />
</TestInsertResponse>

The schema that I use to both handling the response and request against the SQL Adapter is shown below. First I set the type of the Id-attribute to xs:int but this gave me some problems when using the promoted value in the orchestration, everything worked fine when switching back to xs:string.

 
The same technique would be used for receiving a code from the stored procedure (say 1 for success and 0 for failure or whatever) and then to make a logical decision in the orchestration.

Convoying

Convoying is one of BizTalk’s real strengths but it has a lot of pitfalls (I’m talking performance, zombies etc). This article is really good at explaining the different patterns used for creating convoys. It also makes some deep dives into how the subscriptions are solved, why zombies are created and how to deal with them.

Even if you feel you understand the convoy patters from before the part about how BizTalk solves the subscriptions for correlations (the part called Basic Convoy Theory) is great.

Using sequential convoy to handle ordered delivery

A big selling point in BizTalk 2006 is it’s ability to handle Ordered Delivery. It’s important however to understand that this setting (on the Receive and Send port) only works in a pure messaging scenario (a scenario without orchestration, just passing messages between ports). To get order delivery in a scenario using orchestrations one has to use the sequential convoy pattern (basically forcing the orchestration to only one instance on one thread). This Webcast explains this in depth and also deals with some of the different problems that are related to the issue. Problems like performance of course, but also the requirement for receive adapter (on the send side all adapters support ordered delivery) for enabling ordered delivery (you’ll have to use MSMQ, MQSeries or specific scenarios of File, SOAP or HTTP).

Both convoying and ordered delivery are important concepts to understand in depth to be able to make the right decisions in a BizTalk 2006 solution.