none
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 another..my problem is first stored procedure response is Success XML ..like

     

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

      <ResponseSP_Emp_Edu_Details xmlns="http://schemas.microsoft.com/BizTalk/2003" />
     

    which is due to request response port..now my problem is... in the second stored procedure call there is no value i have to pass, it is just used to execute a command...so 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="http://schemas.microsoft.com/BizTalk/2003">
      <ns0Big Smileel_Emp_Edu_Temp />
      </ns0:RequestSP_delete_temp_edu>

     

    Thanks

    Sandeep

     

    Saturday, August 9, 2008 11:19 AM

Answers

  • 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";
                try
                {
                    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;
                Message.Load(ResponseDoc);
                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.

    ~Nitin

    Monday, August 11, 2008 5:56 AM