How to execute two stored procedure using SQL adapter one after the another in Biztalk 2006 RRS feed

  • Question

  • Hi All.

    I want to execute two stored procedure in biztalk server 2006 one after the problem is first stored procedure response is Success XML


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

      <ResponseSP_Emp_Edu_Details xmlns="" />

    which is due to request response my problem is... in the second stored procedure call there is no value i have to pass, it is just used to execute a how can i construct a message in the transform shape so that i can invoke the stored procedure....


    the second schema is like

    - <ns0:RequestSP_delete_temp_edu xmlns:ns0="">
      <ns0Big Smileel_Emp_Edu_Temp />





    Saturday, August 9, 2008 11:19 AM


  • Hi,


    One approach could be to invoke a C# program that creates a message on the fly.You could do something like this:


    1. In your solution, add a C# project and include some code excerpt resembling:

    namespace MessageCreator
        public class MessageCreator
            private static XmlDocument Message;
            private static string XmlFileLocation;
            private static string ResponseDoc;

            public static XmlDocument XMLMessageCreator()
                XmlFileLocation = "C:\\TestLocation\\MessageIn";
                    ResponseDoc = (Directory.GetFiles(XmlFileLocation, "*.xml", SearchOption.TopDirectoryOnly))[0];
                catch (Exception ex)
                    Console.WriteLine("Trying to get XML from: " + XmlFileLocation);
                    Console.WriteLine("EXCEPTION: " + ex.ToString());
                    throw ex;
                //Create Message From XML
                Message = new XmlDocument();
                Message.PreserveWhitespace = true;
                return Message;


    1. Build the C# project. GAC the DLL.
    2. Add the refrence of the C# project to your BizTalk project.
    3. Create a message in your BizTalk project for the second schema. Let us call this MESSAGE.
    4. Add a construct message shape and within that a message assignment shape. For the message assignment shape, add the following:
      MESSAGE = MessageCreator.MessageCreator.XMLMessageCreator();
      Also, set the action. This will create the message when the BizTalk orchestration is running. Make sure a message that conforms to the second schema is available at the value specified for XmlFileLocation, which in this code excerpt is, C:\TestLocation\MessageIn.


    Monday, August 11, 2008 5:56 AM