[BizTalk Server 2006] Inserting parent-child records in SQL Server using stored procedures

Last week I was working on a proof of concept regarding BizTalk Server 2006 R2. Part of the concept was writing records in a database with a parent child relationship. I knew the classic way of doing this by using an orchestration to write the parent and then a while-loop to write all the child records. But actually I’m not very fond of this solution because it is a lot of work for using some standard functionality.

In my situation I had to use the Identity of the just inserted parent record for the child records, so I Googled a bit to find the best way to do that. I came along this post, which uses update grams, but I was looking for a way with stored procedures to fix this. l also found an example here.

It is really terrible that I cannot find the In this excellent blogpost anymore where I read the solution. Bottom line is that you don’t have to create a while-loop to insert all child records. The only thing you have to do is change the ‘maxoccurs’ of node in the generated schema based on the stored procedure you’re going to use to insert the child records to ‘unbounded’. After that you are able to map all children to one message which will be processed by the SQL adapter in a batch!

I used an orchestration to write the parent record and used a multiple input-message map to map the parent ID from the parent record insert and the source child messages to the destination child messages. After some head-scratching I found out that the order of the input-messages is very important. I couldn’t get it to work if the source child message was the first source message in the map and the message with the parent ID was second. The adapter was reporting a missing parameter in the stored procedure (the parent ID). However if I specified the parent ID message first and then the source child message second it worked fine!