Search code examples
c#sql-serverssissql-server-2012smo

Programmatically create SQL Agent Job running SSIS package in C#

I have a SSIS package that is being used to simply transfer data from one database to another on the same server on a SQL Server Agent job. I want to be able to create multiple jobs for different databases at any time so I am trying to create a C# program to create the jobs.

Looking at the Job Step class I cannot find a way to set the type for the step like in the UI: SQL Server Agent Job Step Type

I've tried setting the command to the same form that an already existing job has for calling a SSIS package in the SSIS catalog:

JobStep jobStep = new JobStep(job, "Transfer");
jobStep.Command = @"/ISSERVER ""\SSISDB\ABC\ABC\Transfer.dtsx"" /SERVER FRISCHE /Par ""DestinationConnectionOLEDB_InitialCatalog"";""Dest_Test"" /Par ""SourceConnectionOLEDB_InitialCatalog"";""Src_Test"" /Par ""$ServerOption::LOGGING_LEVEL(Int16)"";1 /Par ""$ServerOption::SYNCHRONIZED(Boolean)"";True /CALLERINFO SQLAGENT /REPORTING E";

But the step that this creates always defaults to the Transact-SQL script option. Is there some way I can tell the JobStep object that I want a type of an SSIS package?

Solution

  • You're looking for the SubSytem. The Enum values are listed here

    Ssis - Specifies the SQL Server Integration Services subsystem.

    Specifically, you want Ssis. Code should be approximately 

    jobStep.SubSystem = AgentSubSystem.Ssis;