none
Biztalk Server 2006 and SQL Server 2005.. RRS feed

  • Question

  •  

    HI,..

    I have a problem...i have developed solution in Biztalk 2006 where i am taking data from SQL tables of individual employees and seggregating xml in Orchestration using Envelope and Document Schema..then sending it to SAP RFC to get vouvher number ..and updating back this voucher number back to SQL table..my query is if suppose SAP is not working due to some reason..biztalk still send xml to SAP for voucher parking..i have requirment that when voucher number is returned from SAP then only changed the status flag...in SQL..but thing is biztalk continuosly take xml from table..and send to SAP it won't wait for the one complete transaction to complete..

    If anyone can suggest what could be the solution so that the process in which ..taking data from SQL sending to SAP and stooring back SQL would be considered as one transaction..and when this transaction is sucessful then not to pick those data from SQL table...

     

    Pls reply...

    Sandeep Handa

    Saturday, June 14, 2008 1:04 PM

Answers

  • Have you set the 'Exception Object Type' for exception handler to Microsoft.XLANGs.BaseTypes.DeliveryFailureException? Can u please tell what error are u seeing? For verifying your orchestration logic you can log into event log by following command by using Expression Block.

    System.Diagnostics.EventLog.WriteEntry ("TestOrch","Inside exception handler");

    For catching fields promote properties as distinguished fields. Then you can use these variables inside orchestration.
    You may be able to do it without promotion by using Messge Construction/Transformation but I havn't verified it to be working.

    I was able to redirect the whole original input xml to another port in the exception handler.

    Thanks
    Sumeet

    Tuesday, June 24, 2008 3:18 AM

