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:
- Create a wrapper stored procedure for sp_create_jobstep.
- 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).
--\
---) 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:
--\------------------------------------------------------------------------------------
---) 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:
--\------------------------------------------------------------------------------------
---) 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:
--\------------------------------------------------------------------------------------
---) 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.
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
Hello Simon,
This is a good suggestion and I am curious about your findings, please keep me posted!
Best regards,
Hans
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?
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.