Earlier this week, I had to call a stored procedure from a simple BizTalk 2016 applicaition. The stored procedure expected to receive 7 parameters, each of which would be available in the BizTalk message.
ALTER PROCEDURE [dbo].[UpsertCarInventory] @year int, @make varchar(40), @model varchar(40), @color varchar(10), @mileage int, @inLot bit, @regPlate varchar(2)
It's been a while since I've had to do this so I quickly googled for the best approach. All the results suggested that I need an orchestration to call the stored proc. This seemed a little heavyweight for my solution, so I figured out how to make it work messaging only.
First I right-clicked my BizTalk schemas project and selected Add\Generated Items\Add Adapter Metadata. This launched the "Add Adapter Wizard". On the "Select Adapter" page, I chose "WCF-SQL" and selected the database hosting the stored proc. I then expanded the branch for "Strongly-Types Procedures" from the tree view and selected to "Add" the name of my "UpsertCarInventory" stored proc, as shown below:
The wizard then created a schema for me to map to before sending the request to Sql Server. In this case, a simple map was required, as shown below:
In the BizTalk admin console, I then created a two-way send port where I gave the Action as "Procedure/dbo/UpsertCarInventory" - as shown below:
On running a test, I found that the stored proc was being called ok from BizTalk but only data from a single row of the source data was being passed. I solved this by updating the schema of the source data. This was actually being selected from a different SQL Server database and is shown below:
I selected the "Schema" and chose "Yes" from the properties window for "Envelope". I then selected the SelectResponse node and clicked the ellipses for the "Body XPath" property. From here, I drilled down to the SelectResponse\SelectResult node. This meant that on receipt of source data such as the one shown below, it would be debatched at each CarInventory node: