SSISDB.catalog.create_execution

The SSISDB Catalog was introduced in SQL Server 2014. With it came a lot of procedures to manage deployed SSIS projects. The procedures cover running, configuring, managing and troubleshooting SSIS packages.

In this post I will cover a procedure that can be used to run SSIS packages. The procedure is SSISDB.catalog.create_execution.

The easiest way to get started with the catalog.create_execution procedure is to script an execution from SQL Server Management Studio. First, make sure you have a package deployed to your server:

Test Package In SSISDB Catalog
Test Package In SSISDB Catalog

Right click the package, and choose “Execute”. This will open the following dialog:

SSISDB Execute Package Dialog
SSISDB Execute Package Dialog

Just press the “Script” button and select “New Query Editor Window”. The script that calls the catalog.create_execution procedure will be generated for you:

create_execution Script
create_execution Script

The basic flow in this script is to first create the execution. That is done with the create_execution procedure and returns a @execution_id. Next, one of the execution parameters is set with the procedure set_execution_parameter_value. The parameter that is scripted is the LOGGING_LEVEL parameter. The different logging levels can be found at MSDN. Lastly, the package is executed with the procedure start_execution.

If you run the generated script, the package will be executed asynchronously. But if you want to wait for the execution to complete, you have to execute the package synchronously.

Execute SSIs package synchronously with create_execution

One of the parameters for the set_execution_parameter_value is named “SYNCHRONIZED”. Add the following row to the script:

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value= 1

Add the row just after the other parameter:

create_execution Script Synchronized Parameter
create_execution Script Synchronized Parameter

This time the script will wait for the package execution to finish. This adds the possibility to check for errors in the execution. An asynchronous execution has the downside that to know if there are any errors, you’ll have to monitor the logs. But if you wait for the package to complete, just look for any errors on the @execution_id by adding the following T-SQL lastly in the script:

DECLARE @Status INT
SELECT @Status = [Status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id
IF (ISNULL(@Status, 0) <> 7) BEGIN
	DECLARE @ErrorMessage NVARCHAR(MAX)
	SELECT TOP 1 @ErrorMessage = 'Execution path ' + execution_path + ' failed with error: ' + [message] FROM SSISDB.catalog.event_messages WHERE operation_id = @execution_id AND event_name = 'OnError'
	;THROW 50000, @ErrorMessage, 1
END 

Now the error will be thrown and look something like this:

Catching Errors From SSISDB execution
Catching Errors From SSISDB execution

To summarize, add the parameter row “SYNCHRONIZED” in the script (before the call to start_execution) and add the error catching routine last in the script.

The complete script for the demostration above look like this:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'TestPackage.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'Test', @project_name=N'TestProject', @use32bitruntime=False, @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value= 1
EXEC [SSISDB].[catalog].[start_execution] @execution_id

DECLARE @Status INT
SELECT @Status = [Status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id
IF (ISNULL(@Status, 0) <> 7) BEGIN
	DECLARE @ErrorMessage NVARCHAR(MAX)
	SELECT TOP 1 @ErrorMessage = 'Execution path ' + execution_path + ' failed with error: ' + [message] FROM SSISDB.catalog.event_messages WHERE operation_id = @execution_id AND event_name = 'OnError'
	;THROW 50000, @ErrorMessage, 1
END 
GO

Personally, I prefer this way to schedule SSIS packages from the SQL Server Agent. And the reason is that I can see the actual error in the SQL Agent log, instead of just a reference to the SSISDB catalog.

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 3 Comments

  1. Faraz Sid

    Hi Tomas. Happy new Year. Thanks for the helpful stuff on this website. I am facing a rather peculiar problem. I have created an SSIS package and deployed it. It appears on the SqlServer and works fine. I have put it in my ON INSERT trigger (pasted below) and works fine when i test it from SqlServer.

    ***start of trigger text***
    create trigger trig_customermaster
    on erp.customer
    after insert, update
    as

    Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N’CustomerMaster.dtsx’, @execution_id=@execution_id OUTPUT, @folder_name=N’fffIntegration’, @project_name=N’fff int-DB1′, @use32bitruntime=False, @reference_id=Null
    Select @execution_id
    DECLARE @var0 smallint = 1
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N’LOGGING_LEVEL’, @parameter_value=@var0
    EXEC [SSISDB].[catalog].[start_execution] @execution_id
    GO

    ***end of trigger text***

    as i said, it works fine and based on my SSIS package another table is populated when an insert or update is made to my erp.customer table. However, when i run my ERP (EPICOR 10) it throws the following error:

    *** start of error message from EPICOR from ***

    The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
    The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
    The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.
    The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication

    *** endof error message from EPICOR from ****

    Can you please guide me as to what i may be doing wrong here? The SSIS package was created using WINDOWS AUTHENTICATION. Thanks in advance.

    1. Tomas Lind

      Hi Faraz, it seems EPICOR is using a SQL login that in effect runs the package. Check if you can use the EXECUTE AS statement to change that.

      Regards,
      Tomas

  2. Bob

    Just fyi, SSISDB was introduced in SQL Server 2012.

Leave a Reply