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.
to all the existing jobs or group of jobs available
in SQL Server instance.
********************************************************************************/
USE msdb
GO
--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')
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
--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',
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',
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
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-------------------------------
No comments:
Post a Comment