none
Implementing Transactions in Orchestration - SQLBinding RRS feed

  • Question

  • Hi All,

    I have a requirement to insert data into multiple tables (one after the other) using SQL binding.

    On event of any error/failure in the middle of the processing of any table insert, I need to rollback the previous inserts.

    In order to do that, can anyone please suggest me the approach on implementing the transactions for the processing.

    Thanks in advance!!


    Thanks & Regards, Sridhar

    Thursday, February 15, 2018 12:36 PM

All replies

  • What you need to use are Composite Operations with the WCF SQL binding.

    Run composite operations on SQL Server using BizTalk Server

    Basically, this combines multiple operations into a single message which can all run as a transaction.

    Thursday, February 15, 2018 12:42 PM
  • Thanks for the response.

    I am trying to create a composite schema for multiple table inserts.

    While adding the second table insert schema, its throwing the below error.

    This operation could not be completed: 
    1. The global element 'http://schemas.microsoft.com/2003/10/Serialization/Arrays:ArrayOflong' has already been declared. 
    2. The complexType 'http://schemas.microsoft.com/2003/10/Serialization/Arrays:ArrayOflong' has already been declared.




    Thanks & Regards, Sridhar

    Thursday, February 15, 2018 1:01 PM
  • Yes, that's because you need to reference the types schema only once in the top level schema on the Import/Includes list.

    It's often easier to manually edit the .xsd files since the Editor is particularly fussy about this.  This is basic schema editing, nothing specific to BizTalk, the sqlBinding or anything.

    Thursday, February 15, 2018 1:15 PM
  • Hi,

    I created a composite schema and added the insert table operations.

    I deployed the application and I created a send port with WCF-Custom transport type with SQLbinding and added the action as "CompositeOperation".

    The process failed with the below error:

    "There was a failure executing the response(receive) pipeline: "Microsoft.BizTalk.DefaultPipelines.XMLReceive, Microsoft.BizTalk.DefaultPipelines, Version=3.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" Source: "XML disassembler" Send Port: "SendPort1" URI: "mssql://ki-laptop58//ars_processing_dev?" Reason: Finding the document specification by message type "http://Integration.CompositeSchema#RequestResponse" failed. Verify the schema deployed properly. "

    I see the CompositeSchema added. 


    Thanks & Regards, Sridhar

    Thursday, February 15, 2018 4:04 PM
  • This is just basic BizTalk, nothing to do with Composite Operations.

    On a Two Way Port, you also need to have the Response schema deployed.

    Reason: Finding the document specification by message type "http://Integration.CompositeSchema#RequestResponse" failed. Verify the schema deployed properly. "

    Thursday, February 15, 2018 5:41 PM