Stored Procedure to Execute an SSIS Package from the SSIS Catalog

 CREATE PROCEDURE [dbo].[CREATE_EntitySelectionExecution]

@P_PROCEDURE_NAME NVARCHAR(128)

, @P_STAGE_ID INT

, @P_BRANCH_NUMBER INT

, @P_CLOSE_DATE DATETIME = NULL

, @P_FILTER_DATE DATETIME = NULL

, @P_START_EXECUTION INT = 1

, @P_EXECUTION_ID BIGINT OUT

AS

BEGIN

/*

Call the [SSISDB].[catalog].[create_execution] stored procedure

for one of the SSIS packages in the <FILL IN> project.

*/

DECLARE

@EXECUTION_ID BIGINT

, @FOLDER_NAME NVARCHAR(128)

, @PROJECT_NAME NVARCHAR(128)

, @PACKAGE_NAME NVARCHAR(128)

, @ENVIRONMENT_REFERENCE_ID BIGINT;

-- Lookup the mapping for the calling stored procedure

SELECT

@FOLDER_NAME = [FolderName]

, @PROJECT_NAME = [ProjectName]

, @PACKAGE_NAME = [PackageName]

, @ENVIRONMENT_REFERENCE_ID = [EnvironmentReferenceID]

FROM [dbo].[MAP_ProcedureToPackage]

WHERE [ProcedureName] = @P_PROCEDURE_NAME;

IF @@ROWCOUNT = 0

THROW 2147483647, N'PROCEDURE NAME LOOKUP failed', 0;

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

@folder_name = @FOLDER_NAME

,   @project_name = @PROJECT_NAME

,   @package_name = @PACKAGE_NAME

, @reference_id = @ENVIRONMENT_REFERENCE_ID 

,   @execution_id = @EXECUTION_ID OUTPUT;

 

EXEC [SSISDB].[catalog].[set_execution_parameter_value] 

@execution_id = @EXECUTION_ID

, @object_type = 50 -- System parameter

, @parameter_name = N'SYNCHRONIZED'

, @parameter_value = 1;

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

        @execution_id = @EXECUTION_ID  

    ,   @object_type = 30 -- package parameter 

    ,   @parameter_name = N'STAGE_ID'  

    ,   @parameter_value = @P_STAGE_ID;

   

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

        @execution_id = @EXECUTION_ID  

,   @object_type = 30 -- package parameter 

    ,   @parameter_name = N'BRANCH_NUMBER'  

    ,   @parameter_value = @P_BRANCH_NUMBER;

IF @P_CLOSE_DATE IS NOT NULL

BEGIN

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

@execution_id = @EXECUTION_ID  

,   @object_type = 30 -- package parameter 

,   @parameter_name = N'CLOSE_DATE'  

,   @parameter_value = @P_CLOSE_DATE;

END

IF @P_FILTER_DATE IS NOT NULL

BEGIN

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

@execution_id = @EXECUTION_ID  

,   @object_type = 30 -- package parameter 

,   @parameter_name = N'FILTER_DATE'  

,   @parameter_value = @P_FILTER_DATE;

END

SET @P_EXECUTION_ID = @EXECUTION_ID;

IF @P_START_EXECUTION = 1

BEGIN

EXEC [SSISDB].[catalog].[start_execution]

  @execution_id = @EXECUTION_ID;

END

END