Udostępnij za pośrednictwem

BizTalk SQL Receive adapter - Envelope Debatching in pipeline

  • Artykuł

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!

Comments

  • Anonymous
    November 12, 2007
    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/.

  • Anonymous
    November 12, 2007
    The comment has been removed

  • Anonymous
    November 14, 2007
    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?

  • Anonymous
    November 15, 2007
    The comment has been removed

  • Anonymous
    January 07, 2008
    The comment has been removed

  • Anonymous
    January 09, 2008
    Thanks! Just corrected the schema name.

  • Anonymous
    June 04, 2008
    The comment has been removed

  • Anonymous
    June 07, 2008
    The comment has been removed

  • Anonymous
    April 22, 2009
    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...

  • Anonymous
    April 22, 2009
    The comment has been removed

  • Anonymous
    April 22, 2009
    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')

  • Anonymous
    June 29, 2009
    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.

  • Anonymous
    June 29, 2009
    Hi, Check the pipeline on your receive location - it should be XML Receive and not pass through.

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

  • Anonymous
    June 29, 2009
    I am using BTS 2006 and SQL server 2005. Does that make a difference?

  • Anonymous
    June 29, 2009
    That would not make a difference

  • Anonymous
    June 29, 2009
    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">  <ns0:EMPLOYEE>    <ns0:EMPID>EMPID_0</ns0:EMPID>    <ns0:EMPNAME>EMPNAME_0</ns0:EMPNAME>  </ns0:EMPLOYEE> </ns0:EmployeeDetails>

  • Anonymous
    June 30, 2009
    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.

  • Anonymous
    June 30, 2009
    Check if you are reading the rows twice?

  • Anonymous
    June 30, 2009
    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

  • Anonymous
    July 08, 2009
    The comment has been removed

  • Anonymous
    July 09, 2009
    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.

  • Anonymous
    July 15, 2009
    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

  • Anonymous
    July 16, 2009
    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.

  • Anonymous
    July 24, 2009
    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

  • Anonymous
    July 25, 2009
    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.

  • Anonymous
    September 14, 2009
    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?

  • Anonymous
    September 20, 2009
    The comment has been removed

  • Anonymous
    October 04, 2009
    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.

  • Anonymous
    December 03, 2009
    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????

  • Anonymous
    December 29, 2009
    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

  • Anonymous
    December 29, 2009
    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

  • Anonymous
    January 11, 2010
    Wonderfull and usefull article

  • Anonymous
    August 16, 2010
    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" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://GetBOLs" version="1.0" xmlns:xs="www.w3.org/.../XMLSchema">
  • <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']" xmlns="schemas.microsoft.com/.../2003" />  </xs:appinfo>  </xs:annotation>
  • <xs:complexType>
  • <xs:sequence>  <xs:element xmlns:q1="http://GetBOLs" 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" targetNamespace="http://GetBOLs" xmlns:xs="www.w3.org/.../XMLSchema">
  • <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

  • Anonymous
    August 16, 2010
    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

  • Anonymous
    August 17, 2010
    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.

  • Anonymous
    August 17, 2010
    The comment has been removed

  • Anonymous
    September 26, 2010
    The comment has been removed

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