Run SQL Server Agent Job from SSIS

Problem

We make extensive use of SQL Server Integration Services (SSIS) packages to perform all sorts of Extract, Transform and Load (ETL) operations. We have many SQL Server Agent Jobs and SQL Server Reporting Services (SSRS) report subscriptions that we want to launch at certain points in our SSIS packages. While we can schedule SQL Server Agent jobs and SSRS report subscriptions, the actual time that we want to run them is dependent on when certain processing in our SSIS packages is completed. We also have certain business rules surrounding launching these jobs that we need to enforce which the built-in scheduling capabilities cannot accommodate. For example, we execute a SQL Server Reporting Services (SSRS) report subscription every Monday at 8:00AM but we want to skip it if the current week coincides with our month end processing which will also execute a report subscription for the same report. Can you provide a solution that we can use to accomplish this?

Solution

I will demonstrate a solution that provides the kind of flexibility that will solve your needs. SQL Server Integration Services (SSIS) has the Execute SQL Server Agent Job Task that allows you to launch a SQL Server Agent Job as a step within an SSIS package.

I will walk through a demo scenario, how to launch a SQL Agent job from an SSIS package, and wrap up with an example where custom business logic is needed to determine whether to launch a SQL Agent job.

Demo Scenario

I have a couple of SQL Server Agent jobs in the Data Warehouse category. The following query shows the list of jobs:

SELECT 
    j.[job_id]
   ,j.[name] 
FROM [msdb].[dbo].[sysjobs] j 
JOIN [msdb].[dbo].[syscategories] c 
ON c.[category_id] = c.[category_id] 
WHERE c.[name] = N'Data Warehouse';

Many people in the organization rely on dashboards to monitor the business. In order to get these dashboards updated as soon as possible, we want to launch the DAILY REFRESH DASHBOARDS job from an SSIS package running in the DAILY CUBE PROCESS job.

I have an SSRS report subscription that I want to launch from an SSIS package. The following query shows the report subscription:

SELECT 
    [SubscriptionID]
   ,[Report_OID]
   ,[Description]
FROM [ReportServer].[dbo].[Subscriptions];

Launch SQL Agent Job from SSIS

The following is the Control Flow from the DAILY_CUBE_PROCESS SSIS package that is executed in the DAILY_CUBE_PROCESS SQL Server Agent job:

The main points are:

• PROCESS CUBE is a sequence container – it is simply a placeholder for the tasks that are required to process a SQL Server Analysis Services (SSAS) cube
• Execute SQL Server Agent Job Task is available in the Other Tasks section of the SSIS Toolbox

To configure the Execute SQL Server Agent Job Task, right-click on it, select Edit, and fill in the dialogs as shown below:

Perform the following steps:

1. Click New
2. Enter a Connection name – this should be a descriptive name like PROD
3. Enter a server name – this is the server where your SQL Agent jobs are running
4. Enter log on information – it’s a best practice to use Windows NT Integrated security

After completing the above dialog, the following dialog will be displayed allowing you to select the job(s) that you want to launch:

Select the DAILY REFRESH DASHBOARDS job.

After completing the setup of the Execute SQL Server Agent Job Task, I renamed it as shown below:

We are now ready to test the SSIS package. You can execute the SSIS package from the development environment (Visual Studio or SQL Server Database Tools) by right-clicking on the package in the Solution Explorer and selecting Execute Package as shown below:

To see that the SQL Server Agent job is running:

• Execute the query shown below
• Open SQL Server Management Studio (SSMS), right-click on the job in the Object Explorer, and select View History

Use the following query to see that the SQL Agent job is running:

DECLARE @RUN_DATE DATE = GETDATE();
SELECT
    j.[name]
   ,a.[run_requested_date]
   ,a.[last_executed_step_id]
FROM msdb.dbo.sysjobactivity a
JOIN msdb.dbo.sysjobs j ON j.job_id = a.job_id
WHERE a.start_execution_date > @RUN_DATE
  AND stop_Execution_date IS NULL
ORDER BY 1

The sysjobactivity table contains the details on the SQL Server Agent jobs that are currently running.

Alternatively, you can View History in SSMS:

You will see the following when you click View History:

The first row indicates that the job is currently running. The second row indicates that step 1 of the job has completed successfully.

There is another way to launch a SQL Server Agent Job from an SSIS package and that is to use an Execute SQL Task and run the stored procedure sp_start_job.

One thing to keep in mind is that whichever way you launch the SQL Server Agent Job from an SSIS package, the SSIS package does not wait for the SQL Agent job to complete. If you need to wait for the job to complete, take a look at the tip Custom sp_start_job to delay next task until SQL Agent Job has completed.

Background on SSRS Report Subscriptions

