We can execute SSIS packages deployed in SSIS Catalog using stored procedure based approach as given below:
DECLARE
@execution_id INT
EXEC
[SSISDB].[catalog].[create_execution]
@package_name =
N'Package.dtsx',
@execution_id =
@execution_id OUTPUT,
@folder_name =
N'FolderName',
@project_name =
N'ProjectName',
@use32bitruntime =
False;
EXEC
[SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type = 30,
@parameter_name =
N'FolderName',
@parameter_value =
N'ParameterValue';
EXEC
[SSISDB].[catalog].[set_execution_parameter_value]
@execution_id,
@object_type = 30,
@parameter_name =
N'EnvironmentPath',
@parameter_value =
N'EnvironmentValue';
exec
catalog.set_execution_parameter_value
@execution_id, 50, 'DUMP_ON_ERROR',1
exec
catalog.set_execution_parameter_value
@execution_id, 50, 'LOGGING_LEVEL',3
EXEC
[SSISDB].[catalog].[start_execution] @execution_id;
Once we execute the package, we can check the status of execution using the below approach:
DECLARE @status AS
BIGINT = 1;
WHILE(@status = 1 OR
@status = 2 OR @status = 5 OR @status= 8)
BEGIN
PRINT @status
PRINT 'waiting 5
seconds for Package to finish'
WAITFOR DELAY
'00:00:5';
SET @status =
(SELECT [Status] FROM SSISDB.[catalog].[executions]
WHERE execution_id = @execution_id);
END
If we want to execute the SSIS package using commandline, we can take the below approach:
dtexec.exe /isserver "\SSISDB\FolderName\ProjectName\Package.dtsx" /server "ServerName" /parameter $Package::FolderName;"ParameterValue" /parameter $Package::Sleep(Int32);60 /parameter $Package::MaxRun(Int32);300 /parameter $Package::EnvironmentPath;"EnvironmentValue"
