--\
---) 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
Jobs created for the demo ..
Generating a CREATE script for the jobs ..
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..
No replacements done on the default instance where the CREATE script was generated ..