When you create an SSRS report subscription and schedule it to run, a SQL Server Agent job gets created to execute the report based on the schedule you choose. I created a standard report subscription and scheduled it run every week on Monday at 8:00AM. The screenshots to setup the subscription in the Report Manager are shown below:

I did this on a new installation of SSRS. You can see the SQL Agent Job that was created to run this subscription in the Jobs folder under SQL Server Agent in the SSMS Object Explorer:

Right-click on the job to view the properties:

The main points are:

• The Category is Report Server – this is the category for SQL Agent jobs that execute SSRS report subscriptions
• The Description indicates that you should not be making any changes to this job as it is used by SSRS

Click on Steps under Select a Page to see the job steps:

Click on Edit (not shown in above screenshot) to see the details for the job step. The Type is Transact-SQL script (T-SQL) and the Command is:

exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='fce3553b-b1b2-4db8-9a81-8682c27d1c32'

Finally, click on Schedules under Select a Page and you will see the Description “Occurs every week on Monday at 8:00:00 AM. Schedule will be used between 3/20/2020 and 3/21/2020”. When I created the subscription, I created a schedule to get the SQL Agent job to be created but I will be executing the report subscription from an SSIS package.

I think the easiest way to understand how this all fits together is to run a couple of queries on the database server where you are running SSRS.

The first query uses the Subscriptions table:

SELECT 
    [SubscriptionID]
   ,[Report_OID]
   ,[Description]
FROM [ReportServer].[dbo].[Subscriptions];

The main points are:

• The SubscriptionID value identifies the report subscription; refer back to the Command in the SQL Server Job step above and you will see that the SubscriptionID value is passed to the AddEvent stored procedure as the @EventData parameter
• The Report_OID value identifies the report in the Catalog table (used in the next query)

Now that we have the SubscriptionID and the Report_OID, we can pull it all together with this query:

SELECT
    j.job_id JOB_ID
   ,TRY_CONVERT(uniqueidentifier, j.[name]) JOB_NAME
   ,c.[name] REPORT_NAME
FROM ReportServer.dbo.ReportSchedule rs
JOIN msdb.dbo.sysjobs j ON rs.ScheduleID = TRY_CONVERT(uniqueidentifier, j.[name])
JOIN ReportServer.dbo.Subscriptions s ON s.[SubscriptionID] = rs.[SubscriptionID]
JOIN ReportServer.dbo.Catalog c ON c.itemid = s.[Report_OID]

The main points are:

• The Subscriptions table joins to the ReportSchedule table using the SubscriptionID column; this is straight-forward
• The ResportSchedule table has a ScheduleID column that joins to the name column in the sysjobs table (i.e. SQL Server Agent jobs); this one is a little strange; however, to execute the SSRS report subscription you need the job name; it’s really not a name though, it’s a UNIQUEIDENTIFIER value
• The Catalog table has the report name and it joins to the Subscriptions table using the Report_OID column

I referenced the tip How to easily identify a scheduled SQL Server Reporting Services report in order to get many of the above details.

Earlier in this section I showed the SQL Agent Jobs and you could easily conclude that the job named D0B35FE3-8537-4A8B-9D11-6B68F62A06CC might be the one that executes the SSRS report subscription. When you look at the job step, you could see that the @EventData parameter value passed to the AddEvent stored procedure matched a SubscriptionID in the Subscriptions table. I went through this detailed description because you may have many SQL Server Agent jobs that are running SSRS report subscriptions so it’s nice to know how to run a query to get the details.

Launch SSRS Report Subscription from SSIS

The following is the Control Flow from the DAILY_ETL SSIS package that is executed in the DAILY_ETL SQL Server Agent job:

The main points are:

• DAILY_ETL is a sequence container – it is simply a placeholder for the tasks that are required to perform the Extract, Transform, and Load steps
• Execute SQL Server Agent Job Task is available in the Other Tasks section of the SSIS Toolbox

To configure the Execute SQL Server Agent Job Task, right click it, and select Edit. You will see the following dialog:

When we configured the Execute SQL Server Agent Job Task to run the DAILY REFRESH DASHBOARDS job in the earlier section, the job name was displayed and we just selected it. With a job that runs an SSRS report subscription, we still get the job name but it’s the UNIQUEIDENTIFIER value in the ScheduleID column of the ResportSchedule table. I went through all the details on how to figure this out in the previous section.

Custom Business Logic to Determine Whether to Launch a Job

I have a situation where an SSRS report subscription is scheduled to be run on Mondays at 8:00AM and also with the month-end processing job. An additional requirement is that the business users do not want to get the weekly report in the same week that the month end processing job will also send one. The SQL Agent job schedules are pretty flexible but there is no way to get this to work without some custom code.

