Sql-server – Run SQL Agent Job 3 times in a Row

I think I would solve it this way:

• Create a driving stored procedure that identifies if work needs to be done. That could mean checking a table for the next folder month to process. This driving table would be pre-populated with the number of folders (months) that need to be processed. You might want to include a method to determine the order of the folders to process so that you process them in the correct order.
• Set up a WHILE LOOP to check IF work is to be done. Use the custom stored procedure sp_sp_start_job_wait to invoke the job that has the 4 steps. This procedure will start the job and wait for it to complete.
• Upon successful completion of the job, mark as complete or delete the folder row for the month you just processed.
• The WHILE LOOP re-iterates to check for more work to be done.

To start the entire process, simply invoke the driving stored procedure.

You could also eliminate the need for the SQL Agent jobs altogether and simply invoke the SSIS packages directly from the driving stored procedure I described earlier using built-in stored procedures; e.g. ssisdb.catalog.create_execution, ssisdb.catalog.start_execution. There is a good blog post describing that process How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure. Summarizing the main points from that post:

create procedure dbo.execute_ssis_package_sample
 @output_execution_id bigint output
as
begin
 declare @execution_id bigint
 exec ssisdb.catalog.create_execution 
  @folder_name = 'mssqltips'
 ,@project_name = 'exec-ssis-stored-proc-ssis-sample'
 ,@package_name = 'Sample.dtsx'
 ,@execution_id = @execution_id output
 exec ssisdb.catalog.start_execution @execution_id
 set @output_execution_id = @execution_id
end

• The stored procedure ssisdb.catalog.create_execution (new in SSIS 2012) is called to create an instance of an SSIS package execution.
• You pass the folder, project and package to the ssisdb.catalog.create_execution stored procedure.
• ssisdb.catalog.create_execution returns an output parameter named @execution_id; you need this value to start executing the SSIS package as well as to check the status of package execution.
• The ssisdb.catalog.start_execution stored procedure (new in SSIS 2012) is called to start the actual execution of the package.
• The SSIS package execution is asynchronous; i.e. when you call the ssisdb.catalog.start_execution stored procedure you are not waiting for the package to complete.

I highlighted the fact that the default behavior of ssisdb.catalog.start_execution is to run asynchronous, but there is a workaround for that as documented in this post by Tim Mitchell. See the section Execute Packages Synchronously. Quoting from that post:

This one is a relatively easy fix. Although the default behavior is to execute [catalog].[start_execution] asynchronously, there is a system parameter that lets you override this behavior on a per-execution basis. The parameter name is SYNCHRONIZED, and it expects a value of either 0 (not synchronized, the default behavior) or 1 (synchronized). When overriding this value and setting it to 1, the call to [catalog].[start_execution] will not return until the package has completed its execution. This means that [catalog].[start_execution] will run as long as the package is still running. The syntax to add this parameter is shown below.

-- Create the execution object
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
    @package_name = N'Package1.dtsx'
    , @project_name = N'Project1'
    , @folder_name = N'Folder1'
    , @use32bitruntime = False
    , @reference_id = NULL
    , @execution_id = @execution_id OUTPUT

-- System parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @execution_id
    , @object_type = 50                     -- System parameter
    , @parameter_name = N'SYNCHRONIZED'
    , @parameter_value = 1

-- Execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

As shown, parameter values (including system, project, and package parameters) are added after [catalog].[create_execution] and before [catalog].[start_execution]. In the above example I set the SYNCHRONIZED system parameter to 1, forcing [catalog].[start_execution] to wait until the package completes before it returns.

This override solves one problem but leaves another: Even though [catalog].[start_execution] now runs synchronously, it does not report the status of the package execution. Most workflows need to know the status of the package execution, not just that the execution has finished. Fortunately, there is a workaround for that as well.

Force a Script Failure if the Package Fails

This workaround requires more than a simple execution parameter, so it has more moving parts. In a nutshell, here is the design pattern for forcing the T-SQL script to fail in the event that the package it has called fails:

• Create the execution
• Set the SYNCHRONIZED parameter to 1
• Execute the package
• Check the execution log for this execution, and force the T-SQL script to fail if the package failed

The previous script already took care of steps 1-3, so we just need to add the final step. This can be as simple or as complicated as needed, but in my example I just check [catalog].[executions] and retrieve the [status] value for that execution; if the value does not equal 7 (the status ID for Success), the script will generate a failure using RAISERROR.

-- Create the execution object
DECLARE @execution_id BIGINT
EXEC [SSISDB].[catalog].[create_execution] 
    @package_name = N'Package2.dtsx'
    , @project_name = N'Project1'
    , @folder_name = N'Folder1'
    , @use32bitruntime = False
    , @reference_id = NULL
    , @execution_id = @execution_id OUTPUT

-- System parameters
EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
    @execution_id
    , @object_type = 50                     -- System parameter
    , @parameter_name = N'SYNCHRONIZED'
    , @parameter_value = 1

-- Execute the package
EXEC [SSISDB].[catalog].[start_execution] @execution_id

-- Check package status, and fail script if the package failed
IF 7 <> (SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id = @execution_id)
RAISERROR('The package failed. Check the SSIS catalog logs for more information', 16, 1)