BizTalk SQL Receive adapter – Envelope Debatching in pipeline


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:http://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:http://SimpleDebatching (i.e. remove the EmpDetails part: http://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:  http://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!

Comments (39)

  1. Actually, you shouldn’t create the new "body" schema (EmpDetails.xsd" by hand, but rather use XSD Import to reuse the elements created by the SQL Adapter schema.  See my post from earlier this year on this topic … http://seroter.wordpress.com/2007/01/03/debatching-inbound-messages-from-biztalk-sql-adapter/.

  2. rahulgarg1 says:

    Totally agree with Richard’s comments!

  3. Hi,

    How do you control your scheduling? Is everything stored with sendflag = 1 and then you set those you’d like to include in the batch to sendflag = 0 as they then will be picked up by the adapter?

    Do you schedule a job for setting this sendflag then or do use another technique?

  4. rahulgarg1 says:

    Hi Richard,

    What I did in this PoC was quite elementary – configured a scheduler (on the Receive Location in the BizTalk admin console), to work at a scheduled time at night where it would automatically execute the stored proc. This stored proc would pickup all the rows that had sendflag = 0 (by default I had the value set the value to 0 when a new row was created). As you can see in the stored procedure that the sendflag would automatically get updated to 1 as soon as the rows are picked up so the next time around these would not be picked again:

    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

    If you are interested in more complicated scheduling, I’d recommend you to watch the Live Meeting:

    https://www.livemeeting.com/cc/microsoft/join?id=BTSBAG&role=attend&pw=35DKTQ

  5. chetan says:

    Tere is small correction in this line

    Expand the schema and click on the Employee node and set the ‘Body XPath’ property to select the ‘EmployeeDetails’ node

    Expand the schema and click on the EmployeeDetails node and set the ‘Body XPath’ property to select the ‘EmployeeDetails’ node

  6. rahulgarg1 says:

    Thanks! Just corrected the schema name.

  7. Sandeep says:

    Hi…i just have one query is..you said to keep the namespace of SQLService.xsd and EmpDetails.xsd same..woundn’t that create and warning in Biztalk of Invalid IDoc,..as i tried and its giving the warning message of Invalid IDoc..

    Pls Comment

    Thanks

    Sandeep

  8. rahulgarg1 says:

    Sandeep,

    It would not – when you create a new schema e.g. EmpDetails.xsd – it would by default create the entire namespace "http://SimpleDebatching.EmpDetails" for you (which is different to the original SQLService namespace). Then you simple remove the EmpDetails part so are basically tricking BizTalk into sending it to the debatched components. Follow this sequence of steps and you’ll be fine mate.

  9. Andreas says:

    I know this post is a bit older but I run in a problem by debatching messages from SQL Adapter. I’ve done it exactly as you describet above, but all records are still in one xml in my output file. When I understand you rigt, I should get a single xml file for each record?

    Maybe you see this and give me hint where I can search for errors…

  10. rahulgarg1 says:

    Hi Andreas,

    Check 2 things:

    1) Make sure the SQL adapter has the XMLTransmit & XMLReceive pipelines (not pass through)

    2) Make sure your new schema of the debatched component has the following Namespace: http://SimpleDebatching (i.e. remove the EmpDetails part) – this Namespace is should be exactly the same as the Target Namespace of the original message.

    Let me know how you go..

  11. rahulgarg1 says:

    Also check if the SQLService.xsd schema has the ‘Envelop’ property to ‘Yes’ (Envelope is the node property of all schemas and has to be set to ‘Yes’ as byy default it is set to ‘No’)

  12. Saurabh Seth says:

    hi,

    I am able to split when my input is an xml file but when i try tdo do it from the sp i get the output in a single xml file.

    <?xml version=”1.0″ encoding=”utf-16″?><EMPLOYEE xmlns=”‘>http://SimpleDebatching”><EMPLOYEE><EMPID>172432</EMPID><EMPNAME>SAURABH”>‘>http://SimpleDebatching”><EMPLOYEE><EMPID>172432</EMPID><EMPNAME>SAURABH SETH</EMPNAME></EMPLOYEE><EMPLOYEE><EMPID>183602</EMPID><EMPNAME>AHMED MUZIB</EMPNAME></EMPLOYEE><EMPLOYEE><EMPID>118955</EMPID><EMPNAME>SANGEETHA</EMPNAME></EMPLOYEE></EMPLOYEE>

    This xml is coming from the adaptor.

    <ns0:EmployeeDetails xmlns:ns0=”‘>http://SimpleDebatching”>

     <ns0:EMPLOYEE>

       <ns0:EMPID>EMPID_0</ns0:EMPID>

       <ns0:EMPNAME>EMPNAME_0</ns0:EMPNAME>

     </ns0:EMPLOYEE>

    <ns0:EMPLOYEE>

       <ns0:EMPID>EMPID_0</ns0:EMPID>

       <ns0:EMPNAME>EMPNAME_0</ns0:EMPNAME>

     </ns0:EMPLOYEE>

    <ns0:EMPLOYEE>

       <ns0:EMPID>EMPID_0</ns0:EMPID>

       <ns0:EMPNAME>EMPNAME_0</ns0:EMPNAME>

     </ns0:EMPLOYEE>

    </ns0:EmployeeDetails>

    This kind of xml is getting debatched.

    plz help me on this.

  13. rahulgarg1 says:

    Hi, Check the pipeline on your receive location – it should be XML Receive and not pass through.

  14. Saurabh Seth says:

    I checked, the pipeline is xml receive. Still no luck. I can send you my vs solution if you want to.

  15. Saurabh Seth says:

    I am using BTS 2006 and SQL server 2005. Does that make a difference?

  16. rahulgarg1 says:

    That would not make a difference

  17. Saurabh Seth says:

    The kind of xml that is genertaed by  the SP is

    <EMPLOYEE>

     <EMPID>172432</EMPID>

     <EMPNAME>SAURABH SETH</EMPNAME>

    </EMPLOYEE>

    <EMPLOYEE>

     <EMPID>183602</EMPID>

     <EMPNAME>AHMED MUZIB</EMPNAME>

    </EMPLOYEE>

    <EMPLOYEE>

     <EMPID>118955</EMPID>

     <EMPNAME>SANGEETHA</EMPNAME>

    </EMPLOYEE>

    it doesnt have any namespace related entries. Should i change the SP to incorporate simillar namespace like the one present in the SQLService.xsd instance

    <ns0:EmployeeDetails xmlns:ns0="http://SimpleDebatching"&gt;

     <ns0:EMPLOYEE>

       <ns0:EMPID>EMPID_0</ns0:EMPID>

       <ns0:EMPNAME>EMPNAME_0</ns0:EMPNAME>

     </ns0:EMPLOYEE>

    </ns0:EmployeeDetails>

  18. Saurabh Seth says:

    hi,

    after a lot of struggle i am able to debatch but i am getting double outputs say for 3 rows read from SP i get 6 dissambled messages in my send port. Any idea why may this be happening.

    I need to process several thousand rows daily this will be a big bottleneck for me.

  19. rahulgarg1 says:

    Check if you are reading the rows twice?

  20. rahulgarg1 says:

    The best way to ensure that you are not reading the records twice is in the SP where you read the record, flag it as read by updating a counter

  21. BizUser says:

    Steps I did :

     <SCHEMA>

       <PARENT>

          <CHILD>

    1. Created Schema from stored procedure (with XML AUTO, ELEMENT, XMLDATA)

    2. ENVELOPE = YES

    3. BODY XPATH : PARENT(BELOW <SCHEMA>)

    4. When Generating Schema used Name space as http://TEST.Schemas

    5. Group Max Occurs =1

    6. Max Occurs = 1

    7. Created a New Schema

    8. Renamed the Root to CHILD

    9. Imports and Select the Envelope Schema

    10.Build and deployed the project.

    11. Configured Receiveport with

        RootName  : PARENT

        DocumentTargetNameSpace= http://TEST.Schemas

        SQLCoomand :exec[SpName]

    12. Receive Pipeline is XML Receive

    13. Configured Send port

    14. Removed XMLDATA from SP

    I am not getting the messages debatched but throwing error

    Event Type: Error

    Event Source: BizTalk Server 2006

    Event Category: BizTalk Server 2006

    Event ID: 5719

    Date: 7/8/2009

    Time: 10:10:42 AM

    User: N/A

    Computer: HON11062

    Description:

    There was a failure executing the receive pipeline: “Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35” Source: “XML disassembler” Receive Port: “ReceivePort3” URI: “SQL://XXXxxx/Test” Reason: Finding the document specification by message type “http://Test.Schemas#Child“ failed. Verify the schema deployed properly.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

  22. rahulgarg1 says:

    Your error message says that your XML receive pipeline is still trying to find the Child message? I’d suggest just follow the simple steps outlined here to actually get a debatching happening. Once you see it in action, you’ll easily figure out where its going south.

  23. bizuser1 says:

    I am able to debatch the message but when I am unable to validate the debatched message with  Schema(not envelope).

    My task is to transform the debatched message to another input message to SQL.

    I tried to enable them as distinguished and also used Xpath. But no luck. Can you let me know where I am going wrong.

    When I generate schema with no elements i was able to make them as distinguished but when added elemnts could not promote them.

    Thanks,

    BizUser

  24. rahulgarg1 says:

    Why do you need to validate it? It will be validated automatically against the XML schema of the debatched messages. I’d say instead of promoting, just use the mapper to map it.

  25. BizUser2 says:

    Hi,

     I need to debatch a record after sending a request to database.Means in my sp querry i am sending a request and getting a record as response.I wana tyo debatch the record .I followed your above procedure that doesn’t help me in debatching for this scenario.I am getting whole records as a response.Could tell me why i am getting this type of mesage

    Thanks

    BizUser2

  26. rahulgarg1 says:

    Hi, I’d check three things: Check if the Envelop property is set to true, body xpath is set on the schema below which you want the debatching to happen & receive pipeline is xml receive. It should definitely debatch.

  27. AJ says:

    I am trying to use this method in an Orchestration. Are there considerations for doing so?

    My paricular pattern is that I am accepting an CSV file as batched input, sending insert transactions to SQL via a SQL Adapter request/response port, then wanting to debatch the response from SQL.

    I was able to successfuly test the Example provided in this post – but that is outside of an Orchestration.

    I am receiving error, “Inner exception: Received unexpected message type http://…. does not match expected type http:…

    Where the first TargetNameSpace is the “EmployeeDetails” from your exampe, and the second TargetNameSpace is the SQLResponseRoot in my generated SQL Adapter schema.

    So my question again is, “Can this method of debatching be used from within an Orchestration using a SQL request/response port, and if so, what other considerations are there?

  28. rahulgarg1 says:

    Hi, sorry I was busy with TechEds in Australia/NZ – I havent tried this through orchestration but I’m sure we can do this. All that we need to do is give the target the same namespace as the debatched components – this will trick the BizTalk into creating the response of debatched components – I’ll try that and let you know..

  29. rahulgarg1 says:

    Folks, quite a few of you have asked what happens with WCF adapaters. Well it remains exactly the same -the debatching works perfectly fine with WCF adapters as well.

    Also, one of my mates recently confirmed that this debatching is also working for him for the Oracle adapters!

    If you are receiving one long xml and you feel something is not working – double check the "BodyXPath" node – this is where it chops off so try and bring it one level down/up and this will give you a deeper understanding of how the debatching works.

  30. Dave says:

    I followed your directions…however I am getting only the first debatched message (only one output file..out of 5 records in the envelope message)..any thoughts on what I must be doing wrong????

  31. Joon says:

    Hi All,

    I want to use Orchestration to debatch the SQL records from SQL receive adapter as I have(must) use mapping to the detached (single) records out of the Batched file.

    Then I have to send it to MSC RM 4.0 system(for this i require debatching as well mapping inside same orchesration).

    Hope you all got my query. I am trying me level best, tries all possible ways I can do with. Reuire help from BizTalk community.

    Regards

    Joon

  32. bhagiratha1984 says:

    Hi All,

    I want to use Orchestration to debatch the SQL records from SQL receive adapter as I have(must) use mapping to the detached (single) records out of the Batched file.

    Then I have to send it to MSC RM 4.0 system(for this i require debatching as well mapping inside same orchesration).

    Hope you all got my query. I am trying me level best, tried all possible ways I can do with. Require help and suggestion.

    Regards

    Joon

  33. Salam says:

    Wonderfull and usefull article

  34. Will Skou says:

    I believe I've followed all your instructions and yet the XML Pipeline will still not debatch.

    I'm using a simple stored proc returning 2 rows and 2 fields.

    My project consists of 2 schemas as follows.

    <?xml version="1.0" encoding="utf-16" ?>

    – <xs:schema xmlns:b="schemas.microsoft.com/…/2003" xmlns="http://GetBOLs&quot; attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://GetBOLs&quot; version="1.0" xmlns:xs="http://www.w3.org/…/XMLSchema"&gt;

    – <xs:annotation>

    – <xs:appinfo>

     <msbtssql:sqlScript value="exec [up_DFAP_AddBOL_BT]" xmlns:msbtssql="schemas.microsoft.com/…/2003" />

     <schemaInfo is_envelope="yes" xmlns="schemas.microsoft.com/…/2003" />

     </xs:appinfo>

     </xs:annotation>

    – <xs:element name="GetBOLs">

    – <xs:annotation>

    – <xs:appinfo>

     <recordInfo body_xpath="/*[local-name()='GetBOLs' and namespace-uri()='http://GetBOLs'%5D&quot; xmlns="schemas.microsoft.com/…/2003" />

     </xs:appinfo>

     </xs:annotation>

    – <xs:complexType>

    – <xs:sequence>

     <xs:element xmlns:q1="http://GetBOLs&quot; minOccurs="0" maxOccurs="1" name="A" type="q1:AType" />

     </xs:sequence>

     </xs:complexType>

     </xs:element>

    – <xs:complexType name="AType">

     <xs:attribute name="SiteId" type="xs:string" />

     <xs:attribute name="ControlNumber" type="xs:string" />

     </xs:complexType>

     </xs:schema>

    and

    <?xml version="1.0" encoding="utf-16" ?>

    – <xs:schema xmlns:b="schemas.microsoft.com/…/2003" xmlns="http://GetBOLs&quot; targetNamespace="http://GetBOLs&quot; xmlns:xs="http://www.w3.org/…/XMLSchema"&gt;

    – <xs:element name="A">

    – <xs:complexType>

    – <xs:sequence>

     <xs:element name="SiteId" type="xs:string" />

     <xs:element name="ControlNumber" type="xs:string" />

     </xs:sequence>

     </xs:complexType>

     </xs:element>

     </xs:schema>

    Any and all help would be appreciated.

    Thanks

  35. rahulgarg1 says:

    Chck your body xpath node – this is where it actually gets debatched – it could be an off-by-one error – try moving it one above & below. Also check your Receive Pipeline – should be XMLReceive

  36. Will Skou says:

    Thanks for the help, I'm not sure exactly what I had done wrong but after a cooling off period overnight everything worked smoothly and effortlessly this morning.

  37. rahulgarg1 says:

    🙂 sometimes, the debatched components get stuffed up with the original namespace – restarting the host is always recommended. Thanks for your feedback!

  38. Luiz says:

    I was able to follow you example an it is realy simple to implement. Then I am trying to use in this my project with VS 2008 SQL Adapter Wizad returns "Failed to execute SQL Statement. Please ensure that the supplied syntax is correct.” when I select store procedure. I even try to with very simple store proc SELECT ‘Things to return’ FOR XML AUTO, XMLDATA; and no success. Any idea what is wrong?

  39. rahulgarg1 says:

    Are you using the new SQL adapter to do this because when I wrote this, we were still using the old SQL adapter?

Skip to main content