SSIS - Deploy SQL Agent Job

Octopus.Script exported 2024-10-07 by andrescolodrero belongs to ‘SQL Server’ category.

Deploy a SQL Agent Job for SSIS Ispac Deployment. Requires SMO to be installed on the machine where this step will be run.

Parameters

When steps based on the template are included in a project’s deployment process, the parameters below can be set.

Server Name

SSIS_ServerName =

null

Folder Name

SSIS_FolderName =

Folder Name of the deployed package

Project Name

SSIS_ProjectName =

null

Catalog Name

SSIS_CatalogName = SSISDB

By default SSISDB

Environment Name

SSIS_EnvironmentName =

null

Package Name

SSIS_PackageName =

Complete name of the package to execute, without dtsx extension

Job Name

SSIS_JobName =

Name of the SQL Job

Job Step Name

SSIS_JobStepName =

null

Job Schedule Name

SSIS_JobSheduleName =

null

Job Execution Frequency

SSIS_JobExecutionFrequency = Daily

Frequency of execution Only supported: OneTime Daily AutoStart

Job Frequency Interval

SSIS_JobFrequencyInterval = 1

Depends of Job Execution Frequency. For now only works with Days Example: 1 -> Recurs every 1 Day 2 -> Recurs every 2 Weeks

Job Execution Time Hour

SSIS_JobExecutionTimeHour =

Hour of the day to execute the job

Job Execution Time Minute

SSIS_JobExecutionTimeMinute =

Minutes of the day to execute the job

Job Category

SSIS_JobCategory =

null

Script body

Steps based on this template will execute the following PowerShell script.

Function Format-OctopusArgument
{

    <#
    .SYNOPSIS
    Converts boolean values to boolean types

    .DESCRIPTION
    Converts boolean values to boolean types

    .PARAMETER Value
    The value to convert

    .EXAMPLE
    Format-OctopusArgument "true"
    #>
    Param(
        [string]$Value
    )

    $Value = $Value.Trim()

    # There must be a better way to do this
    Switch -Wildcard ($Value)
    {

        "True"
        { Return $True
        }
        "False"
        { Return $False
        }
        "#{*}"
        { Return $null
        }
        Default
        { Return $Value
        }
    }
}


Function Get-SSISCommand
{

    <#
    .SYNOPSIS
    Format the SSIS Command that execute the package.
    Only valid for now for Project Deployment ($typecommand = '/ISSERVER "\')

    .DESCRIPTION
    Return a string with the correct format to execute a Project Package Deploy (Step).
    The SSIS Command can be built by paramaters (ServerName, CatalogName, ProjectName, FolderName, Package and Environment)
    @TO-DO:
    But most of the cases need an ending string that could be the same for most of the deployments.
    I will keep this as a Octopus Parameter by now.


    #>

    Param($ServerName, $CatalogName, $FolderName, $ProjectName, $PackageName, $EnvironmentName)
    process {
        $environmentid = Get-EnvironmentId -ServerName $ServerName -EnvironmentName $EnvironmentName -PackageName $PackageName -ProjectName $ProjectName
        write-host "The Environmemnt Id found for $EnvironmentName is $environmentid"
        $slash = '\'
        $quotes = '"'
        $typecommand = '/ISSERVER "\'
        $environmentCommand = '\"" /ENVREFERENCE ' + $environmentid.ToString()
        $packagep = $slash + $CatalogName + $slash + $FolderName + $slash + $ProjectName + $slash + $PackageName + '.dtsx' + $slash
        $servertype = '" /SERVER "\"'
        $commandoptions = ' /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E'
    }
    end {
        return $typecommand + $quotes + $packagep + $quotes + $servertype + $ServerName + $environmentCommand + $commandoptions
    }

}

Function Get-EnvironmentId
{
    <#
    .SYNOPSIS
    Get the ID of the Environment by Name of Environment

    .DESCRIPTION
    ProjectDeploy Packages use Enviroments for variables previously deployed for the package.
    To be able to format the SSIS Command, we will need the Environment ID

    .PARAMETER ServerName
    .PARAMETER CatalogName
    .PARAMETER FolderName
    .PARAMETER ProjectName
    .PARAMETER PackageName
    .PARAMETER EnvironmentName
    #>

    Param($ServerName, $EnvironmentName,$PackageName, $ProjectName)


    $query = "SELECT er.reference_id
    FROM [SSISDB].[internal].[folders] AS f
         JOIN [SSISDB].[internal].[projects] AS p
              ON f.folder_id = p.folder_id
              JOIN [SSISDB].[internal].[environment_references] AS er
                   ON p.project_id = er.project_id
                   where f.name = '$FolderName'
						and er.environment_name = '$EnvironmentName'
						 and p.name = '$ProjectName'"

    $EnvironmentId = Invoke-Sqlcmd  -Query $query -ServerInstance $ServerName -Verbose

    return $EnvironmentId.reference_id
}

Function Add-Job
{
    <#
    .SYNOPSIS
    Add a new type of Job in SQL Agent Job

    .DESCRIPTION
    The function will remove an existing Job with same name (SQL SMO Job doesnt contains update function)
    and it will return as GlobalVariable the JOb Object.

    .PARAMETER JobName
    .PARAMETER ServerName
    .PARAMETER EnvironmentName
    .PARAMETER JobStepName
    .PARAMETER JobStepCommand
    #>

    param($JobName, $ServerName, $EnvironmentName, $JobsStepName, $JobStepCommand, $JobCategory )
    try
    {
        $server = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)
        $server.exe
        $server.JobServer.HostLoginName
        $existingjob = $server.Jobserver.Jobs|where-object {$_.Name -like $JobName}
        if ($existingjob)
        {
            Write-Host "|- Dropping Job [$JobName]..." -NoNewline
            $existingjob.drop()
            Write-Host "|- Done" -ForegroundColor Green
        }

        $job = New-Object Microsoft.SqlServer.Management.SMO.Agent.Job($server.JobServer, $jobName)
        #$job.DropIfExists() only for sqlserver 2016
        $job.Create()
        $job.OwnerLoginName = "sa"
        $job.Category = $JobCategory
        $job.ApplyToTargetServer($server.Name)


    }
    catch
    {
        write-host "####### Error Adding a job" -ForegroundColor Red
        write-host $_.Exception.Message
    }
    #Instead of creating a class and return a Job, lets settup a global variable. Return statament doenst return all script output
    $Global:newjob = $job

}

