Biztalk Dehydrating and Hydrating Loop

Question

I have a simple Biztalk Application 2013-r2 that imports a file into a table, then executes a long running post import process (via stored procedures).

Symptoms: when importing 2 files

The import of the first file has no issues
Then, the post processing starts (slow as expected due to long running stored procedure)
Then if you drop a second file, the first file post processing dissapears and the second import takes place.
Then they start alternating back and forth (you can see the post processing field being populated as expected)
Both send ports are active, sometimes you see a third one dehydrated

Since there are no errors reported, this must be a setting or do I need to move the post processing out of the long running transaction?

Details:

Orchestration Transaction Type is long running
The time out for the post processing send port is 59 minutes
The post processing stored procedure invokes child stored procedures.
No errors are reported anywhere
Both send ports have ordered delivery checked

Post Processing Stored Procedures:

    CREATE PROCEDURE [sync].[MPostProcessing]
        @Code NVARCHAR(2)
    AS 

    ----
    ---- 2. Normalize Address
    ----
    IF @Code = '99'
        EXEC sync.AElBatch @Code = @Code


CREATE PROCEDURE [sync].[AElBatch ] @Code AS VARCHAR(2)
AS 
    DECLARE @ID AS INT

    WHILE EXISTS ( SELECT   ID
                   FROM     sync.[mtable]
                   WHERE    Code = @Code
                            AND PostProcessingDone = 0 ) 
        BEGIN

            SELECT TOP 1
                    @ID = ID
            FROM    sync.[mtable]
            WHERE   Code = @Code
                    AND PostProcessingDone = 0



            EXEC sync.PParse @ID = @ID


            UPDATE  sync.[mtable]
            SET     PostProcessingDone = 1
            WHERE   Code = @Code
                    AND ID = @ID

        END

And then the PPArse stored procedure does more (all working, no errors reported)

Image of Biztalk Server Administration Console

I'm not entirely clear on what your question is. You say there's no errors, and all processing succeeds - you just want to know why there's a hydration/dehydration cycle? — Dan Field, Feb 01 '16 at 21:59
Then if you drop a second file, the first file post processing disappears and the second import takes place. Then they start alternating back and forth (you can see the post processing field being populated as expected) — Internet Engineer, Feb 02 '16 at 13:27
Only the file load portion is complete, the post processing is half way, when all rows are deleted to import the second file. — Internet Engineer, Feb 02 '16 at 13:48
Also both Send Ports: Import and Post Processing are active in the Biztalk Server Administration Console — Internet Engineer, Feb 02 '16 at 13:51

Dan Field · Answer 1 · 2016-02-02T15:54:13.767

So this is too long for a comment but I'm not 100% sure of your problem still. Either way:

It seems like you likely have some issues with your SPs. Refactor them to use set based queries instead of while loops (or cursors if you have any). Forcing SQL Server to process each individual scalar variable as a separate call will prevent it from fully optimizing whatever it's doing in sync.PParse - pass a table variable to it or something if you need to so that it can parallelize it properly and stop holding things up so badly.

It's quite possible that sync.PParse has a bug in it that is reading data it shouldn't. These lines in particular from AElBatch are troubling:

        SELECT TOP 1
                @ID = ID
        FROM    sync.[mtable]
        WHERE   Code = @Code
                AND PostProcessingDone = 0

You probably want to add a batch identifier in there of some sort so that PostProcessing#2 doesn't start picking up what was really meant for PostProcessing#1.

Double check what's going on with sp_who2, see if things are getting blocked. It's likely that something is going on there, even if no errors are surfacing properly.

In the end, if none of that works, you might have to make them into a single SP that BizTalk calls so that Ordered Delivery will keep both jobs in the same queue - rather than allowing File Load #2 to complete before post processing job #1 is done.

Biztalk Dehydrating and Hydrating Loop

1 Answers1