Sql-server – Running SSIS jobs sequentially in T-SQL

sql serversql-server-2016ssisstored-procedures

We are loading ETL data in two parts, first with an SSIS package and then stored procedure.

In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 Stored procedure to run only After completion of Part1 SSIS?

Is there a way to conduct this in T-SQL? Should we use delay function and check every few minutes with SSIS dmv SSISDB.catalog.executions if package is complete? Is there another way?

Background:
We can place sproc in a control flow arrow after SSIS. However, our company does not want to conduct this. We are more of a script based company. Additionally trying to refrain from SQL agent jobs. Currently QA department needs a way to run items, without creating multiple jobs. We created jobs for parent level packages, however do not want to create unit jobs for our 500+ SSIS and sprocs.

--Part 1
EXEC [SSISDB].[catalog].[create_execution] 
               @package_name=N'Customer_StageTable_Part1_Insert.dtsx'
               , @project_name=N'Datawarehouse'
               , @folder_name=N'Datawarehousefolder'
               , @use32bitruntime=False
               , @reference_id= 2
               , @execution_id=@execution_id OUTPUT

select @execution_id

--Part 2
execute Customer_StageTable_Part2_Insert

https://www.sqlshack.com/execute-deployed-package-ssis-catalog-various-options/

Best Answer

You can specify that the package execution is SYNCRONIZED to wait in TSQL until it's complete using [SSISDB].[catalog].[set_execution_parameter_value]

So something like:

--Part 1
EXEC SSISDB.catalog.create_execution
               @package_name=N'Customer_StageTable_Part1_Insert.dtsx'
               , @project_name=N'Datawarehouse'
               , @folder_name=N'Datawarehousefolder'
               , @use32bitruntime=False
               , @reference_id= 2
               , @execution_id=@execution_id OUTPUT

EXEC SSISDB.catalog.set_execution_parameter_value
               @execution_id
               , @object_type=50
               , @parameter_name=N'SYNCHRONIZED'
               , @parameter_value= 1; 

--Start the package and wait for it to complete
EXEC SSISDB.catalog.start_execution @execution_id;

--Part 2
execute Customer_StageTable_Part2_Insert

see, eg http://www.ssistalk.com/2012/07/24/quick-tip-run-ssis-2012-packages-synchronously-and-other-execution-options/

Related Question