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
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.
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