CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresssis
Scott Duncan
Scott Duncan

Reputation: 1151

Parameterize sp_add_jobstep for SSIS

From a TSQL Stored Procedure, I want to use the sp_add_jobstep stored procedure in the msdb database to create an SQL Agent job, which calls an SSIS package. I need to do this programmatically to dynamically set one of the parameters in the SSIS package at time of Job creation. In the "SQL Server Agent>Jobs>New Job" GUI, this is done under the "Steps>Edit>Configuration>Parameters" screen. How does one assign parameters with the sp_add_jobstep Stored Procedure?

The Microsoft documentation: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-add-jobstep-transact-sql does not explain this.

Related posts:

This post shows how to create an Agent job in T-SQL or C#: Create SQL Server Agent jobs programatically

And this post shows the SSIS syntax but does not discuss parameters: How do I create a step in my SQL Server Agent Job which will run my SSIS package?

Upvotes: 2

Views: 4193

Answers (2)

Scott Duncan
Scott Duncan

Reputation: 1151

As noted in the response by SAS, the params have to be passed as part of the command. The documentation for sp_add_jobstep shows a parameter called @additional_parameters, but notes this is not supported. So, while I didn't script it out (which would have been quicker), I did make an example job & then query the msdb.dbo.sysjobsteps table to see the format of the command. Based on that, and the earlier post by CSharper, I wrote the following stored procedure:

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

Upvotes: 1

SAS
SAS

Reputation: 4035

You construct the call like this:

@command=N'/ISSERVER " 
...
/Par "\"$Project::MyParam\"";ParamValue
...

If you already have a similar job you can right-click in SSMS and script it out. That will show you the syntax.

Upvotes: 0

Related Questions