Making your SQL Agent job scripts generic for all servers (SQL Server Series)

Introduction

If you schedule jobs and you are using SQL Server (not Express Edition) SQL Agent is your best friend. I think it is easier to use than Windows Scheduler for most use cases.
The GUI that SQL Server Management Studio has for creating and editing SQL Agent Jobs is also quite good, I think.

Problem

However, after the agent jobs are created, the SQL Agent Job scripting done by SQL Server Management Studio has a number of issues (Microsoft developers of the SQL Server team, please read!).
Where shall I start ..

  • I like to write repeatable scripts, that raise an error only when something is really wrong, so when the script is being executed as expected, no errors are thrown. The generated scripts however use brute force to delete any existing jobs. If they do not exist yet, an error occurs. This is not what I would call a real error, because it is logical that when a job does not exist yet, it cannot be deleted. A simple IF statement could check if the job exists and delete the job only if it exists.
  • Even worse, the generated scripts use the @job_id, a unique identifier, to delete the job. By definition, this will work only once! After the job is (re)created, the job_id will be different! It would be much better to use the @job_name as parameter for sp_delete_job (yes this is possible), it is just that the SQL Server developer team made the choice to use the @job_id .. 🙁
  • Because scripts are always delete and (re)create, instead of alter/modify, your entire job history is gone, when you change anything in your script and execute it on your server (sorry, no workaround for this).
  • Any (SQL) Server specific values, like SSIS Environment IDs and SQLInstance names, can make your scripts only suitable for one server, the one you created the job on with the SSMS GUI.

Solution

Given all the challenges above, I was looking for a way so:

  • I could create the SQL Agent jobs with the SSMS GUI.
  • Then script the jobs and put them under source control.
  • Then also make sure I can install the SQL Agent jobs on a different server (development/test/production) with the same (nearly) unmodified script.

For this to work, we are again visiting the system objects area of the msdb database.

The plan is roughly as follows:

  1. Create a wrapper stored procedure for sp_create_jobstep.
  2. Create a generic install script that partially is manual labour, that you have to do only once, and partially contains a script generated by SSMS for creating jobs.

Create a wrapper stored procedure for sp_create_jobstep.

This wrapper stored procedure, called [dbo].[usp_add_jobstep_wrapper] consists of the following three parts:

  • PART 1: Check if temp table #jobstep_replacements exists with project-/customer specific replacements: this temporary table can be created in a script that creates jobsteps and contains info about textual replacements to be done in any (N)VARCHAR parameter of the stored procedure sp_create_jobstep. However it would be neater to pass a table variable as parameter to the stored procedure, this would make adjusting scripted jobs more work, because a parameter would have to be added to the EXECUTE [dbo].[usp_add_jobstep_wrapper] command.
  • PART 2: Fix environment references in ssis commands: a feature that is open for improvement in a future SQL Server Service Pack is that when you create a jobstep that executes a SSIS Package, and you use a reference to an environment, in the script a technical ID is used, that is most likely to be different on another server. This is also not very easy to fix manually, or you have to look up all the new id’s with a query, and then change this in the script. Good as occupational therapy, but not so good for your productivity.
    So this part of the stored procedure fixes this /ENVREFERENCE for you.
  • PART 3: After doing replacements in parameter values, and fixing the /ENVREFERENCE, the built-in stored procedure sp_add_jobstep is executed.
    This implies that we simply can execute [dbo].[usp_add_jobstep_wrapper] instead of [dbo].[sp_add_jobstep] , and PART 1 and 2 will be done extra.

And here it is (can also be downloaded).

msdb.dbo.usp_add_jobstep_wrapper.sql

--\
---) hansmichiels.com [msdb].[dbo].[usp_add_jobstep_wrapper]
---) Author: Hans Michiels
---) Stored procedure that can help to make SQL Server Agent job scripts usable for multiple servers.
--/
/*
(c) Copyright 2016 - hansmichiels.com
 
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
 
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
 
You should have received a copy of the GNU General Public License
along with this program. If not, see http://www.gnu.org/licenses/.
*/

