sql-server - 如何保持事务和ssis包执行相互等待？

Question

这是在接收服务代理消息时执行 ssis 包的简化代码示例，我需要等待所述包完成才能进一步执行。问题是:事务 TR_testSISOnMsg 没有提交，因为“while not exists”循环等待 ssis 完成，并且 ssis 执行在 SSISDB.catalog.executions 中保持“挂起”状态，因为它在等待事务提交......所以我最终有两个进程等待对方完成......交易范围的微调不是我的强项......有任何想法吗？

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

Answer 1

你可以这样等待状态，

-- execute package
EXEC [SSISDB].[catalog].[start_execution] @execution_id;

SET @status = (SELECT [Status] FROM SSISDB.[catalog].[executions]
            WHERE execution_id = @execution_id);

-- check package execution result
-- created (1), running (2), canceled (3), failed (4), pending (5), ended unexpectedly (6), succeeded (7), stopping (8), and completed (9)  
WHILE(@status = 1 OR @status = 2 OR @status = 5 OR @status= 8)
BEGIN

    WAITFOR DELAY '00:00:1'; --'waiting 1 second for Package to finish'

    SET @status = (SELECT [Status] FROM SSISDB.[catalog].[executions]
            WHERE execution_id = @execution_id);
END             

IF @status <> 7 
BEGIN   
    ...
END