SSIS: Execute SSIS Package from a SQL Server Stored Procedure

I have a Integration Services Project:SSIS_UK_Projects contains the package LEI_Lookup.dtsx. But how I can run it from Stored Procedure?

1. calls catalog.create_execution to create an instance of execution for the LEI_Lookup.dtsx package.

EXEC [SSISDB].[catalog].[create_execution]
@package_name=N’LEI_Lookup.dtsx’,
@execution_id=@execution_id OUTPUT,
@folder_name=N’UK’,
@project_name=N’SSIS_UK_Projects’,
@use32bitruntime=False,
@reference_id=@env_reference_id

2 calls catalog.set_execution_parameter_value to set values for the parameter, and LOGGING_LEVEL parameters.

Select @execution_id
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N’LOGGING_LEVEL’,@parameter_value=1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N’Client_code’, @parameter_value=@parameter1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=30, @parameter_name=N’Source_table’, @parameter_value=N’processing_hold’

3. calls catalog.start_execution to start an instance of execution.

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

Leave a Reply