USE [msdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_add_jobstep_wrapper]') AND type in (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_add_jobstep_wrapper]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[usp_add_jobstep_wrapper]
  @job_id UNIQUEIDENTIFIER = NULL, -- Must provide either this or job_name
  @job_name sysname = NULL, -- Must provide either this or job_id
  @step_id INT = NULL, -- The proc assigns a default
  @step_name sysname,
  @subsystem NVARCHAR(40) = N'TSQL',
  @command NVARCHAR(max) = NULL,
  @additional_parameters NVARCHAR(max) = NULL,
  @cmdexec_success_code INT = 0,
  @on_success_action TINYINT = 1, -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
  @on_success_step_id INT = 0,
  @on_fail_action TINYINT = 2, -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
  @on_fail_step_id INT = 0,
  @server sysname = NULL,
  @database_name sysname = NULL,
  @database_user_name sysname = NULL,
  @retry_attempts INT = 0, -- No retries
  @retry_interval INT = 0, -- 0 minute interval
  @os_run_priority INT = 0, -- -15 = Idle, -1 = Below Normal, 0 = Normal, 1 = Above Normal, 15 = Time Critical)
  @output_file_name NVARCHAR(200) = NULL,
  @flags INT = 0, -- 0 = Normal,
                                                     -- 1 = Encrypted command (read only),
                                                     -- 2 = Append output files (if any),
                                                     -- 4 = Write TSQL step output to step history,
                                                     -- 8 = Write log to table (overwrite existing history),
                                                     -- 16 = Write log to table (append to existing history)
                                                     -- 32 = Write all output to job history
                                                     -- 64 = Create a Windows event to use as a signal for the Cmd jobstep to abort
  @proxy_id INT = NULL,
  @proxy_name sysname = NULL,
  -- mutual exclusive; must specify only one of above 2 parameters to
  -- identify the proxy.
  @step_uid UNIQUEIDENTIFIER = NULL OUTPUT