Function Add-JobStep
{
    <#
    .SYNOPSIS
    Add a job step to a Job

    .DESCRIPTION
    The function will remove an existing Job with same name (SQL SMO Job doesnt contains update function)
    and it will return as GlobalVariable t
     he JOb Object.
    .PARAMETER Job
    .PARAMETER JobStepName
    .PARAMETER JobStepCommand
    #>
    param($job, $JobStepName, $CommandJob )
    try
    {
        $jobStep = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep($job, $JobStepName)
        $jobStep.Subsystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::SSIS

        $jobStep.Command = $CommandJob
        $jobStep.Create()
    }
    catch
    {
        write-host "#########   Error adding a job step" -ForegroundColor Red
        write-host $_.Exception.Message
    }


}

Function Add-JobSchedule
{
    # ToDO: Add more types of frequenct: Weekly, Monthly
    param($job, $JobScheduleName,$JobExecutionFrequency, $FrecuencyInterval, $startHour, $startMinutes)
    try
    {
        $name = $job.Name
        $SQLJobSchedule = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule($job, $JobScheduleName)

        switch ($JobExecutionFrequency) {
            "Daily" {
                     $result = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily
                     $subdayTypes =  [Microsoft.SqlServer.Management.SMO.Agent.FrequencySubDayTypes]::Hour
                    }
            "OneTime" {
                 $result = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::OneTime
                 $subdayTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencySubDayTypes]::Once
                 }
            "AutoStart" {
                    $result = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::AutoStart
                   }
            default  {
                $result = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily
              }
        }


        $SQLJobSchedule.FrequencyTypes =  $result
        # Setup Frequency Interval
        $SQLJobSchedule.FrequencyInterval = $FrecuencyInterval



        # Job Start
        $timeofday = New-TimeSpan -hours $startHour -minutes $startMinutes
        $SQLJobSchedule.ActiveStartTimeOfDay = $timeofday
        #Activate the Job
        $SQLJobSchedule.ActiveStartDate = Get-Date
        $SQLJobSchedule.Create()
    }
    catch
    {
        Write-Host "Error" -ForegroundColor Red
        write-host "Exception Type: $($_.Exception.GetType().FullName)" -ForegroundColor Red
        write-host "Exception Message: $($_.Exception.Message)" -ForegroundColor Red
        $error[0]|format-list -force

    }

}