All replies

  • Saturday, June 14, 2008 2:02 PM
  •  

    Hi Sumeet,

    Thanks for your reply..i tried using singleton Orchestration...but thing is if SAP server is not working then how would i resend the same message to SAP as soon as SAP server will start working...i looked on net i am thinking if i can able to uase MSMQ adapter so that i can send messages to MSMQ and it will transmit messages to SAP if SAP is working fine..but don't have any idea how should i configure...like should i take two way port..so send messages to MSMQ and receive from queue and this will go to SAP port..

    I also have doubt on what is Destination Queue when we configure Send port...

     

    Pls Comment..

    Regards

    Sandeep Handa

    Tuesday, June 17, 2008 5:31 AM
  • I was wondering if 'RetryCount/RetryInterval' can help.

     

    Tuesday, June 17, 2008 12:38 PM
  • Hi Sumeet..

    Ya Retry interval can help..client is asking  if suppose SAP server is under maintaince of any fault occurs which may take long time say day or two then in that case will orchestration  retry for that long..

    As i have checked there is property of send port called "Delivery Notification" so if i set it to transmitted then it could be possible to catch the exception ..and i am thinking if there is exception then i will call SQL stored procedure where i will reset the condition on which i pick the xml from SQL table...so in that case that data is again ready to be send to SAP...and if there is no exception then message will flow as normal...

    I included scope shape in which i included the sent and receive SAP port and included scope to catch exception...so orchestration will wait for Acknowledgment from SAP server if it is negative it will catch the exception...now further to this i am confused..whether the original message still exist..so that i can send to SQL port and read the common field depending on which i can reset the flag...

     

    Pls comment...

    Thanks

    Sandeep

    Tuesday, June 17, 2008 1:47 PM
  • Hi Sumeet..

    I just like to ask ..you advised to look for Retry Interval Retry Count..in that case messages will go in Suspend State which i can view in Query Expression..

    1) My query is how many messages can at the max be stored in Suspend State..

    2)  Will messages expire after any interval of time..or it will retry again and again till port is working (on which there was error)

    Pls reply...

     

    Wednesday, June 18, 2008 10:44 AM
  • Since, it's a singleton orchestration there should be only one message in suspended state.  I am not sure about message expiry.

     

    I havn't tried the following approach but should work.

     

    To prevent flooding of BTS with xml messages from SQL table you can use the response of the previous operation for trigerring the next xml message as follows.

     

    Orchestration Logic:

     

    1. Pick the xml from recieve port say c:\test\SAPIN\

    2. Get the response and send to say c:\test\SAPOUT folder.

    3. Pick the response from c:\test\SAPOUT folder

    4. Get the next message from SQL table and send to c:\test\SAPIN\

     

    You will need a booting xml in c:\test\SAPIN after which the orchestration will sustain itself. In this method there will be only one message in process at all times.

     

    Thanks

    Sumeet

     

    Thursday, June 19, 2008 2:37 AM
  • The number of suspended messages will depend upon the biztalk database throttling parameters. These are configurable.

     

    http://msdn.microsoft.com/en-us/library/aa559628.aspx

     

     

    Thursday, June 19, 2008 4:13 AM
  •  

    Hi Sumeet,

    As you have said i am using retry count and interval in send port..but not using as singleton orchestration..

    1) My query is when SAP port is faulty due to some reason..the messages will go in suspend (resumable) mode..and it will retry after specified interval..as a normal procedure i should inform administrator about the fault so that SAP fault can be taken care of..i read on net that i can catch exception on Send port and send messages to client but by using retry count biztalk only will send exception when retry count is finished,,so in my case the  administartor won'tl get messages when Biztalk is retrying...and when it will send messages it will be of no use as messages will be resumed..

    Pls reply how can i catch excetion and send notification to administrator..

     

    Thanks

    Sandeep Handa.

     

     

     

     

     

     

     

     

    Thursday, June 19, 2008 1:14 PM
  • I tried adding a exception handler but the message gets suspended after the exception handler block is completed, so can't even use a loop.

     

    Saturday, June 21, 2008 2:05 PM
  • Routing for Failed Messages

    http://msdn.microsoft.com/en-us/library/aa578574.aspx

    Thanks
    Sumeet
    Saturday, June 21, 2008 6:33 PM
  • Hi Sumeet..

    Thanks a lot for your reply...

    I am thinking when SAP port is not working then i will catch exception ..send email to administrator..then using SQL adapter i will reset the Status flag in SQL tables..so that data will again be ready for voucher parking..

    but in this case i just have one query like...when i call SQL adapter ..is there is any way i can catch fields from suspended messages as i want information depending on which i will reset the status flag in SQL table..because i need to pass some value so that aganist those values i will reset status flag..

    Also ..how did you catch the exception i am trying using scope and making send port transmitted..and trying to get file in some recevie location ..but no help.. i can only see error logs in event viewer..

    pls let me know the procedure u adopted to catch exception..

     

    Regards

    Sandeep Handa

    Monday, June 23, 2008 3:24 AM
  • Have you set the 'Exception Object Type' for exception handler to Microsoft.XLANGs.BaseTypes.DeliveryFailureException? Can u please tell what error are u seeing? For verifying your orchestration logic you can log into event log by following command by using Expression Block.

    System.Diagnostics.EventLog.WriteEntry ("TestOrch","Inside exception handler");

    For catching fields promote properties as distinguished fields. Then you can use these variables inside orchestration.
    You may be able to do it without promotion by using Messge Construction/Transformation but I havn't verified it to be working.

    I was able to redirect the whole original input xml to another port in the exception handler.

    Thanks
    Sumeet

    Tuesday, June 24, 2008 3:18 AM
  • Hi Sumeet..

    I was working with exception handling...so i got success in catching exception and sending to administartor using SMTP port..but thing worries me is when there is exception the messages will go in suspended(resumable) mode..and when i try to resume it ..by right clicking the message and select resume instance it immediatley give routing failure report..

    what would cause it not to get resumed...

     

    Thanks

    Sandeep

     

    Thursday, July 10, 2008 11:26 AM