Answers for "sp_add_jobstep ssis package"

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