Function Set-SQLJob
{
    #Get-Octopus Variables
    Write-Host "Collecting Octopus Variables"

    $ServerName = Format-OctopusArgument -Value $OctopusParameters["SSIS_ServerName"]
    $FolderName = Format-OctopusArgument -Value $OctopusParameters["SSIS_FolderName"]
    $ProjectName = Format-OctopusArgument -Value $OctopusParameters["SSIS_ProjectName"]
    $CatalogName = Format-OctopusArgument -Value $OctopusParameters["SSIS_CatalogName"]
    $EnvironmentName = Format-OctopusArgument -Value $OctopusParameters["SSIS_EnvironmentName"]
    $PackageName = Format-OctopusArgument -Value $OctopusParameters["SSIS_PackageName"]
    $JobName = Format-OctopusArgument -Value $OctopusParameters["SSIS_JobName"]
    $JobCategory =  Format-OctopusArgument -Value $OctopusParameters["SSIS_JobCategory"]
    $JobStepName = Format-OctopusArgument -Value $OctopusParameters["SSIS_JobStepName"]
    $JobScheduleName = Format-OctopusArgument -Value $OctopusParameters["SSIS_JobScheduleName"]
    $JobExecutionFrequency = Format-OctopusArgument -Value $OctopusParameters["SSIS_JobExecutionFrequency"]
    $JobFrequencyInterval = Format-OctopusArgument -Value $OctopusParameters["SSIS_JobFrequencyInterval"]
    $JobExecutionTimeHour = Format-OctopusArgument -Value $OctopusParameters["SSIS_JobExecutionTimeHour"]
    $JobExecutionTimeMinute = Format-OctopusArgument -Value $OctopusParameters["SSIS_JobExecutionTimeMinute"]


   # FrecuencyType is hardcoded

    #Getting Module sqlserver if possible.
    $Module = get-module -ListAvailable -name sqlserver
    if ($Module.Name -eq 'sqlserver') {
        write-host "Importing Module SqlServer"
        Import-Module sqlserver -DisableNameChecking
    } else {
        write-host "Importing Module sqlps"
        import-module sqlps -Verbose -DisableNameChecking
    }

    #First step is to generate the command execution for Job Step.
    $JobStepCommand = Get-SSISCommand -ServerName $ServerName -CatalogName $CatalogName -FolderName $FolderName -ProjectName $ProjectName -PackageName $PackageName -EnvironmentName $EnvironmentName -Verbose

    write-Host "Command found to deploy Step is "
    write-Host $JobStepCommand
    write-Host "STARTING DEPLOYMENT "
    write-Host "|- Start Adding the Job $JobName"
    Add-Job -JobName $JobName -ServerName $ServerName -EnvironmentName $EnvironmentName -JobsStepName $JobStepName -JobStepCommand $JobStepCommandmand
    write-Host "|- $JobName Added to $ServerName"

    write-Host "|--- Start Adding the JobStep $JobStepName to Job $JobName"
    Add-JobStep -JobStepName $JobStepName -CommandJob $JobStepCommand -Job $Global:newjob
    write-Host "|--- $JobStepName added to Job $JobName"

    write-Host "|----  Start Adding JobShedule  $JobScheduleName JobStep $JobName"
    Add-JobSchedule -job $Global:newjob  -JobScheduleName $JobScheduleName  -JobExecutionFrequency $JobExecutionFrequency -FrecuencyInterval $JobFrequencyInterval -startHour $JobExecutionTimeHour -startMinutes $JobExecutionTimeMinute
    write-Host "|---- $JobStepName added to Job $JobName"
}

Write-Host "Starting deployment of SQL Job"


Set-SQLJob


Write-Host "Finishing Install"

Provided under the Apache License version 2.0.

Report an issue

To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.

