Using template message body on WCF-SQL adapter in Biztalk to insert promoted properties into SQL database

Question

I need to insert some data from an XML document into an SQL table using a stored procedure. This should be done using the WCF-SQL adapter on a send port in Biztalk. I've set up the connection properties on the adapter, but I am missing the message part.

I'm currently using the below code with some hardcoded values inside a body template:

<SaveDocument xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
   <conversationID>123456</conversationID>
  <dataType>OIO</dataType>
  <fromID></fromID>
  <toID></toID>
  <msgInfoExtension><![CDATA[<infoExt><fileInfo fileName="ublinvoice.xml" encoding="utf-8" /></infoExt>]]></msgInfoExtension>
  <msgBody>
  <bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/>
  </msgBody>
  <msgBodyBin></msgBodyBin>
</SaveDocument>

Instead of the hardcoded values, I need to grab some of the promoted properties in an XML document, but I'm unsure how to correctly go about doing this.

In the MS documentation I found this page, which describes the structure of the request: https://docs.microsoft.com/en-us/biztalk/adapters-and-accelerators/adapter-sql/message-schemas-for-procedures-and-functions

Dijkgraaf Dijkgraaf · Accepted Answer · 2018-11-15T17:51:15

Distinguish the properties that you want to set in the SaveDocument schema
Create a map that populates the fields, the ones you want to set from the Promoted properties, just fill them with some constants from the map.
In an Orchestration have a Construct Message that contains both the map shape pointing to the above map and a message assignment shape.
In the message assignment shape have some statements like saveSG.conversationID = sourceMSG(PropmotedProperty).

P.S. I think you have manually tried to create the SaveDocument schema. This is probably not correct. The correct way is to create the stored procedure and then use the Generate Instance dialogue in Visual Studio, and to configure that to auto generate the schema for you, and as a bonus, it even generates a binding file containing the settings you need for your send port.

Using template message body on WCF-SQL adapter in Biztalk to insert promoted properties into SQL database

2 Answers