BizTalk: Handling SQL Adapter Exceptions to resume suspended orchestrations

Microsoft.XLANGs.BaseTypes.DeliveryFailureException

You might come across a need to catch SQL Adapter exceptions such as "Unable to connect to database" or "Invalid Stored Proc" when calling Stored procs from BizTalk using SQL Adapter. You can do the following to be able to suspend the orchestration and resume from the administration console when the connection is fixed (after retry count) or stored proc name is fixed in the database.

1. Create a Receive shape to receive the Insert Record File
2. Mark the orchestration as Long Running
3. Add a Scope shape and mark it as Long Running
4. Add Send Shape to the Scope
5. Add Exception Handler block for the Scope (right click Scope and select New Exception Handler)
6. Catch Microsoft.XLANGs.BaseTypes.DeliveryFailureException (name the Exception object to DeliveryFailureException and type as the Microsoft.XLANGs.BaseTypes.DeliveryFailureException, make sure you have reference to Microsoft.XLANGs.BaseTypes)
7. Add Suspend Shape to the Exception block and set the Error message
8. Configure a Send Port (One-way) TransactionType to Transmitted
9. Deploy and create physical ports in BizTalk Admin Console

Gotchas:

• Orhcestration has to be long running
• Above instructions work for one-way Send Ports (to catch DeliveryFailureException)
• Above instructions work for two-way solicit send port but catch SOAPException instead of DeliveryFailureException as request-response port throws SOAP Exception
• Mark Send Port Transaction Type as Transmitted
• The example orchestration provided here works when you would like to resume just the SendPort that threw the exception after fixing the DB issue
• If you would like to resume your orchestration from the Send shape that threw the error then you need to have a Loop around the scope and loop until the error is fixed. Suspend shape is a persistence point too so if you don’t loop after resume the orchestration would start from the Suspend shape instead of the Send Shape that threw the error.