I created the following stored procedure to determine whether the weekly SSRS report subscription should be run (the SSRS report subscription is run unconditionally in the month-end job):

CREATE OR ALTER PROCEDURE [dbo].[CHECK_RUN_WEEKLY_REPORT]
    @P_TODAY DATE = NULL
   ,@P_RUN_REPORT INT OUTPUT
AS
BEGIN
   DECLARE @TODAY DATE = IIF(@P_TODAY IS NULL, GETDATE(), @P_TODAY);
   DECLARE @CURRENT_WEEK INT = DATEPART(week, @TODAY)
          ,@LAST_MONTH_END_WEEK INT = DATEPART(week, DATEADD(day, 1, EOMONTH(@TODAY, -1)))
          ,@NEXT_MONTH_END_WEEK INT = DATEPART(week, DATEADD(day, 1, EOMONTH(@TODAY)));
   IF @CURRENT_WEEK > @LAST_MONTH_END_WEEK AND @CURRENT_WEEK < @NEXT_MONTH_END_WEEK
      SET @P_RUN_REPORT = 1;   -- run the report
   ELSE
      SET @P_RUN_REPORT = 0;   -- do not run the report
END

The main points are:

• The @TODAY variable gets the value of the current date or the value passed in as the @P_TODAY stored procedure parameter
• Get the week number in the year (@CURRENT_WEEK) of the @TODAY variable
• The month-end processing runs on the first day of the month
• Determine the week number in the year of the previous month-end processing (@LAST_MONTH_END_WEEK) and the next month-end processing (@NEXT_MONTH_END_WEEK)
• If the (@CURRENT_WEEK) is equal to either month-end processing week then do not run the report
• If the (@CURRENT_WEEK) is not equal to either month-end processing week then do run the report

When I used this stored procedure, it was only executed if the current day was Monday and there was no option to override the value of the @TODAY variable; it was always the current date. The value of the @CURRENT_WEEK variable could never be outside the range of the @LAST_MONTH_END_WEEK and the @NEXT_MONTH_END_WEEK.  There is no check for the error conditions in the stored procedure above. I only added the ability to override the @TODAY variable so I could show the result of run the report and not run the report based on the dates I passed in.

DECLARE @RUN_REPORT INT;
EXEC [dbo].[CHECK_RUN_WEEKLY_REPORT] @P_TODAY = '20200323', @P_RUN_REPORT = @RUN_REPORT OUTPUT;
SELECT @RUN_REPORT AS [20200323];
EXEC [dbo].[CHECK_RUN_WEEKLY_REPORT] @P_TODAY = '20200330', @P_RUN_REPORT = @RUN_REPORT OUTPUT;
SELECT @RUN_REPORT AS [20200330];

The following sample code tests the stored procedure:

The main points are:

• Pass in Monday, March 23, 2020 and the stored procedure returns 1 (i.e. run the weekly report)
• Pass in Monday, March 30, 2020 and the stored procedure returns 0 (i.e. do not run the weekly report)

You can use the Execute SQL Task in an SSIS package to execute the above stored procedure and use a precedence constraint to conditionally launch the SQL Server Agent job.

The following screenshots show the addition of checking whether to run the report:

The main points are:

• Declare the SSIS variable RUN_REPORT as an Int32 type in the SSIS package
• CHECK RUN WEEKLY REPORT is an Execute SQL Task that executes the CHECK_RUN_WEEKLY_REPORT stored procedure; the RUN_REPORT variable gets set to the value of the @P_RUN_REPORT output parameter
• There is a precedence constraint on LAUNCH WEEKLY SALES SUBSCRIPTION; CHECK RUN WEEKLY REPORT must run successfully and the value of the RUN_REPORT variable must be 1

Here is the precedence constraint:

The CHECK RUN WEEKLY REPORT Execute SQL Task executes the CHECK_RUN_WEEKLY_REPORT stored procedure with this SQL Statement:

EXEC [dbo].[CHECK_RUN_WEEKLY_REPORT]  @P_RUN_REPORT = ? OUTPUT

Here is the Parameter Mapping which assigns the @P_RUN_REPORT output parameter value to the RUN_REPORT SSIS variable:

I’m using the OLE DB Connection manager so the Parameter Name is just the number of the positional parameter (i.e. the ‘?’) in the SQL Statement.

Next Steps

I have accomplished my goal of showing how to launch a SQL Server Agent Job from an SSIS package and how to create some custom logic to supplement the built-in scheduling capabilities.

The following are the next steps to continue your learning:

• Check your SQL Agent jobs and SSRS report subscriptions to see if there are dependencies that limit your ability to schedule them to run at fixed times.
• Start launching SQL Agent jobs and/or SSRS report subscriptions in lieu of scheduling them where appropriate.
• Download the code from this tip here and experiment.