AS
BEGIN
  DECLARE @retval INT

  DECLARE @sort_order INT
  DECLARE @max_sort_order INT
  DECLARE @subsystems NVARCHAR(128)
  DECLARE @replace_scope NVARCHAR(128)
  DECLARE @scripted_value NVARCHAR(128)
  DECLARE @replace_value NVARCHAR(128)
  DECLARE @message NVARCHAR(MAX)
  DECLARE @divider_length INT = 120

  DECLARE @folder_name NVARCHAR(128)
  DECLARE @project_name NVARCHAR(128)
  DECLARE @startpos INT
  DECLARE @endpos INT
  DECLARE @endpos_project INT
  DECLARE @reference_id INT = -1
  DECLARE @new_reference NVARCHAR(128)
  DECLARE @new_command NVARCHAR(MAX)
  
  PRINT ' ';
  PRINT REPLICATE('-', @divider_length);
  PRINT 'Stored proc : [usp_add_jobstep_wrapper]'
  PRINT 'Copyright : (c) 2016 - hansmichiels.com'
  PRINT 'License : GNU General Public License, see http://www.gnu.org/licenses/'

  IF @job_name IS NOT NULL
  BEGIN
  -- PRINT 'Stored proc:
    SELECT @message = 'Job : ' + @job_name;
  END ELSE BEGIN
    SELECT @message = 'Job : ' + ISNULL((SELECT TOP 1 j.name FROM dbo.sysjobs j WHERE job_id = @job_id), N'(unknown)');
  END
  PRINT @message;
  SELECT @message = 'Jobstep : ' + @step_name;
  PRINT @message;
  PRINT ' ';

  --\---------------------------------------------------------------------------------------------------------
  ---) PART 1: Check if temp table #jobstep_replacements exists with project-/customer specific replacements.
  --/---------------------------------------------------------------------------------------------------------

  IF OBJECT_ID('tempdb..#jobstep_replacements', 'U') IS NULL
  BEGIN

    PRINT 'No jobstep_replacements were found, installing original script.';

  END ELSE BEGIN

    PRINT 'If any replacements are made, they will be shown below.';

    DECLARE replacements_cursor CURSOR LOCAL STATIC FOR
    SELECT TOP (2147483647) -- Otherwise ORDER BY might not work.
        CASE
          WHEN ISNULL(v.[subsystems], N'') IN (N'*', N'')
          THEN N'*'
          ELSE N',' + v.[subsystems] + N','
          END AS [subsystems],
        CASE
          WHEN ISNULL(v.[replace_scope], N'') IN (N'*', N'')
          THEN N'*'
          ELSE N',' + v.[replace_scope] + N','
          END AS [replace_scope],
        v.[scripted_value],
        v.[replace_value]
    FROM
        #jobstep_replacements v
    ORDER BY
        v.sort_order,
        v.id;
              
    OPEN replacements_cursor;

    FETCH NEXT FROM replacements_cursor
      INTO @subsystems, @replace_scope, @scripted_value, @replace_value;

    WHILE @@FETCH_STATUS = 0
    BEGIN

      IF (@subsystems = N'*' OR CHARINDEX(N',' + @subsystem + N',', @subsystems, 1) > 0)
          AND @replace_value IS NOT NULL
          AND @scripted_value != @replace_value
      BEGIN

        IF (@replace_scope = N'*' OR CHARINDEX(N',@command,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @command, 1) > 0
        BEGIN
            SET @message = 'Replacement in @command: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @command: ' + @command;
            SELECT @command = REPLACE(@command, @scripted_value, @replace_value);
            PRINT 'New @command: ' + @command;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@additional_parameters,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @additional_parameters, 1) > 0
        BEGIN
            SET @message = 'Replacement in @additional_parameters: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @additional_parameters: ' + @additional_parameters;
            SET @additional_parameters = REPLACE(@additional_parameters, @scripted_value, @replace_value);
            PRINT 'New @additional_parameters: ' + @additional_parameters;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@server,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @server, 1) > 0
        BEGIN
            SET @message = 'Replacement in @server: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @server: ' + @server;
            SET @server = REPLACE(@server, @scripted_value, @replace_value);
            PRINT 'New @server: ' + @server;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@database_name,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @database_name, 1) > 0
        BEGIN
            SET @message = 'Replacement in @database_name: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @database_name: ' + @database_name;
            SET @database_name = REPLACE(@database_name, @scripted_value, @replace_value);
            PRINT 'New @database_name: ' + @database_name;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@database_user_name,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @database_user_name, 1) > 0
        BEGIN
            SET @message = 'Replacement in @database_user_name: ' + @scripted_value + ' by ' + @replace_value;
            PRINT REPLICATE('', @divider_length);
            PRINT @message;
            PRINT 'Old @database_user_name: ' + @database_user_name;
            SET @database_user_name = REPLACE(@database_user_name, @scripted_value, @replace_value);
            PRINT 'New @database_user_name: ' + @database_user_name;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@proxy_name,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @proxy_name, 1) > 0
        BEGIN
            SET @message = 'Replacement in @proxy_name: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @proxy_name: ' + @proxy_name;
            SET @proxy_name = REPLACE(@proxy_name, @scripted_value, @replace_value);
            PRINT 'New @proxy_name: ' + @proxy_name;
        END

        IF (@replace_scope = N'*' OR CHARINDEX(N',@output_file_name,', @replace_scope, 1) > 0) AND CHARINDEX(@scripted_value, @output_file_name, 1) > 0
        BEGIN
            SET @message = 'Replacement in @output_file_name: ' + @scripted_value + ' by ' + @replace_value;
            PRINT ' '
            PRINT @message;
            PRINT 'Old @output_file_name: ' + @output_file_name;
            SET @output_file_name = REPLACE(@output_file_name, @scripted_value, @replace_value);
            PRINT 'New @output_file_name: ' + @output_file_name;
        END

      END

      FETCH NEXT FROM replacements_cursor
        INTO @subsystems, @replace_scope, @scripted_value, @replace_value;
      
    END

    CLOSE replacements_cursor
    DEALLOCATE replacements_cursor
          
  END

  --\---------------------------------------------------------------------------------------------------------
  ---) PART 2: Fix environment references in ssis commands.
  --/---------------------------------------------------------------------------------------------------------
         
  --\
  ---) First check if there is something to do
  --/
  IF @subsystem = N'SSIS' AND CHARINDEX(N'/ENVREFERENCE', @command, 1) > 0 AND CHARINDEX(N'/ISSERVER “\”\SSISDB\', @command, 1) > 0
  BEGIN
    --\
    ---) Pull out @folder_name and @project_name from the @command variable value
    --/
    SELECT
      @startpos = CHARINDEX(N'/ISSERVER “\”\SSISDB\', @command, 1) + LEN(N'/ISSERVER “\”\SSISDB\'),
      -- @endpos = CHARINDEX(N'dtsx\””', @command, @startpos),
      @endpos = CHARINDEX(N'\', @command, @startpos + 1),
      @endpos_project = CHARINDEX(N'\', @command, @endpos + 1),
      @folder_name = SUBSTRING(@command, @startpos, @endpos - @startpos),
      @project_name = SUBSTRING(@command, @endpos + 1, @endpos_project - @endpos - 1);

      --\
      ---) Armed with the correct @folder_name and @project_name get the environment reference id.
      --/
      SELECT @reference_id = ISNULL((
          SELECT TOP 1
              er.reference_id
          FROM
              SSISDB.catalog.environments AS env
          JOIN
              SSISDB.catalog.folders AS fld
              ON fld.folder_id = env.folder_id
          JOIN
              SSISDB.catalog.projects AS prj
              ON prj.folder_id = fld.folder_id
          JOIN
              SSISDB.catalog.environment_references AS er
              ON er.project_id = prj.project_id
          WHERE
              fld.name = @folder_name
              AND prj.name = @project_name
      ), -1)

  END

  --\
  ---) If a valid environment reference id was found ..
  --/
  IF @reference_id != -1
  BEGIN
    SELECT
      --\
      ---) .. adjust the /ENVREFERENCE part of the @command so that it is followed by the retrieved @reference_id.
      --/
      @startpos = CHARINDEX(N'/ENVREFERENCE ', @command, 1),
      @endpos = CHARINDEX(N' ', @command, @startpos + + LEN(N'/ENVREFERENCE ') + 1);

      SELECT
          @new_reference = '/ENVREFERENCE ' + CONVERT(NVARCHAR, @reference_id),
          @new_command = LEFT(@command, @startpos - 1) + @new_reference + SUBSTRING(@command, @endpos, 8000);
      IF @new_command != @command
      BEGIN
          SET @message = 'Replacement in @command: fixing /ENVREFERENCE';
          PRINT ' '
          PRINT @message;
          PRINT 'Old @command: ' + @command;
          PRINT 'New @command: ' + @new_command;
          SET @command = @new_command;
      END
  END

  --\---------------------------------------------------------------------------------------------------------
  ---) PART 3: Now we have done our trick with the parameter values,
  ---) execute the built-in stored procedure sp_add_jobstep.
  --/---------------------------------------------------------------------------------------------------------

  EXECUTE @retval = dbo.sp_add_jobstep
      @job_id = @job_id,
      @job_name = @job_name,
      @step_id = @step_id,
      @step_name = @step_name,
      @subsystem = @subsystem,
      @command = @command,
      @additional_parameters = @additional_parameters,
      @cmdexec_success_code = @cmdexec_success_code,
      @on_success_action = @on_success_action,
      @on_success_step_id = @on_success_step_id,
      @on_fail_action = @on_fail_action,
      @on_fail_step_id = @on_fail_step_id,
      @server = @server,
      @database_name = @database_name,
      @database_user_name = @database_user_name,
      @retry_attempts = @retry_attempts,
      @retry_interval = @retry_interval,
      @os_run_priority = @os_run_priority,
      @output_file_name = @output_file_name,
      @flags = @flags,
      @proxy_id = @proxy_id,
      @proxy_name = @proxy_name,
      @step_uid = @step_uid OUTPUT

  RETURN(@retval)
