sp_add_jobstep ssis package
CREATE PROCEDURE [dbo].[CreateAgentjobHourlySSIS]
@job NVARCHAR(128),
@package NVARCHAR(max), -- \SSISDB\MyCatalog\MyProject\MyPackage.dtsx
@params NVARCHAR(max), -- /Par "\"$Project::MyParameter\"";ParameterValue /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True
@servername NVARCHAR(28),
@startdate DATE,
@starttime TIME,
@frequencyhours INT
AS
BEGIN TRY
BEGIN TRAN
--GRANT EXEC on CreateAgentjobHourlySSIS to PUBLIC
--1. Add a job
EXEC msdb.dbo.sp_add_job
@job_name = @job
--2. Add a job step named process step. This step runs the stored procedure
DECLARE @SSIScommand as NVARCHAR(max)
SET @SSIScommand = '/ISSERVER "\"'+@package+'\"" /SERVER "\"'+@servername+'\"" '+@params+' /CALLERINFO SQLAGENT /REPORTING E'
EXEC msdb.dbo.sp_add_jobstep
@job_name = @job,
@step_name = N'process step',
@subsystem = N'Dts',
@command = @SSIScommand
--3. Schedule the job starting at a specified date and time
DECLARE @startdateasint int = YEAR(@startDate)*10000+MONTH(@startdate)*100+DAY(@startdate)
DECLARE @starttimeasint int = DATEPART(HOUR,@starttime)*10000+DATEPART(MINUTE,@starttime)*100+DATEPART(SECOND,@starttime)
EXEC msdb.dbo.sp_add_jobschedule @job_name = @job,
@name = 'Hourly Schedule',
@freq_type = 4, --daily
@freq_interval = 1,
@freq_subday_type = 0x8, -- hourly
@freq_subday_interval = @frequencyhours,
@active_start_date = @startdateasint,
@active_start_time = @starttimeasint
--4. Add the job to the SQL Server
EXEC msdb.dbo.sp_add_jobserver
@job_name = @job,
@server_name = @servername
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT ERROR_Message(), ERROR_Line();
ROLLBACK TRAN
END CATCH