BizTalk SQL Receive adapter - Envelope Debatching in pipeline

  • Article

Last week, whilst doing a proof-of-concept for a customer in Melbourne, I was asked to demo a scheduled batch load from a BizTalk SQL Receive Adapter where the required rows (based on certain flags) are picked up at a scheduled time and then debatched so that each row is sent individually to another instance of the DB (mind you, if you do not debatch, the default behaviour is to send one long xml with mutiple rows of records). The scheduling requirement was pretty straight forward (if you are interested in more complicated scheduling, please watch the following Live Meeting on BizTalk Task Scheduling here) but the debatching was slightly tricky (if you are planning to do it in the pipeline itself and not in the orchestration using code) so let me reconstruct the steps that I did to simplify this process for anyone else interested in a similar debatching (table/column/project names have been changed for privacy reasons). There is only one thing I must emphasize, and that is the BizTalk SQL Receive adapter does the debatching in the XML Receive pipeline for you automatically - your effort lies in only having to capture the debatched messages - that is the defining moment!

  • Create a new Biztalk project and call it 'SimpleDebatching' and setup the strong name in the assembly and call the Application name 'SimpleDebatching' as well (in the Deployment Configuration)

  • Create a DB on the SQL Server and lets call it 'HRMS' and lets create an 'EMPLOYEE' table with columns 'EMPID' varchar[10] , EMPNAME varchar[50] & SENDFLAG [int]

  • Enter some 4-5 rows into the table and set the SENDFLAG = 0 for each one of them

  • Create a simple stored proc:

    CREATE PROC [dbo].[POLL_EMPLOYEE] AS SELECT LTRIM(RTRIM(EMPID)) AS EMPID, LTRIM(RTRIM(EMPNAME)) AS EMPNAME FROM EMPLOYEE WHERE SENDFLAG = 0 for xml auto, elements, xmldata

    UPDATE EMPLOYEE SET SENDFLAG = 1

  • Right click the project Add -> Add Generated Items -> Add Adapter Metadata and click on Add

  • Click on SQL -> Next and click on Set and select your SQL Server name, login details & 'HRMS' as the DB and click on Test Connection to confirm the connection.

  • Target namespace:https://SimpleDebatching  Select Receive port & type 'EmployeeDetails' as the Document Root element name and click on Next

  • Click on Stored Procedure and select 'POLL_EMPLOYEE' from the drop down list and click on 'Generate' then click on 'Next' and 'Finish'

  • Go back to the Stored Procedure and comment out the xmldata portion e.g:  elements--, xmldata

  • Delete the Orchestration created by the wizard as we would not need it for this example

  • Double click on the SQLService.xsd schema and set the 'Envelope' property to 'Yes'  (Envelope is the node property of all schemas and setting it to "yes" sets the is_envelope attribute to "yes", specifying that the selected schema represents an envelope. By default it is set to "No")

  • Expand the schema and click on the EmployeeDetails node and set the 'Body XPath' property to select the 'EmployeeDetails' node (this identifies the portion of the schema that defines the body of the message associated. The message would be then debatched at this node and everything below this node will be chopped off - only works if the Envelope in the previous step is set to "Yes"). 

  • Expand the schema and click on the node, 'EMPLOYEE' set the property Max Occurs to 1 (Max Occurs property is used to configure the maximum number of times that the element or elements corresponding to the selected node occur in the scope in an instance message which in our case is one)

  • Create a new schema (please check Richard's blog here if you do not wish to create the new Schema from the ground up) which maps to the debatched component (right click the project Add -> New Item -> Schema Files) and call it EmpDetails.xsd

  • Set the Target Namespace:https://SimpleDebatching  (i.e. remove the EmpDetails part: https://SimpleDebatching.EmpDetails - this Namespace is now exactly the same as the Target Namespace of the original message and hence will pickup the debatched components)

  • Rename the Root node to EMPLOYEE (this must match exactly the debatched component of the SQLService.xsd schema)

  • Right Click EMPLOYEE -> Insert Schema Node -> Child Field Element -> EMPID (leave the type as default i.e. xs:string. Note - this must match exactly the debatched component of the SQLService.xsd schema)

  • Right Click EMPLOYEE -> Insert Schema Node -> Child Field Element -> EMPNAME (leave the type as default i.e. xs:string. Note - this must match exactly the debatched component of the SQLService.xsd schema)

  • Save All and right click the project and click 'Deploy' (will build your Application too)

  • On the Admin Console, go the the SimpleDebatching Application and right Click Receive Port -> New One Way Receive Port and call it RP_Emp

  • Right Click Receive Location -> New One Way Receive Location and choose RP_Emp and click OK and choose SQL as the type and Receive pipeline is XML Receive and then hit Configure

  • Click on Connection string and select your SQL Server name, login details & 'HRMS' as the DB and click on Test Connection to confirm the connection.

  • Document Root Element Name: EmployeeDetails

  • Document Target namespace:  https://SimpleDebatching

  • In the SQL Command select the project name: SimpleDebatching and select the SQLService in the schema

  • Click OK in the Config screen and OK in the Receive Location screen

  • Right click send port -> New -> Static one-way Select the 'FILE' type and click Configure and select an output directory

  • Click on Filters and choose the Property BTS.ReceivePortName == RP_Emp and click OK

  • Right click the SimpleDebatching Application and Start - checkout the Output directory

Happy to help if you face any issues. Alternatively, you can use code involving XPath in Orchestration to debatch a message by looping around (I've done that too if anyone is interested) but this is far more elegant!

NOTE: If you do not want to create a new schema from scratch but instead use your existing schema and just use the import option - have a look at Richard Seroter’s Architecture Musings here. That will save you the effort of having to do the next few steps. Strongly recommended!