none
how can i get synchronous behavior between my proc and an ssis pkg? RRS feed

  • Question

  • Hi we run 2017 std. I want the proc you see below to wait for catalog based ssis pkg runs to finish. It isn't waiting in its current state.  I'm pretty sure I proved this by putting a delay in the pkg and running the commands you see in the 2nd image.

    The reason I want this has to do with a scheduling package I'm trying out. If i'm not mistaken, in the good old days "callers" waited on file system based  pkgs to finish.  Does anybody know how to make this synchronous if the pkg is in the catalog?

    create PROCEDURE [dbo].[runssispackage]
    	
    	@folder varchar(1000),
    	@project varchar(1000),
    	@package varchar(1000),
    	@output_execution_id bigint output
    AS
    BEGIN
    	
    	SET NOCOUNT ON;
    	declare @execution_id bigint
        exec ssisdb.catalog.create_execution 
             @folder 
            ,@project 
            ,@package 
            ,@execution_id = @execution_id output
        exec ssisdb.catalog.start_execution @execution_id
        set @output_execution_id = @execution_id
    
    END
    GO
    declare @output_execution_id bigint 
    exec dbo.runssispackage 
    'x', 
    'y', 
    'z.dtsx', @output_execution_id output


     

    Tuesday, November 19, 2019 7:51 PM

All replies