END
GO

Create a generic install script

This install script consists of the following parts:

PART 1: Create temporary table #jobstep_replacements with specific replacements.

This is the part where you customize for your own environments.
Two things require further explanation: subsystems and replace-scope.
Each jobstep is defined for a specific subsystem. Valid values are:

Full name Code
Operating system (CmdExec) CmdExec
PowerShell PowerShell
Replication Distributor Distribution
Replication Merge Merge
Replication Queue Reader QueueReader
Replication Snapshot Snapshot
SQL Server Analysis Services Command ANALYSISCOMMAND
SQL Server Analysis Services Query ANALYSISQUERY
SQL Server Integation Services Package SSIS
Transact-SQL script (T-SQL) TSQL

The subsystem codes are used in SQL scripts generated by SSMS to create jobsteps.

Replace scopes is not an official term, I just called it that way.
Valid values are any (n)varchar parameter names of sp_add_jobstep, so:

  • @command
  • @additional_parameters
  • @server
  • @database_name
  • @database_user_name
  • @proxy_name
  • @output_file_name

The temporary table #jobstep_replacements contains the columns [subsystems] and [replace_scope].
Both can be filled with a commaseparated list to control the subsystems and parameters where the replacement should take place. A * wildcard means “all subsystems” or “all parameters”.

An example of part 1:

MSSQL-E08-jobscript-part1

