title: How to keep transaction and ssis package execution to wait for each other?
body: <p>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 ?</p>
  
  <pre><code>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
  </code></pre>