Blog

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:

Right click the package, and choose “Execute”. This will open the following 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:

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:

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:

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.