--\------------------------------------------------------------------------------------
---) PART 1: Create temporary table #jobstep_replacements with specific replacements.
--/------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#jobstep_replacements', 'U') IS NOT NULL
DROP TABLE #jobstep_replacements;

IF @@SERVERNAME != 'VWS63-SQL161' -- The server where the script was generated.
BEGIN
    CREATE TABLE #jobstep_replacements
    --\------------------------------------------------------------------------------------
    ---) IMPORTANT; do not change the structure of this temporary table because
    ---) stored procedure [usp_add_jobstep_wrapper] depends on it.
    --/------------------------------------------------------------------------------------
    (
    [id] INT IDENTITY(1, 1),
    [subsystems] NVARCHAR(128) NOT NULL, -- Use wildcard * or comma separated list with subsystem codes e.g. SSIS,TSQL (don't use spaces!)
    /*
    SUBSYSTEMS
    -------------------------------------- ---------------------
    Full name Code
    -------------------------------------- ---------------------
    Operating system (CmdExec) CmdExec
    PowerShell PowerShell
    Replication Distributor Distribution
    Replication Merge Merge
    Replication Queue Reader QueueReader
    Replication Snapshot Snapshot
    SQL Server Analysis Services Command ANALYSISCOMMAND
    SQL Server Analysis Services Query ANALYSISQUERY
    SQL Server Integation Services Package SSIS
    Transact-SQL script (T-SQL) TSQL
    */

    [replace_scope] NVARCHAR(128) NOT NULL, -- Use wildcard * or comma separated list with subsystem codes e.g. @command,@proxy_name (don't use spaces!)
    /*
    --------------------------------------
    REPLACE-SCOPES
    --------------------------------------
    @command
    @additional_parameters
    @server
    @database_name
    @database_user_name
    @proxy_name
    @output_file_name
    */

    [scripted_value] NVARCHAR(128) NOT NULL,
    [replace_value] NVARCHAR(128) NULL,
    [sort_order] INT NOT NULL DEFAULT(100)
    );
END

--\
---) Fill the temporary table, based on the current SQL Server Instance name.
---) Include the server on which the scripts were made in one IF statement, so the records can be updated later.
--/
IF @@SERVERNAME = 'VWS63-SQL161\TABULAR'
BEGIN
    INSERT INTO #jobstep_replacements
    ( [sort_order], [subsystems], [replace_scope], [scripted_value], [replace_value])
    VALUES
      (1, N'SSIS',
          N'@command',
          N'VWS63-SQL161\”',
          N'VWS63-SQL161\TABULAR\”'),

      (2, N'SSIS',
          N'@command',
          N'DEV',
          N'TST'),

     (3, N'TSQL',
          N'@command,@database_name',
          N'Demo_DEV',
          N'Demo_TST'),

     (4, N'PowerShell',
          N'@command',
          N'F:\Files\Development',
          N'F:\Files\Test')
END
ELSE IF @@SERVERNAME = 'PRODSERVER'
BEGIN
    INSERT INTO #jobstep_replacements
    ( [sort_order], [subsystems], [replace_scope], [scripted_value], [replace_value])
    VALUES
      (1, N'SSIS',
          N'@command',
          N'VWS63-SQL161\”',
          N'VWS63-SQL163\PRD\”'),

      (2, N'SSIS',
          N'@command',
          N'DEV',
          N'PRD'),

     (3, N'TSQL',
          N'@command,@database_name',
          N'Demo_DEV',
          N'Demo_PRD'),

     (4, N'PowerShell',
          N'@command',
          N'F:\Files\Development',
          N'F:\Files\Production')
END

PART 2: Smart sp_delete_job statements, using @job_name and IF EXISTS.

Those statements can be generated using a SQL query. This query is available as comment in the MSSQL-E08-jobscript.sql script (download the scripts here). Just select it and execute. Then you copy and paste from the query output window.

An example of part 2:

MSSQL-E08-jobscript-part2

--\------------------------------------------------------------------------------------
---) PART 2: Smart sp_delete_job statements, using @job_name and IF EXISTS
--/------------------------------------------------------------------------------------

