How to keep transaction and ssis package execution to wait for each other?

Question

Here is a simplified code sample executing an ssis package on reception of a service broker message, which I need to wait for the completion of said package to go further. Problem is : the transaction TR_testSSISOnMsg doesn't commit, due to the "while not exists" loop waiting ssis to complete, and the ssis execution stays "pending" in SSISDB.catalog.executions because its waiting for the transaction to commit... so I end up with two processes waiting for each other to complete... the fine tuning of transactions scopes is not my forte... Any ideas ?

CREATE PROCEDURE [dbo].[testSSISOnMsg]
AS
BEGIN
    BEGIN TRANSACTION TR_testSSISOnMsg
    WHILE(1=1)
    BEGIN
        DROP TABLE IF EXISTS testLog
        CREATE TABLE testLog (IdLog UNIQUEIDENTIFIER,dateLog DATETIME,msgLog NVARCHAR(100))

        DECLARE @conv_handle UNIQUEIDENTIFIER,
                @msg_type sysname;
        WAITFOR (
            RECEIVE TOP (1)
                    @conv_handle = conversation_handle,
                    @msg_type = message_type_name
                FROM [//SQL2016-DMDI/WorkspaceR/testSSIS/File_DemandeSSIS]
        ), TIMEOUT 1000;

        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION TR_testSSISOnMsg;
            BREAK;
        END

        IF @msg_type = N'//SQL2016-DMDI/WorkspaceR/testSSIS/MsgDemandeExec'
        BEGIN
            DECLARE @execution_id bigint,@status int;
            DECLARE @package_name NVARCHAR(100) = N'testPackage.dtsx';

            EXEC SSISDB.catalog.create_execution_VCH
                @folder_name = N'DataScience',
                @project_name = N'ScoresIndexation',
                @package_name = @package_name,
                @execution_id = @execution_id output;

            EXEC SSISDB.catalog.start_execution_VCH @execution_id;

            WHILE NOT EXISTS (SELECT 1 FROM SSISDB.catalog.executions where execution_id = @execution_id and end_time IS NOT NULL)
            BEGIN
                INSERT INTO testLog VALUES(NEWID(),GETDATE(),'waiting')
                WAITFOR DELAY '00:00:30';
            END
        END
        ELSE BEGIN
            END CONVERSATION @conv_handle;
        END
        COMMIT TRANSACTION TR_testSSISOnMsg;
    END
END
GO

user_155892 · Answer

This can't work as is (holding the transaction open until SSIS finishes) -- running the SSIS execution happens through a separate external process/connection -- if you don't commit your transaction that creates and starts the execution, the other connection won't be able to see it in order to actually run the execution.

We wanted to do the same thing...what we are going to do for this instead is start the execution asynchronously and commit the transaction, and at the end of the SSIS package send a service broker message saying it's done. In the activation procedure we use that message to update the status based on the success or failure of the execution. Note that you can't use SYNCHRONIZED for the same reason -- we wouldn't be able to commit the transaction until the execution is finished, and the execution can't begin actually running until the transaction that kicked it off has committed.

(Note that it still won't be 100% bulletproof because if the SSIS execution starts but is then aborted/server powers off, the process will be stalled because the execution didn't finish.)

How to keep transaction and ssis package execution to wait for each other?

3 Answers