Generally we create SQL Server Jobs to execute some SQL scripts, SSIS packages or power shell commands when user calls job manually or call automatically by Scheduler. Sometimes we need to change SQL server Job steps script dynamically based on some dynamic values. Now let’s see, how to change SQL server Job steps command dynamically
To show this, I have taken example of SQL Job step with T-SQL Command. Before this I have created one simple stored procedure in ADVENTUREWORKSDW2012 DB to insert values into DIMPRODUCT table.
GO
CREATE PROC DBO.INSERTINTODIMPRODUCT
(
@PRODUCTNAME VARCHAR(100),@COLOR VARCHAR(100)
)
AS
BEGIN
INSERT INTO [ADVENTUREWORKSDW2012].[DBO].[DIMPRODUCT]
(
ENGLISHPRODUCTNAME,SPANISHPRODUCTNAME,FRENCHPRODUCTNAME,FINISHEDGOODSFLAG,COLOR,STARTDATE
)
SELECT @PRODUCTNAME,@PRODUCTNAME,@PRODUCTNAME,1,@COLOR,GETDATE()
END
We can call above Stored procedure by below
EXEC INSERTINTODIMPRODUCT 'Pen',’Blue'
So same script i am calling from SQL Server Agent Job by creating Job name Update Job Step and Step Insert values dynamically to insert data dynamically
Go to SQL Server Agent in your Database server -->Job and right click and select New Job
Next click on Steps and give name for step and select type of execution as T-SQL . Choose your database to execute your command and write your Script in command window And click on OK and close the Job windows by saving. Now your job is created.
Next go to newly created job and right click and click on Start Job at Step . After completion of job just run the below query
SELECT TOP 10 [PRODUCTKEY],
ENGLISHPRODUCTNAME,SPANISHPRODUCTNAME,FRENCHPRODUCTNAME,FINISHEDGOODSFLAG,COLOR,*
FROM [ADVENTUREWORKSDW2012].[DBO].[DIMPRODUCT]
ORDER BY 1 DESC
Out Put will be like below
Now if we want to change SQL script in step1 , we can update script by using sys stored procedure SP_UPDATE_JOBSTEP.
USE MSDB
GO
DECLARE @JOBNAME VARCHAR(100)='UPDATE JOB STEP'
DECLARE @PRODUCTNAME VARCHAR(100)='Ball'
DECLARE @COLOR VARCHAR(100)='White'
DECLARE @STEP_ID INT=1
DECLARE @CMD VARCHAR(100)='EXEC INSERTINTODIMPRODUCT ' + @PRODUCTNAME +',' + @COLOR
EXEC MSDB.DBO.SP_UPDATE_JOBSTEP @JOB_NAME = @JOBNAME
,@STEP_ID = @STEP_ID
,@COMMAND = @CMD
Now if we open Step1 script in Job, i command script will change like below
Kiran Reddy A
Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL
Get Free Email Updates to your Inbox!