{
  "Id": "f69ea863-ab61-4c58-bcbe-a133450113c9",
  "Name": "SSIS - Deploy SQL Agent Job",
  "Description": "Deploy a SQL Agent Job for SSIS Ispac Deployment. Requires SMO to be installed on the machine where this step will be run. ",
  "Version": 10,
  "ExportedAt": "2018-17-04T10:59:08.819Z",
  "ActionType": "Octopus.Script",
  "Author": "andrescolodrero",
  "Parameters": [
    {
      "Id": "44a2fa3c-3117-450b-8004-ebdbfd2362e9",
      "Name": "SSIS_ServerName",
      "Label": "Server Name",
      "HelpText": null,
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "ae040e98-f686-4f44-ad80-8939fbd902e4",
      "Name": "SSIS_FolderName",
      "Label": "Folder Name",
      "HelpText": "Folder Name of the deployed package",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "3e755e91-afb5-46c3-ad61-47bf80ac88b8",
      "Name": "SSIS_ProjectName",
      "Label": "Project Name",
      "HelpText": null,
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "21dc0b13-7db0-4c99-b512-53dbfad55893",
      "Name": "SSIS_CatalogName",
      "Label": "Catalog Name",
      "HelpText": "By default SSISDB",
      "DefaultValue": "SSISDB",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "eb197ae7-99e1-41a2-aee4-5b379fdb349c",
      "Name": "SSIS_EnvironmentName",
      "Label": "Environment Name",
      "HelpText": null,
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "94f97e01-d04c-4696-ad0a-e170a21ecd87",
      "Name": "SSIS_PackageName",
      "Label": "Package Name",
      "HelpText": "Complete name of the package to execute, without dtsx extension",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "cb138aa6-2f51-4ebd-b744-9b0d17535ea1",
      "Name": "SSIS_JobName",
      "Label": "Job Name",
      "HelpText": "Name of the SQL Job",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "458225af-7d11-4f98-b984-2fe02e66996e",
      "Name": "SSIS_JobStepName",
      "Label": "Job Step Name",
      "HelpText": null,
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "07d85895-e548-4f54-a403-25cc53fa1a88",
      "Name": "SSIS_JobSheduleName",
      "Label": "Job Schedule Name",
      "HelpText": null,
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "023f21b1-7757-42f2-8b69-e9a95deb7bae",
      "Name": "SSIS_JobExecutionFrequency",
      "Label": "Job Execution Frequency",
      "HelpText": "Frequency of execution\nOnly supported:\nOneTime\nDaily\nAutoStart",
      "DefaultValue": "Daily",
      "DisplaySettings": {
        "Octopus.ControlType": "Select",
        "Octopus.SelectOptions": "AutoStart\nDaily\nOneTime"
      },
      "Links": {}
    },
    {
      "Id": "25efa439-5490-4ed4-a70f-7b514bd18446",
      "Name": "SSIS_JobFrequencyInterval",
      "Label": "Job Frequency Interval",
      "HelpText": "Depends of Job Execution Frequency.\nFor now only works with Days\nExample:\n1 -> Recurs every 1 Day\n2 -> Recurs every 2 Weeks",
      "DefaultValue": "1",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "50b3ab13-8635-4631-a247-092792364902",
      "Name": "SSIS_JobExecutionTimeHour",
      "Label": "Job Execution Time Hour",
      "HelpText": "Hour of the day to execute the job",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "4d57dc4e-8717-4f05-bdad-0f16cc9b566d",
      "Name": "SSIS_JobExecutionTimeMinute",
      "Label": "Job Execution Time Minute",
      "HelpText": "Minutes of the day to execute the job",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    },
    {
      "Id": "7a4c18db-51c3-48e7-ab04-b4f084dde3de",
      "Name": "SSIS_JobCategory",
      "Label": "Job Category",
      "HelpText": null,
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      },
      "Links": {}
    }
  ],
  "Properties": {
    "Octopus.Action.Script.Syntax": "PowerShell",
    "Octopus.Action.Script.ScriptSource": "Inline",
    "Octopus.Action.Script.ScriptBody": "Function Format-OctopusArgument\n{\n\n    <#\n    .SYNOPSIS\n    Converts boolean values to boolean types\n\n    .DESCRIPTION\n    Converts boolean values to boolean types\n\n    .PARAMETER Value\n    The value to convert\n\n    .EXAMPLE\n    Format-OctopusArgument \"true\"\n    #>\n    Param(\n        [string]$Value\n    )\n\n    $Value = $Value.Trim()\n\n    # There must be a better way to do this\n    Switch -Wildcard ($Value)\n    {\n\n        \"True\"\n        { Return $True\n        }\n        \"False\"\n        { Return $False\n        }\n        \"#{*}\"\n        { Return $null\n        }\n        Default\n        { Return $Value\n        }\n    }\n}\n\n\nFunction Get-SSISCommand\n{\n\n    <#\n    .SYNOPSIS\n    Format the SSIS Command that execute the package.\n    Only valid for now for Project Deployment ($typecommand = '/ISSERVER \"\\')\n\n    .DESCRIPTION\n    Return a string with the correct format to execute a Project Package Deploy (Step).\n    The SSIS Command can be built by paramaters (ServerName, CatalogName, ProjectName, FolderName, Package and Environment)\n    @TO-DO:\n    But most of the cases need an ending string that could be the same for most of the deployments.\n    I will keep this as a Octopus Parameter by now.\n\n\n    #>\n\n    Param($ServerName, $CatalogName, $FolderName, $ProjectName, $PackageName, $EnvironmentName)\n    process {\n        $environmentid = Get-EnvironmentId -ServerName $ServerName -EnvironmentName $EnvironmentName -PackageName $PackageName -ProjectName $ProjectName\n        write-host \"The Environmemnt Id found for $EnvironmentName is $environmentid\"\n        $slash = '\\'\n        $quotes = '\"'\n        $typecommand = '/ISSERVER \"\\'\n        $environmentCommand = '\\\"\" /ENVREFERENCE ' + $environmentid.ToString()\n        $packagep = $slash + $CatalogName + $slash + $FolderName + $slash + $ProjectName + $slash + $PackageName + '.dtsx' + $slash\n        $servertype = '\" /SERVER \"\\\"'\n        $commandoptions = ' /Par \"\\\"$ServerOption::LOGGING_LEVEL(Int16)\\\"\";1 /Par \"\\\"$ServerOption::SYNCHRONIZED(Boolean)\\\"\";True /CALLERINFO SQLAGENT /REPORTING E'\n    }\n    end {\n        return $typecommand + $quotes + $packagep + $quotes + $servertype + $ServerName + $environmentCommand + $commandoptions\n    }\n\n}\n\nFunction Get-EnvironmentId\n{\n    <#\n    .SYNOPSIS\n    Get the ID of the Environment by Name of Environment\n\n    .DESCRIPTION\n    ProjectDeploy Packages use Enviroments for variables previously deployed for the package.\n    To be able to format the SSIS Command, we will need the Environment ID\n\n    .PARAMETER ServerName\n    .PARAMETER CatalogName\n    .PARAMETER FolderName\n    .PARAMETER ProjectName\n    .PARAMETER PackageName\n    .PARAMETER EnvironmentName\n    #>\n\n    Param($ServerName, $EnvironmentName,$PackageName, $ProjectName)\n\n\n    $query = \"SELECT er.reference_id\n    FROM [SSISDB].[internal].[folders] AS f\n         JOIN [SSISDB].[internal].[projects] AS p\n              ON f.folder_id = p.folder_id\n              JOIN [SSISDB].[internal].[environment_references] AS er\n                   ON p.project_id = er.project_id\n                   where f.name = '$FolderName'\n\t\t\t\t\t\tand er.environment_name = '$EnvironmentName'\n\t\t\t\t\t\t and p.name = '$ProjectName'\"\n\n    $EnvironmentId = Invoke-Sqlcmd  -Query $query -ServerInstance $ServerName -Verbose\n\n    return $EnvironmentId.reference_id\n}\n\nFunction Add-Job\n{\n    <#\n    .SYNOPSIS\n    Add a new type of Job in SQL Agent Job\n\n    .DESCRIPTION\n    The function will remove an existing Job with same name (SQL SMO Job doesnt contains update function)\n    and it will return as GlobalVariable the JOb Object.\n\n    .PARAMETER JobName\n    .PARAMETER ServerName\n    .PARAMETER EnvironmentName\n    .PARAMETER JobStepName\n    .PARAMETER JobStepCommand\n    #>\n\n    param($JobName, $ServerName, $EnvironmentName, $JobsStepName, $JobStepCommand, $JobCategory )\n    try\n    {\n        $server = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerName)\n        $server.exe\n        $server.JobServer.HostLoginName\n        $existingjob = $server.Jobserver.Jobs|where-object {$_.Name -like $JobName}\n        if ($existingjob)\n        {\n            Write-Host \"|- Dropping Job [$JobName]...\" -NoNewline\n            $existingjob.drop()\n            Write-Host \"|- Done\" -ForegroundColor Green\n        }\n\n        $job = New-Object Microsoft.SqlServer.Management.SMO.Agent.Job($server.JobServer, $jobName)\n        #$job.DropIfExists() only for sqlserver 2016\n        $job.Create()\n        $job.OwnerLoginName = \"sa\"\n        $job.Category = $JobCategory\n        $job.ApplyToTargetServer($server.Name)\n\n\n    }\n    catch\n    {\n        write-host \"####### Error Adding a job\" -ForegroundColor Red\n        write-host $_.Exception.Message\n    }\n    #Instead of creating a class and return a Job, lets settup a global variable. Return statament doenst return all script output\n    $Global:newjob = $job\n\n}\n\nFunction Add-JobStep\n{\n    <#\n    .SYNOPSIS\n    Add a job step to a Job\n\n    .DESCRIPTION\n    The function will remove an existing Job with same name (SQL SMO Job doesnt contains update function)\n    and it will return as GlobalVariable t\n     he JOb Object.\n    .PARAMETER Job\n    .PARAMETER JobStepName\n    .PARAMETER JobStepCommand\n    #>\n    param($job, $JobStepName, $CommandJob )\n    try\n    {\n        $jobStep = New-Object Microsoft.SqlServer.Management.Smo.Agent.JobStep($job, $JobStepName)\n        $jobStep.Subsystem = [Microsoft.SqlServer.Management.Smo.Agent.AgentSubSystem]::SSIS\n\n        $jobStep.Command = $CommandJob\n        $jobStep.Create()\n    }\n    catch\n    {\n        write-host \"#########   Error adding a job step\" -ForegroundColor Red\n        write-host $_.Exception.Message\n    }\n\n\n}\n\nFunction Add-JobSchedule\n{\n    # ToDO: Add more types of frequenct: Weekly, Monthly\n    param($job, $JobScheduleName,$JobExecutionFrequency, $FrecuencyInterval, $startHour, $startMinutes)\n    try\n    {\n        $name = $job.Name\n        $SQLJobSchedule = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule($job, $JobScheduleName)\n\n        switch ($JobExecutionFrequency) {\n            \"Daily\" {\n                     $result = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily\n                     $subdayTypes =  [Microsoft.SqlServer.Management.SMO.Agent.FrequencySubDayTypes]::Hour\n                    }\n            \"OneTime\" {\n                 $result = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::OneTime\n                 $subdayTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencySubDayTypes]::Once\n                 }\n            \"AutoStart\" {\n                    $result = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::AutoStart\n                   }\n            default  {\n                $result = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily\n              }\n        }\n\n\n        $SQLJobSchedule.FrequencyTypes =  $result\n        # Setup Frequency Interval\n        $SQLJobSchedule.FrequencyInterval = $FrecuencyInterval\n\n\n\n        # Job Start\n        $timeofday = New-TimeSpan -hours $startHour -minutes $startMinutes\n        $SQLJobSchedule.ActiveStartTimeOfDay = $timeofday\n        #Activate the Job\n        $SQLJobSchedule.ActiveStartDate = Get-Date\n        $SQLJobSchedule.Create()\n    }\n    catch\n    {\n        Write-Host \"Error\" -ForegroundColor Red\n        write-host \"Exception Type: $($_.Exception.GetType().FullName)\" -ForegroundColor Red\n        write-host \"Exception Message: $($_.Exception.Message)\" -ForegroundColor Red\n        $error[0]|format-list -force\n\n    }\n\n}\n\nFunction Set-SQLJob\n{\n    #Get-Octopus Variables\n    Write-Host \"Collecting Octopus Variables\"\n\n    $ServerName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_ServerName\"]\n    $FolderName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_FolderName\"]\n    $ProjectName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_ProjectName\"]\n    $CatalogName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_CatalogName\"]\n    $EnvironmentName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_EnvironmentName\"]\n    $PackageName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_PackageName\"]\n    $JobName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_JobName\"]\n    $JobCategory =  Format-OctopusArgument -Value $OctopusParameters[\"SSIS_JobCategory\"]\n    $JobStepName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_JobStepName\"]\n    $JobScheduleName = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_JobScheduleName\"]\n    $JobExecutionFrequency = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_JobExecutionFrequency\"]\n    $JobFrequencyInterval = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_JobFrequencyInterval\"]\n    $JobExecutionTimeHour = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_JobExecutionTimeHour\"]\n    $JobExecutionTimeMinute = Format-OctopusArgument -Value $OctopusParameters[\"SSIS_JobExecutionTimeMinute\"]\n\n\n   # FrecuencyType is hardcoded\n\n    #Getting Module sqlserver if possible.\n    $Module = get-module -ListAvailable -name sqlserver\n    if ($Module.Name -eq 'sqlserver') {\n        write-host \"Importing Module SqlServer\"\n        Import-Module sqlserver -DisableNameChecking\n    } else {\n        write-host \"Importing Module sqlps\"\n        import-module sqlps -Verbose -DisableNameChecking\n    }\n\n    #First step is to generate the command execution for Job Step.\n    $JobStepCommand = Get-SSISCommand -ServerName $ServerName -CatalogName $CatalogName -FolderName $FolderName -ProjectName $ProjectName -PackageName $PackageName -EnvironmentName $EnvironmentName -Verbose\n\n    write-Host \"Command found to deploy Step is \"\n    write-Host $JobStepCommand\n    write-Host \"STARTING DEPLOYMENT \"\n    write-Host \"|- Start Adding the Job $JobName\"\n    Add-Job -JobName $JobName -ServerName $ServerName -EnvironmentName $EnvironmentName -JobsStepName $JobStepName -JobStepCommand $JobStepCommandmand\n    write-Host \"|- $JobName Added to $ServerName\"\n\n    write-Host \"|--- Start Adding the JobStep $JobStepName to Job $JobName\"\n    Add-JobStep -JobStepName $JobStepName -CommandJob $JobStepCommand -Job $Global:newjob\n    write-Host \"|--- $JobStepName added to Job $JobName\"\n\n    write-Host \"|----  Start Adding JobShedule  $JobScheduleName JobStep $JobName\"\n    Add-JobSchedule -job $Global:newjob  -JobScheduleName $JobScheduleName  -JobExecutionFrequency $JobExecutionFrequency -FrecuencyInterval $JobFrequencyInterval -startHour $JobExecutionTimeHour -startMinutes $JobExecutionTimeMinute\n    write-Host \"|---- $JobStepName added to Job $JobName\"\n}\n\nWrite-Host \"Starting deployment of SQL Job\"\n\n\nSet-SQLJob\n\n\nWrite-Host \"Finishing Install\"\n"
  },
  "Category": "SQL Server",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates//opt/buildagent/work/75443764cd38076d/step-templates/ssis-deploy-sqlagentjob.json",
  "Website": "/step-templates/f69ea863-ab61-4c58-bcbe-a133450113c9",
  "Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAD9QTFRFlZ+r3DAr6p+dy8/V4G9t////5efp9M7NrLS+wCYm8/T1vcPK1tnd10xK+fn6/PLyUU5O+eXk3+Hk7O3u7/DxS2XoPwAADb9JREFUeNrsnYl6nbgOgMEYDHghGN7/Wa8k70B6s3AOZD5o2umcSaf+0S4bUbX/kat6QB6QB+QBeUAekAfkAXlAHpAH5AF5QB6QB+QBeUAekAckXMv4XwBZVCPVnwcZlZSNXRrzp0HGTkqplrY1zfKHQboGMZwoGvVXQUbVy152QaPUu3XrJJCl6Xsp1/SBfbdunQJiZd/3zVqqmfprIEb1iLHRpLF5s279FsQ0iCH3etQ03R8CQYyq74/MwTbN3wGxQFGRRJTaJiVL815z/wXIIiviQEunq2lsNyZhvdfcfw6iCMPavl9H20jkgV8gP1F2NRRJmvEvgIA4gAS0B8xkpexEYWB3F0ijAyOxfwAkcsBvHQk53QWW71HwGm8PIhJHazIS98HYdUqBar1TJD8EYQOABGNe+w0J0dj3iuSHIOMw6PRHOyDpdhggE2XvDmLYAChsDh4MSPI1g92DWkGaosbbey0kARbOyFCaTCYgDemioQWp3D+O9EO4NGNCRpIFMKQzjlG9TyS/iOwoE64jjeaVwICOzjeoGfgue38QshPRMV57lhpVjbNemZTMK7X+gaQRSRgQzaz2JDX9CjRiDvWV+gMgRniSltWMMV0TSo1fcIEjEAKUa7k/CDiomkjaeeAU8JEmoRAOuoLp/hWidTJp9RBiipkF07our9fj/Lpmn51MeM2TnAx5gnp/cRZj6P2aD6BdWoBu1QUeiESwWoCu8a10OBfzHUFaATIxoFssfjIxUKbZiJobkg/ibFSNny2aM/pa4Lt0y4eoWwJkQP9S11NQNoOmw18Ic0qDDsIIg59TiC517aTDa5a7OBDPLDjRBMemmbgTCIhjEINbNVpHLXzozzxAhI4mg9ETv7i4DwhYiHa6JfA2T9F6dPltaDwgBQifwgG5ZOAMlpNAZlrShEpW8ykG/mgkCaMmX40LXwX3uUBR21wLgoYxoMOtc22agpJlGBM5AYF5pcFUwOkXXr8Ty2n7IxrWgze4sIo6WrvD4LNx6pc8QDtzHVA0uwGIcJ6otO4IQhahfZLCtqYjYiUwsOlqEMMp8S31w4MIHrUKv1PvnZlhsUJjF4NAWHQ5PCRUIoGA5XutEpMJsquPFjvzX6GcB2I0Ybg45wWDpi/Iz7K07QPiOfZQEwtls7gShCL6kGe6U4tBg8Bmk7syfSjRpF0glOVCEDT3Mp0KQZyV+cxeswKEjur1baGcuc8O66bQsM10C0Wa6jy4oG2E7gXkXeAxdOdhmLkMBPxWSLJyFj5vBKJLURAGJ58m0NKNcuLh01UgLLvXU87CWSEQVlDUSOHu/gQp2xgaTSAidRFISICjl83UiyVYl3/NIdHiKQZy73pNEIq4BqTNzZht2w8sCISjXWjnqYtcEZtLwTBM9c2Qci5I+ouDYs2sQMGPZxH+Y5kGiFIE6nskp4LwEPcmTpaBd99MqZTiLHPK2wwRDAQq5sxVjeS+enMBSGhAzMRhQsTIUOK1Lz9w2cWHZqy+YSevkMiknWvSMRfZoGg2mX1ecBA6yHupCyRCEqDkasaqMYsYc/LGRwWUmdHd7j4dG/x4ukIiE3HQ382KVDF546NAN9XHSmQsWo65wkbmuFSdxcdCtQ7yKP2ZgzLdx9dc19kSEbFqF0mzdsYuDgydf/I/RW8m324jPGUgPPgsoTPz0Af5MNn0p5ZgZpDJ9F6QfI2ztxQf/TT3DS+2J8Hm8b/sYAJxmXeCzJukikdnpcUUG5BeKKzQnfpf0UJUX4gmpyaNdVoQJlWzYSGGG9I5Fz0mXtoJGEh9sPc70ZZErBrN+0AMyyTCkkEwr1BJe1hOwnfysEiQyl5dMWneqlp8iGGCstyI4YLIVKT4gwfDJmvMTHDrIUP44FWz4JbEe93vnIUJXlSHyUDi92rnps1c+/LcgBiG7OIghqu6KHHXYxZlMsLLfpAzlAGTfjB0ICzlgLq0jqO5rGbnIAudtU+KqpAfKiI25XghCM3cuYlvn34+D2Qil5rqKDZlWRY/BA97CkM4aWRb89Pz2+eBsIHMedab1smks62fogs0+JMSDmL+3RH080B8a9qDCJMVvXrehgiu6yiP+pRN0epEgQi3SeUkkgeXXUOuDmdWBn7Wbuh5Gz2U67JtgsvqomUdtw4RQnNx3hMNJ269QS2iXRN7DrmUmXXGIYr+48knBqoTLUR4xztTXzRU73OgSPvSmov27OscELCEQWBgQM1hrjqc2tR+EPx1ojgVZMJTc+hzQzXl2sCc0pVMFkDRLa85iHbWyQe0Xoau1rkrg0AMk5VU5pJCmeXOILR9CMGCJ7cL5TuDJCVReDe7Aoi5K8hUUwKYc4A0MoXCLRy/+vHOIKBYPnXnbVk7BY1KS78zCKPNJShmY/9pjo0ToJjW/PErtJHxniCCjjtAxMBds9LXcrYCIZjFau4PAqURxwg+bDvvuJ/WdeiiEGW8PYge9GSEL7yjMNxOlLGd87XjGi3jriC4k4tHY8H5Gn94GUtc56QiCBn5eGcQMHRB9epEe2yDE0boe4y2i0f8jUcBkPV2IHg2nmHDkwk+uAqD573Q1dps0WAqYPTLi0L7r0CAAXs4NR3vxy8mi+fDAKRQI0AZ7wgyD7j8AQ/O0bMjrDFL8cjeYu0m+KEDux2IyLo4qFM0Q6R4GKnbgbQ3BDE6UdRsXpxWdblIrN00p0fiuBfIpCMGbtIafHwS8UAkYaHG2uLpRHBcKzqvW4GM6Skxhs62a6R7fh0fPgyZripARnK8NwOJ8gh9UXz00K0fn5p2v1uUXXZp771AhN6cc8PZLt4ejFJ+3INV8fm3cQkl7nqngOj9le7jJ8ARAwgqF0HFhxDHDq775Vp0SgGb/308XEEjg5KLbUgmo1Kdx8hSlRuBOHlU2bPfBp8GzSIGPn1o246e3BvBB9usKLwPCHPHqPAx42C1thAIkTQKn80fF6tsNtHiTiB0imelAQlBIluBOJmAVPBRXWXL6QM3ATGYslPhKpNEmq1AnJ04kI2vvQnIxAftXWofQRYUyGZxOJMDOXZjd+4BYnU6mZdApOw3AulwcAWR2O2ib9EOEoNOSSCqFi1f4ViXbL2Lokki3ka2MrkDiKryg5IIgqePRpxRozYUjmQxi9o+Pb1e3/tVVTG1yaJuGZz2IHt/nGoEN9zQbBe1di53NOCEi3p3vbwbX8oD7n1PkzfwH5RljX7iDs7fMDQ5yHrrtrmpLFeDyKraqDbpFk6pkRKsO04NckYBJW8a5bZCpWh9s7HrXpMzfhVEVdX2RtLENhpJJSWNcUKMkBqqppgTBmKBPGVEVeu68UIQ4NjPLwtjtUg08KOx2dCK3eQ2SOQtSAMkciHIUlX9/tMmkRQUXiB7JwtlbpbPXwBiqqra3cZVxUlnSaPCHwCLPzo/jYp1JUi/U6yuwZltNH6uPxh8YuXRHKcRdMsCSHsViK0KjzUqWSWMvt8bj5EHY3LR3MfWdt1yGUiVCQRFUdGXBNWqjklU6KhkOmUpD4Yqq1uvAmkAZHVdBZrXBhQ0CXcBDmcm2y4c+uHCnGxIVJZNlfVWkIpcVgf330HY0e19UIqyODMpyUGzlkwYWb4FkfFFtv7/QSwtP0CYTFCUxq877VpzgWASmWXAdtN7fCdIUKcyUEBo6StSKU9i8s6Q7Lyboiw4a9JhfL8KpE/j/3Lr7WMzyJHEiqTzAjEuoy+cs/Nc14CYqjoK62AxMnnbPqTAVC+iQHBQOUbFctnYUjFXSYQU6yD36vNAntTL0sCzhvL57d03arfP8GaJVJu/fu03xUnn1KtznSGXCO/vPVYmS3uljWx1q/eRJQ/mfr6sT+ibIy+LFZZpr/VayyZE7lPCzk2XpQmznwxffulova/FkUIk3VFxAiWIT+jlZwOL15eOcftSZK+KpR94MaNkVmF9MggQQ7y5EERVpXKBoZfeyNhYmXjVOjYRTFXaC0G8SIKb2lbvnYzlFU2PX7y977TotZr1FZDFk7ipnoWhLzJUJqBO1BmiXpYfxVyuGzdNzKUglMgHmWQRfWloSDmkYW6BaZwppryeJenYi8eBfqn50ESZNMFARuUyYhnbV2qbBVuXpjQuczdF+nhVO6j3JIszENO4MCkzmx59C3VbpvuWtrUvHr/+9QZdcMPGyUJu2gtyN4U5erV1wZHlLx7H/NWWaRNAKK3fh2572IaIFkNiMXcACb4LKI5KCih8q+PH7QxVV0v36pHlX99WMLLaBfmi8D2I5ytOlZYY6ZtXv2rhOztWNghlp1gdvpxgr1ApnR9f/qaFb+0hRqFsh6tjMNmJIo+J9uWvI/nm9vQaUfIb3JQG0imXz2fRsHn5C2K+e2DArH1QsNhvGKuUR462OWhsr/Llbyf4yaEaGR2Yu83gsVaftLgMUtqN4b/hFR4/O69lk1iUsVTTG+VFofbbz+YN73776VFAH99dG1Iu7l09Uh1bdCdf/wqlXxyXHRML5sD/GBD/jpfx/fJsvOttu589vnXv2KhAIBgYQQNfNg//hBdyQcio+vCjxxpks1gLApmqj+rjox0/5G1BgteVfbaPhTjR6Okwl/kAFtl/9PcGyWqpPutEYFW1dM5CAARkcneJlDwLlVP+dVDhMNdHW8mP45TzriBZ7k+Xi4W9kbMS0v5JkDdeD8gD8oA8IA/IA/KAPCAPyAPygDwgD8gD8oA8IA/IA/IXr/8JMAAhf0RDrOWy2QAAAABJRU5ErkJggg==",
  "$Meta": {
    "Type": "ActionTemplate"
  }
}

History

Page updated on Invalid Date