--\
---) 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