How to add additional Job step in all the existing jobs in an SQL Server instance

1. Background

SQL Server Agent Jobs play an important role in the process
automation of any application. Apart from Job monitoring, addition of Data
Quality Check steps at the end of the job step is very useful to analyze what
type of data is processed e.g no of records, data discrepancy etc.

Below is the SQL script that adds additional step in all the existing
jobs or a group of jobs. For below
example, I have used T-SQL as Step Type, this can be change based on our
requirement:

/********************************************************************************

** Purpose        :     The Purpose of the script is to add the new
step
                        to all the
existing jobs or group of jobs available
                        in SQL Server
instance.

********************************************************************************/

USE msdb

–Declaring Variables

DECLARE @TotalJobs
INT

DECLARE @Min INT

DECLARE
@ReturnCode INT

DECLARE
@MaxJobStep_id INT

DECLARE
@NextStep_id INT

DECLARE @Job_id NVARCHAR(500)

DECLARE @JobName NVARCHAR(500)

DECLARE
@CommandString NVARCHAR(1000)

–Setting the values in variables

SET
@ReturnCode = 0

SET @Min = 1

–Declaring table variable

DECLARE @JobTable TABLE

(ID INT IDENTITY,

Job_id nvarchar(500),

JobName NVARCHAR(500) )

–Getting all the required jobs

INSERT INTO @JobTable

SELECT job_id, name FROM sysjobs

–Filter if Job step needs to be added in specific set of
jobs

— WHERE NAME IN ( )–
Give jobs name here

— Total count of the job

SELECT @TotalJobs
= @@ROWCOUNT

–Starting process to add the new step

WHILE @TotalJobs
>= @Min

BEGIN

SELECT
@Job_id = job_id,
@JobName = JobName

FROM
@JobTable

WHERE
ID = @Min

SELECT @MaxJobStep_id =
MAX(Step_id)

FROM
sysjobsteps

WHERE
job_id =
@Job_id

IF
NOT EXISTS (SELECT 1 FROM dbo.sysjobsteps WHERE
job_id = @Job_id
AND
step_name = ‘Add_DataQualityCheck’)

BEGIN

–Updating
the status of existing last Step in the Job

UPDATE
sysjobsteps

SET
on_success_action = 3

WHERE
job_id = @Job_id AND
step_id = @MaxJobStep_id

END

SET
@NextStep_id =
@MaxJobStep_id + 1

–Creating
command string for AcquireDataQualityCheck

— If
you want to modify the existing step… Change the coomand
–string here

      SET
@CommandString = ‘EXEC
usp_DataQualityCheck   ‘ + ””+@JobName +
      ””

—
Adding the Job step

GOTO
EndSave

QuitWithRollback:

IF
(@@TRANCOUNT
> 0) ROLLBACK TRANSACTION

EndSave:

IF
NOT EXISTS (SELECT 1 FROM dbo.sysjobsteps WHERE
job_id = @Job_id AND step_name = ‘Add_DataQualityCheck’)

BEGIN

EXEC
@ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@Job_id, @step_name =
N’Add_DataQualityCheck’,

@step_id=@NextStep_id,

@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= @CommandString,

@database_name=N’master’,

@flags=0

IF (@@ERROR <> 0 OR
@ReturnCode <> 0)
GOTO QuitWithRollback

END

ELSE

BEGIN

      EXEC
@ReturnCode = msdb.dbo.sp_update_jobstep @job_id=@Job_id, @step_name =
      N’Add_DataQualityCheck’,

@step_id=@MaxJobStep_id,

@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= @CommandString,

@database_name=N’master’,

@flags=0

IF (@@ERROR <> 0 OR
@ReturnCode <> 0)
GOTO QuitWithRollback

END

SET
@Job_id = NULL

SET
@MaxJobStep_id = NULL

SET
@NextStep_id = NULL

SET
@Min = @Min + 1

END

————————–End
of Script——————————–

2. Script execution and Result:

Following
are the two jobs available in same instance of SQL Server:

Each Job is having two steps :

Staging_Data_Population:
Archive_Data_Population:

Now execute the script, it will add an
additional step as last step in both the jobs. It will also change the
“OnSuccess” & “OnFailure” status of the step. Below is the result:

———————— End of
Article——————————-

Related Posts

Leave a Reply Cancel reply