Execute SSIS Package Using T SQL

SQL Server provides several tools to execute the SSIS package. A package can be executed using dtexec command prompt utility, SQL Agent and Transact SQL. This article provides information and examples to help you understand the package execution using TSQL.

The SSIS project deployed into the Integration Service Catalog has few parts

• Folder
• Project
• Packages
• Environment

A project deployed to SSIS Catalog will look similar to the below image.
The following system stored procedures and views are used for package execution from T-SQL.

• create_execution
• set_execution_parameter_value
• start_execution
• executions
• operation_messages

The “create_execution” procedure initiates the package execution and returns a unique number called execution Id. At this point, the package is not executed but the procedure only creates an instance and sets up some of the default parameters.

DECLARE @execution_id BIGINT 

EXEC ssisdb.catalog.Create_execution 
  @folder_name = 'IntellectSQL', 
  @project_name = 'ExecutePackageDemo', 
  @package_name = 'Package.dtsx', 
  @reference_id = 5, 
  @execution_id = @execution_id output 

The next procedure is “set_execution_parameter_value”. While it is optional, it is useful to set any run time value for the parameters and change the default value of the parameters.

The following example sets a value to the package parameter “User”.

EXEC ssisdb.catalog.Set_execution_parameter_value 
  @execution_id, 
  30, 
  'User', 
  N'Joe' 

The first parameter is the execution id which returned from the “create_execution” procedure,
the second parameter is an object type. The acceptable values for the object type are 20, 30 and 50.
The value 20 indicates project parameter and 30 indicates package parameter and 50 indicates default system parameter. In this example, the parameter “User” is a package parameter so 30 is mentioned as the object type.

The final procedure is “catalog.start_execution”, the execution of this procedure starts the package to run in the server.

exec ssisdb.catalog.start_execution @execution_id

This procedure returns failure for the following reasons

• The execution id is incorrect.
• The user does not have sufficient rights to execute the package.
• The environment reference associated with the project is not valid.
• Required parameter values have not been set.

The procedure won’t report an error even if there is a failure within the package execution except for the aforesaid validations. SQL Server runs the package in an asynchronous mode by default. So, to capture the outcome of the package execution the view “catalog.executions” should be checked repeatedly until the desired result is found.

The default behavior can be changed by overriding the SYNCHRONIZED parameter value from 0 to 1 as shown in the below SQL statement.

EXEC ssisdb.catalog.Set_execution_parameter_value 
  @execution_id, 
  50, 
  synchronized, 
  1 

In synchronous execution, the “start_execution” finishes only after the completion of the package execution.
The status of the package execution can be retrieved from the view “catalog.executions”.
The status 7 will be returned for the successful execution, for the complete list of status code, refer to the BOL.
The below query returns the status of the execution.

SELECT status 
FROM   ssisdb.catalog.executions 
WHERE  execution_id = @execution_id 

The error details are captured and stored in ssisdb when there is a failure in package execution and the details
can be retrieved from the view “catalog.operations”

SELECT [message] 
FROM   ssisdb.catalog.operation_messages 
WHERE  message_type = 120 
       AND operation_id = @execution_id 

Putting everything together

DECLARE @execution_id BIGINT, 
        @msg          NVARCHAR(max) 

EXEC ssisdb.catalog.Create_execution 
  @folder_name = 'IntellectSQL', 
  @project_name = 'ExecutePackageDemo', 
  @package_name = 'Package.dtsx', 
  @reference_id = 5, 
  @execution_id = @execution_id output 

EXEC ssisdb.catalog.Set_execution_parameter_value 
  @execution_id, 
  30, 
  'User', 
  N'Joe' 

EXEC ssisdb.catalog.Set_execution_parameter_value 
  @execution_id, 
  50, 
  'SYNCHRONIZED', 
  1 

EXEC ssisdb.catalog.Start_execution 
  @execution_id 

IF (SELECT status 
    FROM   ssisdb.catalog.executions 
    WHERE  execution_id = @execution_id) <> 7 
  BEGIN 
      SELECT @msg = [message] 
      FROM   ssisdb.catalog.operation_messages 
      WHERE  message_type = 120 
             AND operation_id = @execution_id 

      RAISERROR(@msg,16,1) WITH nowait 
  END