SSIS : Execute SSIS Packages with T-SQL using SSISDB Catalog

There are several ways to execute SSIS packages that have been deployed to SSISDB catalog. One of method is using T-SQL statement or SQL stored procedures. It is the most flexible way to execute SSIS catalog deployed packages. There are multiple scenarios where it is very handy or helpful  to run SSIS package from T-SQL quires or SQL stored procedures.


The following steps will cover the requirement :

1. Connect to SSMS and open SSIS catalog database, check the required SSIS package :

2. Right Click on SSIS package and click on execute :

3. Now, Need to select the Parameter used in SSIS package, click on three dots (...) belongs to parameter(s)  and input the require value :

4. After parameter inputs, click on script (Top Left) to generate the following script :

The above code can be copied from here :

USE [SSISDB] 

--This will create a execution id for package run 

DECLARE @execution_id BIGINT 

EXEC [SSISDB].[catalog].[Create_execution] 

  @package_name=N'Test_Pkg.dtsx', 

  @execution_id=@execution_id output, 

  @folder_name=N'Test', 

  @project_name=N'SSIS_Package_Run_Using_T-sql', 

  @use32bitruntime=false, 

  @reference_id=NULL 

SELECT @execution_id 

--This will set the parameter values to used in SSIS package 

DECLARE @var0 INT = 0 

EXEC [SSISDB].[catalog].[Set_execution_parameter_value] 

  @execution_id, 

  @object_type=20, 

  @parameter_name=N'Project_Param', 

  @parameter_value=@var0 

DECLARE @var1 SMALLINT = 1

EXEC [SSISDB].[catalog].[Set_execution_parameter_value] 

  @execution_id, 

  @object_type=50, 

  @parameter_name=N'LOGGING_LEVEL', 

  @parameter_value=@var1 

--Final Store procedure to execute the ssis package  

EXEC [SSISDB].[catalog].[Start_execution] 

  @execution_id 

GO

Conclusion : 

In this post, I have demonstrated how to run catalog deployed SSIS package using T-SQL statements. We can develop a stored proc to fulfill requirements and run SSIS packages using T-SQL.