Sunday, August 4, 2013

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
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')
      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 :

  1. Staging_Data_Population:
  2. 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