DECLARE @jobname VARCHAR(128),
@jobdescription VARCHAR(512),
@step_name VARCHAR(128),
@command VARCHAR(MAX) = 'dtloge',
@database VARCHAR(128),
@subsystem VARCHAR(128),
@WildChar BIT = 0
SELECT DISTINCT
'PRINT ''JOB:' + REPLACE(j.NAME, '''','''''') + '''
EXEC msdb.dbo.sp_update_jobstep
@job_id = ''' + CAST(ST.JOB_ID AS VARCHAR(128)) + ''', -- uniqueidentifier
@step_id = ' + CAST(ST.step_id AS VARCHAR) + ', -- int
@subsystem = N''SSIS'', -- nvarchar(40)
@command = N''' + LTRIM(RTRIM(REPLACE(command, 'C:\apps\Applications\dtlogexec.exe', ''))) + ' /CHECKPOINTING OFF /REPORTING E''
GO'
FROM msdb.dbo.sysjobsteps st (NOLOCK)
INNER JOIN msdb.dbo.sysjobs AS j (NOLOCK) ON j.job_id = st.job_id
CROSS APPLY (SELECT CAST(SUM(Counter) AS VARCHAR) + '-' + Util.dbo.StringConcat(subsystem + ' (' + CAST(counter AS VARCHAR) + ')', ', ') JobSubsystemList
FROM (SELECT s2.subsystem,
COUNT(*) AS Counter
FROM msdb.dbo.sysjobsteps s2 (NOLOCK)
WHERE s2.Job_id = st.job_id
GROUP BY s2.subsystem) b) k
WHERE j.enabled = 1
AND (@jobname IS NULL
OR j.name LIKE @jobname
OR CHARINDEX(@jobname, j.name, 0) > 0)
AND (@jobdescription IS NULL
OR (@WildChar = 1
AND j.description LIKE @jobdescription)
OR (@WildChar = 0
AND CHARINDEX(@jobdescription, j.description, 0) > 0))
AND (@step_name IS NULL
OR (@WildChar = 1
AND st.step_name LIKE @step_name)
OR (@WildChar = 0
AND CHARINDEX(@step_name, st.step_name, 0) > 0))
AND (@command IS NULL
OR (@WildChar = 1
AND st.command LIKE @command)
OR (@WildChar = 0
AND CHARINDEX(@command, st.command, 0) > 0))
AND (@database IS NULL
OR (@WildChar = 1
AND st.database_name LIKE @database)
OR (@WildChar = 0
AND CHARINDEX(@database, st.database_name, 0) > 0))
AND (@subsystem IS NULL
OR (@WildChar = 1
AND st.subsystem LIKE @subsystem)
OR (@WildChar = 0
AND CHARINDEX(@subsystem, st.subsystem, 0) > 0))