SSIS 2012 Automation – Part 3: Create SQL Agent Job which will execute SSIS package from SSIS catalog, Assign Envir...

[Krishnakumar_Rukmangathan]

First published on MSDN on Oct 07, 2013

This third and last part of automation blog, SSIS 2012 Automation – Part 1: Deploy SSIS Project to SSIS Catalog , SSIS 2012 Automation – Part 2: Create environment, map it to project and package level parameters and assign reference to project

In previous two parts we had deployed project to SSIS Catalog and configured it to use environment values.

In this part we will create SQLA gent job which will run this package. We will assign environment reference so that we can change parameter values at runtime and also we will assign schedule to Job.

Scheduling part I have coded only in C# (too lazy to port that to powershell), someone needs powershell version send message will try.

Make sure you are meeting all pre-requisites mentioned in part 1

C# Code:

//Connect to SQL server which has SSIS Package

Server SMO_Demo = new Server("dil\\denali");

//Create Job

Job jb = new Job(SMO_Demo.JobServer, "SQL Agent", 0);

jb.Create();

jb.Category = "[Uncategorized (Local)]";

jb.ApplyToTargetServer("dil\\denali");

jb.Alter();

//Create Job Step, Main part is subsystem

JobStep jbs = new JobStep(jb, "SSIS_Job_CS");

jbs.Create();

jbs.Server = "dil\\denali";

jbs.SubSystem = Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem.Ssis;

jbs.OnFailAction = StepCompletionAction.QuitWithFailure;

jbs.OnSuccessAction = StepCompletionAction.QuitWithSuccess;

//Connect to Integration Service Catalog and load project

IntegrationServices Is_Demo = new IntegrationServices(SMO_Demo);

Catalog ct_demo = Is_Demo.Catalogs["SSISDB"];

CatalogFolder folder = ct_demo.Folders["ExecuteTest"];

ProjectInfo projects = folder.Projects["BuildTestSSIS"];

//Provide SSIS commadn which need to be executed, Environment reference will decide which environment we are using

EnvironmentReference rf = projects.References["Environment_From_CS_Code", "."];

rf.Refresh();

long refid = rf.ReferenceId;

jbs.Command = "/ISSERVER \"\\\"\\SSISDB\\ExecuteTest\\BuildTestSSIS\\Package.dtsx\\\"\" /SERVER \"\\\"dil\\denali\\\"\" /ENVREFERENCE "+refid+" /Par \"\\\"$ServerOption::LOGGING_LEVEL(Int16)\\\"\";1 /Par \"\\\"$ServerOption::SYNCHRONIZED(Boolean)\\\"\";True /CALLERINFO SQLAGENT /REPORTING E";

jbs.Alter();

//Creating schedule for Job, it runs daily with interval of 1 hour

JobSchedule jbsch = new JobSchedule(jb, "Job Schedule from code");

jbsch.Create();

jbsch.ActiveStartDate = new DateTime(2013, 7, 26, 23, 00, 00);

jbsch.ActiveEndDate = new DateTime(2013,7,30,23,00,00);

jbsch.IsEnabled = true; //enbaled schedule

jbsch.FrequencyTypes = FrequencyTypes.Daily; //daily job

jbsch.FrequencyInterval = 1; //run after intereval of 1 day

jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Hour; //hourkly interval for each day

jbsch.FrequencySubDayInterval = 1; // asking it to run after every 1 hour

jbsch.ActiveStartTimeOfDay = new TimeSpan(20, 30, 00); // when should it start for daily schedule end time will be 23:59 by default could be changed using jbsch.ActiveEndTimeOfDay method

jbsch.Alter();

Powershell:

Import-Module SQLPS -DisableNameChecking

# Add-Type -AssemblyName "Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

#Connect to SQL server which has SSIS Package

$instanceName = "dil\denali"

$server=New-Object Microsoft.SqlServer.Management.Smo.Server($instanceName)

#Create Job

$jobName = "SSIS_Job_Powershell"

$job = New-Object Microsoft.SqlServer.Management.SMO.Agent.Job($server.JobServer, $jobName)

$job.Create()

$job.ApplyToTargetServer($instanceName)

#Create Job Step, Main part is subsystem

$jobStep=New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep($job, "SSIS_Job_Step")

$jobStep.OnSuccessAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithSuccess

$jobStep.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure

$jobStep.Subsystem=[Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::SSIS

$jobStep.server="dil\denali"

#Connect to SQL server which has SSIS Package

$sqlInstance = "dil\denali"

$sqlConnectionString = "Data Source=$sqlInstance;Initial Catalog=master;Integrated Security=SSPI"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

#Connect to Integration Service Catalog and load project

$ssisServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection

$ssisCatalog = $ssisServer.Catalogs["SSISDB"]

$ssisFolderName = "ExecuteTest"

$ssisFolder = $ssisCatalog.Folders.Item($ssisFolderName)

$ssisProjectName = "BuildTestSSIS"

$ssisProject = $ssisFolder.Projects.Item($ssisProjectName)

$ssisPackageName = "Package.dtsx"

#Provide SSIS commadn which need to be executed, Environment reference will decide which environment we are using

$environmentReference = $ssisProject.References.Item("Environment_from_powershell", $ssisFolder.Name)

$environmentReference.Refresh()

Write-Host $environmentReference.ReferenceId

$jobStep.Command='/ISSERVER "\"\SSISDB\ExecuteTest\BuildTestSSIS\Package.dtsx\"" /SERVER "\"dil\denali\"" /ENVREFERENCE '+$environmentReference.ReferenceId+' /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'

$jobStep.Create()

Below code can help in creating Job schedule for above job:

http://powershell.com/cs/media/p/52.aspx

Our journey ends here and this finishes automation series.

Now you should be able to deploy packages, configure them and configure job which will run this package.

Do reply with your suggestions and comments and if any then questions well!!!

Author : Dilkush(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Debarchan(MSFT) SQL Developer Engineer, Microsoft