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

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

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