How to automate the changes of parameters in ssis package run by a job? I have a job that runs a package with specific parameters as you can see on the picture below:



I want to set up those parameters (the 2 dates in yellow) to 1900-01-01 instead of 2019-02-27.

I need to automate this as I have a few of those packages to update.

How can I get this done via scripting?
The way I have been doing this is first I get the job_id and the command of the job by using the following query:

select @@servername -- this should be on the same server as the job
exec  msdb.dbo.sp_help_job @job_name = 'Daily DW CA'


that is to get the job_id and the step_id that has the command you want to update 

you can also get that info like this:

note that this could be automated as well, but on this situation it was not necessary.

select * from
msdb.dbo.sysjobs
where name ='Daily DW CA'


then you use the @job_id and step_id in the script below.
the script below will change the dates from 2019-02-27 to 1900-01-01

set nocount on

declare @job_id uniqueidentifier = '90D3F4A2-1AC7-4741-BCCA-9A7B64A69B20'
declare @step_id int = 1
declare @command NVARCHAR(MAX)
declare @sql NVARCHAR(MAX)


--a quick select just to check
    SELECT step_id,
           step_name,
           subsystem,
           command,
           output_file_name,
           proxy_id
    FROM msdb.dbo.sysjobsteps
    WHERE (job_id = @job_id)
      AND ((@step_id IS NULL) OR (step_id = @step_id))
    ORDER BY job_id, step_id

--REPLACE(string, old_string, new_string)

    SELECT @COMMAND = replace(command,'2019-02-27','1900-01-01')
    FROM msdb.dbo.sysjobsteps
    WHERE (job_id = @job_id)
      AND ((@step_id IS NULL) OR (step_id = @step_id))
    ORDER BY job_id, step_id

print @command

SET @SQL =
'
EXEC msdb.dbo.sp_update_jobstep 
@job_id=N''' +  '90d3f4a2-1ac7-4741-bcca-9a7b64a69b20' + ''',' + '
@step_id=1 , ' +
'@command=N''' + @command + '' + char(39) 

print @sql

exec sp_executesql @sql


You can check if all is ok by having a look at the following command:

exec  msdb.dbo.sp_help_job @job_name = 'Daily DW CA'


you can see the desired values in the command shown below: