Execute SSIS via Stored Procedure (SSIS 2012)

05/03/2015 00:59

The SSIS 2012 Catalog integration with SQL Server comes with the advantage of being able to execute SSIS packages indigenously from within T-SQL, along with a host of other functionality including improved monitoring and logging, integrated security and obviously the new deployment model.

Executing SSIS packages by calling a stored procedure can be handy in many situations, but also comes with some (albeit minor) stipulations and quirks, this post tries to go through some of the basics of executing SSIS packages through stored procedures, while also touching lightly on some of the issues I encountered while having a go at this new SSIS execution method.

 

SSIS packages are executed asynchronously through a collection of procedures, these procedures perform functions such as initialize the execution context of a package, control the package/project/environment parameters, and kick off a particular execution context.

So lets begin with an example, we start by kicking-off an SSIS package execution context using the stored procedure: [SSISDB].[catalog].[create_execution]:

    DECLARE @exec_id BIGINT

    EXEC [SSISDB].[catalog].[create_execution] 
        @package_name=N'Test.dtsx',     --SSIS package name TABLE:(SELECT * FROM [SSISDB].internal.packages)
        @folder_name=N'Warehouse ETLs', --Folder were the package lives TABLE:(SELECT * FROM [SSISDB].internal.folders)
        @project_name=N'ImportPackages',--Project name were SSIS package lives TABLE:(SELECT * FROM [SSISDB].internal.projects)
        @use32bitruntime=FALSE, 
        @reference_id=NULL,             --Environment reference, if null then no environment configuration is applied.
        @execution_id=@exec_id OUTPUT   --The paramter is outputed and contains the execution_id of your SSIS execution context.

    SELECT @exec_id

This does not actually run the SSIS package, but instead it starts an execution context, with a specific execution_id value which is returned to the user as a result of executing the stored procedure, this execution_id will be used for various functions such as adding parameters to the execution context, starting the execution of the package and checking on the status of the package.

Once a package is created, it will have an Execution Status of Created (1). You can query the package execution status (in numerical format) using the following stored procedure:

  SELECT [STATUS]
  FROM [SSISDB].[internal].[operations]
  WHERE operation_id = @exec_id

There is a handy tip by Jamie Thomson about SSIS Execution Statuses.

Just one more thing to note here, if you get any of the project/folder/package names wrong you’ll get an error that looks like this:
Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.

Just make sure you get the names correct, you can use the SELECT procedures above to verify the names you could use for each parameters.

Now in order to add parameters to an SSIS package execution context, you can call the following stored procedure:

    DECLARE @IntParam sql_variant = 2 --Some random parameter value, needs to be in sql_variant format

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @exec_id,  -- The execution_id value we received by calling [create_execution]
        @object_type=30,  --30 is Package Parameters, you can also use 20 for Project parameters or 50 for Environment
        @parameter_name=N'ParamDateOffset',  --Parameter name
        @parameter_value=@IntParam

The code above is pretty much self explanatory, again make sure you get the object_typeparameters correct here (i.e. whether they are package level parameters of project level parameters etc.), otherwise you’ll get a nasty error about parameter not existing or not having permission to set them.

Finally when you are ready to execute an SSIS package, you can use the following simple stored procedure:

    EXEC [SSISDB].[catalog].[start_execution] @exec_id

This will change the package Execution Status from Created (1) to Running (2).

It is important to note that if a package is left un-executed (and in the Created (1) Execution Status), then the next time SSIS maintenance job runs it will change the Execution Status toEnded Unexpectedly (6).

Here you could monitor the SSIS package through the relevant log tables in the SSIS Catalog DB, or using the more friendly SSRS reports to follow the progress of your SSIS package. If everything goes well, the SSIS package Execution Status will change to Succeeded (8).

SSIS Windows Authentication Account Requirement

In order to execute SSIS packages, you will need to execute the above stored procedures using a Windows Authentication Account rather than SQL Server Authentication Account, otherwise you will be at the receiving end of the following error:

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
A .NET Framework error occurred during execution of user-defined routine or aggregate “start_execution_internal”:
System.Data.SqlClient.SqlException: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Although annoying, this requirement makes sense considering SSIS executes outside the context of SQL Server and as part of an OS process, which means that a Windows account is necessary in order to execute a package.

I tried changing the execution context (through EXECUTE AS) but that didn’t seem to help, so I resorted to using a Windows Authenticated Account in order to execute an SSIS package via stored procedure.

Back