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
