2

How do I execute SQL Agent job 3 times in a row?

We are creating Data Warehouse Project. It ETLs data from Flatfile Legacy Folder, and creates Dim and Fact tables.

SSIS Project Environment Variable (FlatfileFolder) set: to March 2018

  1. Import ETL Flatfiles into Staging Database for March 2018
  2. Create Customer Dimension Tables
  3. Create Product Dimension Tables
  4. Create SalesTransaction Fact Tables

Want to execute this 3 times in a row, changing Project Environment Variable (FlatfileFolder) to April 2018. Run 4 jobs again, then change Project Environment Variable (FlatfileFolder) to May 2018, and run 4 job steps again. (Theoretically, should have been designed to extract all history files and load at once, however did not design this, just inheriting it.)

This stack link below talks about endless loop, only want to run it 3 times, and change environment variable.

Running job in endless loop

Options:

(a) write 12 Step SQL Agent Job,

(b) Or somehow after Step 4, on success go to back to Step 1? How to make this only run 3 times?

Eventually may want to execute job 5-10 times in row for later months. Trying to conduct with SQL environment, prefer to refrain from C# or Powershell unless required.

We are processing data.

Thanks,

3
  • Are the packages stored in the SSISDB? If so, you can execute them with stored procedures. So theoretically, you could make your own stored procedure to take an input (i.e. number of times to execute the package) and then use that sproc to execute the packages.
    – Jacob H
    Commented Apr 5, 2019 at 17:27
  • Update a value in a table to track how many times it has run, and when it has run three times, throw and error and have that job step end on error. Commented Apr 5, 2019 at 17:39
  • Hi @JacobH correct, the packages are stored in SSISDB, feel free to write in answer, and I can give points, Scott also has good input below
    – user173948
    Commented Apr 5, 2019 at 21:38

1 Answer 1

6

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)
2
  • 1
    This is awesome ! thank you! one more question, if SSIS packares are within SQL agent jobs, and I stop Sql agent job, the ssis package and everything within stops; now if I try to cancel a stored procedure which runs SSIS , sprocs will stop, but not SSIS, is there anyway to close everything within sproc, SSIS and all its dependencies?
    – user173948
    Commented Apr 6, 2019 at 17:47
  • @JoeSmith - I'm afraid I don't know the answer to your last question. The information I've presented pretty much sums up my knowledge on this particular subject. Perhaps someone else will provide a better answer for your needs. Commented Apr 7, 2019 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.