T-SQL Stored Proc to Execute SSIS Catalog Package in Synchronous Mode & Return Status.

General Anatomy of Catalog Package Execution.

• Create an instance of Execution
• Set Execution instance Parameters
• Start the execution instance.

The following stored proc accomplishes the following.

• Start a SSIS Package from Catalog.
• Waits for Package to Finish. (Synchronous Execution)
• Returns error if Package fails.

It accepts the following parameters -

•  Folder
•  Project
• Package
• Environment
• One Package Parameter of type Int.
• One Package Parameter of type varchar.
• Type of Parameter - Package or Project.

-------------- Stored Proc Code -------------------

CREATE Proc dbo.usp_Execute_Package

@environment_name varchar(1024),

@package_name varchar(1024) ,

@folder_name varchar(1024) ,

@project_name varchar(1024),

@param_name_int varchar(1024) = null ,

@param_value_int int = null,

@param_name_varchar varchar(1024) = NULL,

@param_value_varchar varchar(1024) = NULL,

@param_type int = 30 -- 30 to indicate package parameter, 20 to indicate project parameter.

AS

declare @reference_id int

declare @errmsg varchar(1024)

-- Get the Reference to Catalog Environment.

SELECT  @reference_id = reference_id

  FROM  [catalog].environment_references er

        JOIN [catalog].projects p ON p.project_id = er.project_id

 WHERE  er.environment_name = @environment_name

-- Create Execution Instance.

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution]

       @package_name=@package_name,

       @execution_id=@execution_id OUTPUT,

       @folder_name=@folder_name,

       @project_name=@project_name,

       @use32bitruntime=False,

       @reference_id=@reference_id

-- Set Int Parameter.

IF @param_name_int is not null

EXEC [catalog].[set_execution_parameter_value] @execution_id, 

       @object_type=@param_type,

       @parameter_name=@param_name_int,

       @parameter_value=@param_value_int

-- Set Varchar Parameter.

IF @param_name_varchar is not null

EXEC [catalog].[set_execution_parameter_value] @execution_id, 

       @object_type=@param_type,

       @parameter_name=@param_name_varchar,

       @parameter_value=@param_value_varchar

-- Set Logging Level.

DECLARE @var1 smallint = 1

EXEC [catalog].[set_execution_parameter_value]

       @execution_id, 

       @object_type=50,

       @parameter_name=N'LOGGING_LEVEL',

       @parameter_value=@var1

-- Set for Synchronized Execution.

exec [catalog].[set_execution_parameter_value]

              @execution_id, 

              @object_type=50,

              @parameter_name=N'SYNCHRONIZED',

              @parameter_value=1

-- Start Execution.

EXEC [catalog].[start_execution] @execution_id

-- Capture Any Error Messages.

SELECT top 1 @errmsg =  left(MESSAGE,1024)

FROM   (

       SELECT  em.*

       FROM    catalog.event_messages em

       WHERE   em.operation_id = @execution_id

           AND event_name = 'OnError'

       )q

ORDER BY message_time DESC

if @errmsg is not null

 BEGIN

       RAISERROR (@errmsg, 16, 1 )

       return -1

END

RETURN 0

GO

--- Usage

exec dbo.usp_Execute_Package @environment_name = 'My_Environment',

       @package_name = 'My_Package.dtsx',

       @folder_name = My_Folder',

       @project_name = 'My_Project',

       @param_name_int = 'pMy_Param',

       @param_value_int = 10000