USE [msdb]
GO
/* -- Query to generate sp_delete_job statements:
SELECT
    CASE sql.line_no
      WHEN 1 THEN 'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = N'''+ name + ''')'
      WHEN 2 THEN ' EXEC msdb.dbo.sp_delete_job @job_name=N'''+ name + ''', @delete_unused_schedule=1;'
      WHEN 3 THEN 'GO'
    END AS [drop_sql_for_copy_and_paste]
FROM
    msdb.dbo.sysjobs j
CROSS JOIN -- To force new lines in the output, a cross join with 3 line_no rows is used.
    (SELECT 1 AS [line_no] UNION SELECT 2 UNION SELECT 3) AS [sql]
WHERE
    j.name like 'MSSQL-E08%' -- IMPORTANT: here you filter on the jobs to create delete statements for.
ORDER BY
    j.name,
    sql.line_no
*/

IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = N'MSSQL-E08-Job1-SSIS')
  EXEC msdb.dbo.sp_delete_job @job_name=N'MSSQL-E08-Job1-SSIS', @delete_unused_schedule=1;
GO
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = N'MSSQL-E08-Job2-TSQL')
  EXEC msdb.dbo.sp_delete_job @job_name=N'MSSQL-E08-Job2-TSQL', @delete_unused_schedule=1;
GO
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE [name] = N'MSSQL-E08-Job3-Powershell')
  EXEC msdb.dbo.sp_delete_job @job_name=N'MSSQL-E08-Job3-Powershell', @delete_unused_schedule=1;
GO

PART 3: Create jobs, script generated by SSMS.

Here you paste the script to create jobs that was generated by SQL Server Management Studio. After pasting, replace sp_add_jobstep by usp_add_jobstep_wrapper.

An example of part 3:

MSSQL-E08-jobscript-part3

--\------------------------------------------------------------------------------------
---) PART 3: Create jobs, script generated by SSMS
--/------------------------------------------------------------------------------------
--\
---) IMPORTANT NOTE: You can generate the part below as follows:
---) In SQL Server Management Studio select a SQL Agent job, then press F7
---) In the Object Explorer Details Pane, select the jobs you want to create a script for.
---) Then rightclick and in the context menu select:
---) > Script Job As > CREATE To > New Query Editor Window
---) In the script generated with SQL Server Management Studio, replace
---) sp_add_jobstep
---) by
---) usp_add_jobstep_wrapper
--/
USE [msdb]
GO

/****** Object: Job [MSSQL-E08-Job1-SSIS] Script Date: 19-12-2016 18:59:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 19-12-2016 18:59:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MSSQL-E08-Job1-SSIS',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'VWS63-SQL161\Hans', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [AnyPackage.dtsx] Script Date: 19-12-2016 18:59:58 ******/
EXEC @ReturnCode = msdb.dbo.usp_add_jobstep_wrapper @job_id=@jobId, @step_name=N'AnyPackage.dtsx',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'SSIS',
  @command=N'/ISSERVER “\”\SSISDB\DEV\SSIS-E10-P2\AnyPackage.dtsx\”” /SERVER “\”VWS63-SQL161\”” /ENVREFERENCE 4 /Par “\”$ServerOption::LOGGING_LEVEL(Int16)\””;1 /Par “\”$ServerOption::SYNCHRONIZED(Boolean)\””;True /CALLERINFO SQLAGENT /REPORTING E',
  @database_name=N'master',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule for MSSQL-E08-Job1-SSIS',
  @enabled=0,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20161216,
  @active_end_date=99991231,
  @active_start_time=60000,
  @active_end_time=235959,
  @schedule_uid=N'6d24a58d-f800-4341-ab5b-41fbe4923da8'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

/****** Object: Job [MSSQL-E08-Job2-TSQL] Script Date: 19-12-2016 18:59:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 19-12-2016 18:59:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MSSQL-E08-Job2-TSQL',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'VWS63-SQL161\Hans', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Demo_DEV] Script Date: 19-12-2016 18:59:58 ******/
EXEC @ReturnCode = msdb.dbo.usp_add_jobstep_wrapper @job_id=@jobId, @step_name=N'Demo_DEV',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'SELECT * from sys.objects',
  @database_name=N'Demo_DEV',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule for MSSQL-E08-Job2-TSQL',
  @enabled=0,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20161216,
  @active_end_date=99991231,
  @active_start_time=70000,
  @active_end_time=235959,
  @schedule_uid=N'a25c43d5-8543-4723-903b-beeb6d9a07a3'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

