[BTS] [add to favorites] Use SqlAdapter to insert data and obtain the ID number

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 howInsert a record and inserting ing the new id of the inserted row in return(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) = NULLASBEGINSET 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 useXml raw Mode. This mode transforms the result set into a generic identifier<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-attributeXs: intBut this gave me some problems when using the promoted value in the orchestration, everything worked fine when switching backXs: string.

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

Hehe, I also have my own solutions. The method is similar to this one. However, this method is more convenient now :)