catalog.start_execution (SSISDB Database)

SQL Server 2012

Starts an instance of execution in the Integration Services catalog.

start_execution [ @execution_id = ] execution_id

[ @execution_id = ] execution_id

The unique identifier for the instance of execution. The execution_id is bigint.

An execution is used to specify the parameter values that will be used by a package during a single instance of package execution. After an instance of execution has been created, before it has been started, the corresponding project might be redeployed. In this case, the instance of execution will reference a project that is outdated. This will cause the stored procedure to fail.

Note Note

Executions can only be started once. To start an instance of execution, it must be in the created state (a value of 1 in the status column of the catalog.operations view).

The following example calls catalog.create_execution to create an instance of execution for the Child1.dtsx package. Integration Services Project1 contains the package. The example calls catalog.set_execution_parameter_value to set values for the Parameter1, Parameter2, and LOGGING_LEVEL parameters. The example calls catalog.start_execution to start an instance of execution.

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Child1.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'TestDeply4', @project_name=N'Integration Services Project1', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 sql_variant = N'Child1.dtsx'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Parameter1', @parameter_value=@var0
DECLARE @var1 sql_variant = N'Child2.dtsx'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Parameter2', @parameter_value=@var1
DECLARE @var2 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var2
EXEC [SSISDB].[catalog].[start_execution] @execution_id

This stored procedure requires one of the following permissions:

  • READ and MODIFY permissions on the instance of execution, READ and EXECUTE permissions on the project, and if applicable, READ permissions on the referenced environment

  • Membership to the ssis_admin database role

  • Membership to the sysadmin server role

The following list describes some conditions that may raise an error or warning:

  • The user does not have the appropriate permissions

  • The execution identifier is not valid

  • The execution has already been started, or it has already been completed; executions can be started only once

  • The environment reference associated with the project is not valid

  • Required parameter values have not been set

  • The project version associated with the instance of execution is outdated; only the most current version of a project can be executed

Community Additions