BizTalk send/receive - does it wait for completion of a called stored procedure?

Question

I've setup a BizTalk design that chains a couple of send/receives to a SQL stored procedure (which inserts the data to relevant tables). It's organised in a specific sequence, so data goes into Table A, and following tables after this check that the data exists in Table A at the Stored Procedure level (simple (IF EXISTS in Table A setup...).

I've noticed though that the flow isn't consistent further down the chain, almost as if SQL is executing the stored procedure to insert/update the record slower than the BizTalk transaction is occurring. I've made sure that my Biz design is send/receive, as I assumed the transaction wouldn't progress until Biz received a response from the stored procedure (which would indicate SQL has finished inserting the required data).

The below example highlights where the process writes data to the Person table, but is later called upon by the Student Programme/Student Module. Occasionally, it will dehydrate on the Programme or Module stored procedure (from what I can tell, because the stored procedures are looking to see if a Person record created at the start of the flow exists)

Can anyone please confirm if;

• Send/Receive will wait for a SQL stored procedure to finish executing before progressing the BizTalk transaction further through the orchestration?

Answer 1

BizTalk Orchestrations have some smarts built into it, if there are no dependencies in the next shapes on the response, then no, it might not wait for the response to execute the next shapes. What you can try is to enable the Delivery Notification to Transmitted on the Logical Send Port settings.