/****** Object: Job [MSSQL-E08-Job3-Powershell] Script Date: 19-12-2016 18:59:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 19-12-2016 18:59:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MSSQL-E08-Job3-Powershell',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'No description available.',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'VWS63-SQL161\Hans', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Powershell step] Script Date: 19-12-2016 18:59:59 ******/
EXEC @ReturnCode = msdb.dbo.usp_add_jobstep_wrapper @job_id=@jobId, @step_name=N'Powershell step',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'PowerShell',
  @command=N'dir “F:\Files\Development\*.csv”',
  @database_name=N'master',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule for MSSQL-E08-Job3-Powershell',
  @enabled=0,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20161216,
  @active_end_date=99991231,
  @active_start_time=110000,
  @active_end_time=235959,
  @schedule_uid=N'a6f0e0ca-d27a-4d3f-a349-4a53c0392541'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

It’s demo time ..

It is also time to set up a demo, otherwise you have to believe me that this works, and I always like to prove it.
For this I have deployed two SSIS projects from an earlier blog post to two SQL Instances on my virtual server. One instance is the default instance MSSQLSERVER and the other one is called TABULAR (it has SSAS installed in TABULAR mode).
For the demo I will pretend however that the default instance is my development environment and the TABULAR instance is my test.
On the default instance I create three SQL Agent jobs for SSIS-, TSQL and PowerShell jobsteps, respectively.

Jobs created for the demo ..

Then I create a script from the jobs, paste it into script MSSQL-E08-jobscript.sql and replace sp_add_jobstep by usp_add_jobstep_wrapper.

Generating a CREATE script for the jobs ..

After doing the necessary preparation to install usp_add_jobstep_wrapper on both instances, I can create the SQL Agent jobs on the TABULAR instance.
The output window shows which replacements were done.

As you can see in the output window, multiple replacements will be applied one after the other ..

Replacement in the TSQL jobstep ..

Replacement in the PowerShell jobstep..

I can still use the script on the default instance, on that instance simply no replacements will be done.

No replacements done on the default instance where the CREATE script was generated ..

Download the scripts here.

Conclusion / Wrap up

In this blog post you could read how to cope with some peculiarities of SQL Server Management Studio when it creates SQL Agent job scripts.
I have explained how you can make your script generic for all environments, which makes it more suitable for putting it under source control.
For this a special stored procedure, [dbo].[usp_add_jobstep_wrapper] is installed in the [msdb] database and a generic install script must be made that is partly developed and partly pasted from the SSMS Script.

And, of course, have a merry Christmas and a happy New Year!

(c) 2016 hansmichiels.com – Do not steal the contents – spread the link instead – thank you.

Hans Michiels

Hans Michiels

Hans is an Independent Business Intelligence and Datawarehouse Consultant & Microsoft SQL Server Consultant, working in the Netherlands. He has been working in the software industry since 1996, with SQL Server since the year 2001, and since 2008 he has a primary focus on datawarehouse- and business intelligence projects using Microsoft technology, using a Datavault and Kimball architecture. He has a special interest in Datawarehouse Automation and Metadata driven solutions. * Certified in Data Vault Modeling: Certified Data Vault 2.0 Practitioner, CDVDM (aka Data Vault 1.0) * Certified in MS SQL Server: * MCSA (Microsoft Certified Solutions Associate) SQL Server 2012 - MCITP Business Intelligence Developer 2005/2008 - MCITP Database Developer 2005/2008 - MCITP Database Administrator 2005/2008

More Posts

5 thoughts on “Making your SQL Agent job scripts generic for all servers (SQL Server Series)”

  1. This looks great Hans, and solves some of the problem we’ve been having.

    You say:
    Because scripts are always delete and (re)create, instead of alter/modify, your entire job history is gone, when you change anything in your script and execute it on your server (sorry, no workaround for this).

    But can’t we instead use sp_update_jobstep and sp_update_job to avoid deleting and creating the jobsteps?

    I’m going to give this a go and will share my results if I get it working. Because losing history is a major annoyance for us, especially as we move to regular releases of agent jobs.

    Thanks,
    Simon

    1. Hello Simon,
      This is a good suggestion and I am curious about your findings, please keep me posted!
      Best regards,
      Hans

  2. Just today I used the SMO assembly from Powershell to generate job-creation scripts that I used to create jobs on several servers. It went fine and the jobs run. Did I miss anything?

    1. Hi Derek, thanks for your feedback. That is probably a better way, never to old to learn! Thanks. I will try it when I can.

Leave a Reply

Your email address will not be published. Required fields are marked *