CODEWITHSUNDEEP
ssissql-server-2016
Eduardo Almeida
Eduardo Almeida

Reputation: 408

SSIS schedule and restart package execution

I want to avoid duplicate SSIS operations when the SQL Server restarts.

I've set a job with a single step (to run a SSIS package which has an infinite loop itself). This job is scheduled to run "when the Sql Server Agent starts".

When the SQL Server Agent is restarted, the job is also restarted and the Active Operations in my SSIS catalog show a single running Task, which is good. But when the Windows Server itself is restarted, the Active Operations shows two running Tasks, and they are both the same -> https://i.sstatic.net/g2HRG.jpg

I expect only a single Active Operation whether I restart the Windows Server or the SQL Server Agent. How can I achieve this?

Upvotes: 1

Views: 572

Answers (1)

userfl89
userfl89

Reputation: 4790

In the job step, execute the package from the SSIS catalog using T-SQL and add a condition to check if the package is already active. In the example below the catalog.executions DMV is queried for a package by the given name with a status of 2, which represents a currently running package. If this package is found already active then nothing is done, otherwise it is executed from the catalog.start_execution stored procedure.

IF NOT EXISTS(SELECT EXECUTION_ID FROM SSISDB.CATALOG.EXECUTIONS WHERE PACKAGE_NAME = 'YourPackage.dtsx' 
AND PROJECT_NAME = 'ProjectOfPackage' AND FOLDER_NAME = 'Folder Containing Package' AND [STATUS] = 2)
BEGIN 

DECLARE @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'YourPackage.dtsx', @execution_id=@execution_id OUTPUT, 
@folder_name=N'Folder Containing Package', @project_name=N'ProjectOfPackage'

DECLARE @var0 smallint = 2
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, 
@parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

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

END

Upvotes: 2

Related Questions