Integrations
Start for free

Deploy ispac SSIS project from a Package parameter

Octopus.Script exported 2026-03-13 by bcullman belongs to ‘SQL Server’ category.

This step template will deploy SSIS ispac projects to SQL Server Integration Services Catalog. The template uses a referenced package and is Worker compatible.

This template will install the Nuget package provider if it is not present on the machine it is running on.

NOTE: The SqlServer PowerShell module this template utilizes removed the assemblies necessary to interface with SSIS as of version 22.0.59. Version 21.1.18256 has been pinned and will be used if the SqlServer PowerShell module is not installed.

Parameters

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

Database server name (\instance)

SSIS.Template.ServerName =

Name of the SQL Server you are deploying to.

SQL Authentication Username

SSIS.Template.sqlAccountUsername =

(Optional) Username of the SQL Authentication account. Use this approach when deploying to Azure Databases with SSIS configured. If SQL Authentication Username and Password are blank, Integrated Authentication is used.

SQL Authentication Password

SSIS.Template.sqlAccountPassword =

(Optional) Password of the SQL Authentication account.

Enable SQL CLR

SSIS.Template.EnableCLR = False

This will reconfigure SQL Server to enable the SQL CLR. It is highly recommended that this be previously authorized by your Database Administrator.

Catalog name

SSIS.Template.CatalogName = SSISDB

Name of the catalog to create in Integration Services Catalogs on SQL Server. When using the GUI, this value gets hardcoded to SSISDB and cannot be changed. It is recommended that you do not change the default value.

Catalog password

SSIS.Template.CatalogPwd =

Password to the Integration Services Catalog.

Folder name

SSIS.Template.FolderName =

Name of the folder to use within the Integration Services Catalog

Use environment

SSIS.Template.UseEnvironment = False

This will make a project reference to the defined environment.

Environment name

SSIS.Template.EnvironmentName =

Name of the environment to reference the project to. If the environment doesn’t exist, it will create it.

Reference project parameters to environment variables

SSIS.Template.ReferenceProjectParametersToEnvironmentVairables = False

Checking this box will make Project Parameters reference Environment Variables. If the Environment Variable doesn’t exist, it will create it. This expects that an Octopus variable of the same name exists.

Reference package parameters to environment variables

SSIS.Template.ReferencePackageParametersToEnvironmentVairables = False

Checking this box will make Package Parameters reference Environment Variables. If the Environment Variable doesn’t exist, it will create it. This expects than an Octopus variable of the same name exists.

Use Fully Qualified Variable Names

SSIS.Template.UseFullyQualifiedVariableNames = False

When true the package variables names must be represented in dtsx_name_without_extension.variable_name

Use Custom Filter for connection manager properties

SSIS.Template.UseCustomFilter = False

Custom filter should contain the regular expression for ignoring properties when setting will occur during the auto-mapping

Custom Filter for connection manager properties

SSIS.Template.CustomFilter =

Regular expression for filtering out the connection manager properties during the auto-mapping process. This string is used when UseCustomFilter is set to true

Clean obsolete variables from environment

SSIS.Template.SyncEnvironment = False

When true synchronizes the environment:

  • Removes obsolete variables
  • Removes renamed variables
  • Replaces values of valid variables (also when false)

Package Id

SSIS.Template.ssisPackageId =

Id of the package to deploy, used to support deployment with Workers.

Script body

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

function Add-TemporaryPinnedSqlServerModule
{
    # Define parameters
    param(
        $LocalModulesPath
    )

    $PowerShellModuleName = "SqlServer"
    $RequiredVersion = [version]'21.1.18256'

    # Check to see if the package provider has been installed
    if ($null -eq (Get-PackageProvider -ListAvailable -Name Nuget -ErrorAction SilentlyContinue))
    {
        # Display that we need the nuget package provider
        Write-Host "Nuget package provider not found, installing ..."

        # Install Nuget package provider
        Install-PackageProvider -Name Nuget -Force
    }

    # Save the module in the temporary location
    Save-Module -Name $PowerShellModuleName -Path $LocalModulesPath -Force -RequiredVersion $RequiredVersion -Repository "PSGallery"

    # Display
    Write-Output "Saved module $PowerShellModuleName v$RequiredVersion to $LocalModulesPath"

    # Import the module
    Import-Module -Name $PowerShellModuleName
}

Function Remove-TemporaryPinnedSqlServerModule
{
    param(
        [Parameter(Mandatory = $true)][string]$LocalModulesPath,
        [Parameter(Mandatory = $true)][version]$PinnedVersion
    )

    try { Remove-Module SqlServer -Force -ErrorAction SilentlyContinue } catch {}

    $moduleRoot = Join-Path $LocalModulesPath 'SqlServer'
    $pinnedPath = Join-Path $moduleRoot $PinnedVersion.ToString()

    if (Test-Path $pinnedPath)
    {
        Write-Host "Removing temporary pinned SqlServer module folder: $pinnedPath"
        Remove-Item -Path $pinnedPath -Recurse -Force -ErrorAction SilentlyContinue
    }

    # Optional: remove parent if empty
    if (Test-Path $moduleRoot)
    {
        $remaining = Get-ChildItem -Path $moduleRoot -Force -ErrorAction SilentlyContinue
        if (-not $remaining)
        {
            Remove-Item -Path $moduleRoot -Force -ErrorAction SilentlyContinue
        }
    }
}

Function Test-IntegrationServicesTypeAvailable
{
    # True if the IntegrationServices type resolves in this PowerShell session, else false
    $typeName = 'Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices'
    $aqn = "$typeName, Microsoft.SqlServer.Management.IntegrationServices"

    try
    {
        if ([Type]::GetType($aqn, $false)) { return $true }
    }
    catch {}

    # Try to load assembly (strong name), may fail on some boxes
    try { [void][Reflection.Assembly]::Load('Microsoft.SqlServer.Management.IntegrationServices') } catch {}

    try
    {
        if ([Type]::GetType($aqn, $false)) { return $true }
    }
    catch {}

    # Fall back to explicit GAC path load (LoadFrom) if present
    $gacRoot = Join-Path $env:windir 'Microsoft.NET\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices'
    if (Test-Path $gacRoot)
    {
        $gacDll = Get-ChildItem -Path $gacRoot -Recurse -Filter 'Microsoft.SqlServer.Management.IntegrationServices.dll' -File -ErrorAction SilentlyContinue |
                  Sort-Object FullName -Descending |
                  Select-Object -First 1

        if ($null -eq $gacDll)
        {
            try
            {
                $asm = [Reflection.Assembly]::LoadFrom($gacDll.FullName)
                if ($asm.GetType('Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices', $false)) { return $true }
            }
            catch {}
        }
    }

    try
    {
        if ([Type]::GetType($aqn, $false)) { return $true }
    }
    catch {}

    return $false
}

Function Load-SqlServerAssemblies
{
    # Get the folder where the SqlServer module ended up in
    $sqlServerModulePath = [System.IO.Path]::GetDirectoryName((Get-Module SqlServer).Path)

    # Loop through the assemblies
    foreach($assemblyFile in (Get-ChildItem -Path $sqlServerModulePath -Exclude msv*.dll | Where-Object {$_.Extension -eq ".dll"}))
    {
        try
        {
            # Only load managed .NET assemblies (native DLLs will throw)
            [void][System.Reflection.AssemblyName]::GetAssemblyName($assemblyFile.FullName)

            # Load the managed assembly
            [Reflection.Assembly]::LoadFrom($assemblyFile.FullName) | Out-Null
        }
        catch [System.BadImageFormatException]
        {
            # Native DLL (or wrong format), skip
        }
    }
}

Function Initialize-SsisDeploymentRuntime
{
    param(
        [Parameter(Mandatory = $true)][string]$LocalModulesPath,
        [Parameter(Mandatory = $true)][version]$PinnedSqlServerVersion
    )

    # Define PowerShell Modules path
    $env:PSModulePath = "$LocalModulesPath;$env:PSModulePath"

    $runtime = [pscustomobject]@{
        UsedPinnedSqlServerModule = $false
        SsisTypeAvailable         = $false
    }

    # First preference: use SSIS assemblies already available on the machine/session
    $runtime.SsisTypeAvailable = Test-IntegrationServicesTypeAvailable

    if ($runtime.SsisTypeAvailable)
    {
        Write-Host "SSIS IntegrationServices type is already available."

        # Only need a SqlServer module for cmdlets such as Invoke-Sqlcmd
        if ($null -ne (Get-Module -ListAvailable -Name "SqlServer"))
        {
            Import-Module -Name SqlServer -ErrorAction Stop
        }
        else
        {
            Write-Output "PowerShell module SqlServer not present, downloading temporary pinned copy for cmdlets ..."
            [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [System.Net.SecurityProtocolType]::Tls12

            Add-TemporaryPinnedSqlServerModule -LocalModulesPath $LocalModulesPath

            $manifestPath = Join-Path (Join-Path (Join-Path $LocalModulesPath 'SqlServer') $PinnedSqlServerVersion.ToString()) 'SqlServer.psd1'
            Write-Host "Importing pinned SqlServer module from: $manifestPath"
            Import-Module -Name $manifestPath -Force -DisableNameChecking -ErrorAction Stop

            $runtime.UsedPinnedSqlServerModule = $true
        }

        # SSIS type already resolved, no need to bulk-load module assemblies
        return $runtime
    }

    # Second preference: fall back to pinned SqlServer module that still contains SSIS assemblies
    Write-Output "SSIS IntegrationServices type not available via installed components. Downloading pinned SqlServer module $PinnedSqlServerVersion temporarily ..."
    [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [System.Net.SecurityProtocolType]::Tls12

    $manifestPath = Join-Path (Join-Path (Join-Path $LocalModulesPath 'SqlServer') $PinnedSqlServerVersion.ToString()) 'SqlServer.psd1'
    if (-not (Test-Path $manifestPath))
    {
        Add-TemporaryPinnedSqlServerModule -LocalModulesPath $LocalModulesPath
    }

    Write-Host "Importing pinned SqlServer module from: $manifestPath"
    Import-Module -Name $manifestPath -Force -DisableNameChecking -ErrorAction Stop
    Load-SqlServerAssemblies

    $runtime.UsedPinnedSqlServerModule = $true
    $runtime.SsisTypeAvailable = Test-IntegrationServicesTypeAvailable

    return $runtime
}

Function Get-Catalog
{
    # define parameters
    Param ($CatalogName)
    # NOTE: using $integrationServices variable defined in main

    # define working variables
    $Catalog = $null
    # check to see if there are any catalogs
    if($integrationServices.Catalogs.Count -gt 0 -and $integrationServices.Catalogs[$CatalogName])
    {
        # get reference to catalog
        $Catalog = $integrationServices.Catalogs[$CatalogName]
    }
    else
    {
        if((Get-CLREnabled) -eq 0)
        {
            if(-not $EnableCLR)
            {
                # throw error
                throw "SQL CLR is not enabled."
            }
            else
            {
                # display sql clr isn't enabled
                Write-Warning "SQL CLR is not enabled on $($sqlConnection.DataSource).  This feature must be enabled for SSIS catalogs."

                # enabling SQLCLR
                Write-Host "Enabling SQL CLR ..."
                Enable-SQLCLR
                Write-Host "SQL CLR enabled"
            }
        }

        # Provision a new SSIS Catalog
        Write-Host "Creating SSIS Catalog ..."

        $Catalog = New-Object "$ISNamespace.Catalog" ($integrationServices, $CatalogName, $OctopusParameters['SSIS.Template.CatalogPwd'])
        $Catalog.Create()
    }

    # return the catalog
    return $Catalog
}

Function Get-CLREnabled
{
    # define parameters
    # Not using any parameters, but am using $sqlConnection defined in main

    # define working variables
    $Query = "SELECT * FROM sys.configurations WHERE name = 'clr enabled'"

    # execute script
    $CLREnabled = Invoke-Sqlcmd -ServerInstance $sqlConnection.DataSource -Database "master" -Query $Query | Select value

    # return value
    return $CLREnabled.Value
}

Function Enable-SQLCLR
{
    $QueryArray = "sp_configure 'show advanced options', 1", "RECONFIGURE", "sp_configure 'clr enabled', 1", "RECONFIGURE "
    # execute script

    foreach($Query in $QueryArray)
    {
        Invoke-Sqlcmd -ServerInstance $sqlConnection.DataSource -Database "master" -Query $Query
    }

    # check that it's enabled
    if((Get-CLREnabled) -ne 1)
    {
        # throw error
        throw "Failed to enable SQL CLR"
    }
}

Function Get-Folder
{
    # parameters
    Param($FolderName, $Catalog)

    $Folder = $null
    # try to get reference to folder

    if(!($Catalog.Folders -eq $null))
    {
        $Folder = $Catalog.Folders[$FolderName]
    }

    # check to see if $Folder has a value
    if($Folder -eq $null)
    {
        # display
        Write-Host "Folder $FolderName doesn't exist, creating folder..."

        # create the folder
        $Folder = New-Object "$ISNamespace.CatalogFolder" ($Catalog, $FolderName, $FolderName)
        $Folder.Create()
    }

    # return the folder reference
    return $Folder
}

Function Get-Environment
{
    # define parameters
    Param($Folder, $EnvironmentName)

    $Environment = $null
    # get reference to Environment
    if(!($Folder.Environments -eq $null) -and $Folder.Environments.Count -gt 0)
    {
        $Environment = $Folder.Environments[$EnvironmentName]
    }

    # check to see if it's a null reference
    if($Environment -eq $null)
    {
        # display
        Write-Host "Environment $EnvironmentName doesn't exist, creating environment..."

        # create environment
        $Environment = New-Object "$ISNamespace.EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
        $Environment.Create()
    }

    # return the environment
    return $Environment
}

Function Set-EnvironmentReference
{
    # define parameters
    Param($Project, $Environment, $Folder)

    # get reference
    $Reference = $null

    if(!($Project.References -eq $null))
    {
        $Reference = $Project.References[$Environment.Name, $Folder.Name]
    }

    # check to see if it's a null reference
    if($Reference -eq $null)
    {
        # display
        Write-Host "Project does not reference environment $($Environment.Name), creating reference..."

        # create reference
        $Project.References.Add($Environment.Name, $Folder.Name)
        $Project.Alter()
    }
}

Function Set-ProjectParametersToEnvironmentVariablesReference
{
    # define parameters
    Param($Project, $Environment)

    $UpsertedVariables = @()

    if($Project.Parameters -eq $null)
    {
        Write-Host "No project parameters exist"
        return
    }

    # loop through project parameters
    foreach($Parameter in $Project.Parameters)
    {
        # skip if the parameter is included in custom filters
        if ($UseCustomFilter)
        {
            if ($Parameter.Name -match $CustomFilter)
            {
                Write-Host "- $($Parameter.Name) skipped due to CustomFilters."
                continue
            }
        }

        # Add variable to list of variable
        $UpsertedVariables += $Parameter.Name

        $Variable = $null
        if(!($Environment.Variables -eq $null))
        {
            # get reference to variable
            $Variable = $Environment.Variables[$Parameter.Name]
        }

        # check to see if variable exists
        if($Variable -eq $null)
        {
            # add the environment variable
            Add-EnvironmentVariable -Environment $Environment -Parameter $Parameter -ParameterName $Parameter.Name

            # get reference to the newly created variable
            $Variable = $Environment.Variables[$Parameter.Name]
        }

        # set the environment variable value
        Set-EnvironmentVariableValue -Variable $Variable -Parameter $Parameter -ParameterName $Parameter.Name
    }

    # alter the environment
    $Environment.Alter()
    $Project.Alter()

    return $UpsertedVariables
}

Function Set-PackageVariablesToEnvironmentVariablesReference
{
    # define parameters
    Param($Project, $Environment)

    $Variables = @()
    $UpsertedVariables = @()

    # loop through packages in project in order to store a temp collection of variables
    foreach($Package in $Project.Packages)
    {
        # loop through parameters of package
        foreach($Parameter in $Package.Parameters)
        {
            # add to the temporary variable collection
            $Variables += $Parameter.Name
        }
    }

    # loop through packages in project
    foreach($Package in $Project.Packages)
    {
        # loop through parameters of package
        foreach($Parameter in $Package.Parameters)
        {
            if ($UseFullyQualifiedVariableNames)
            {
                # Set fully qualified variable name
                $ParameterName = $Parameter.ObjectName.Replace(".dtsx", "")+"."+$Parameter.Name
            }
            else
            {
                # check if exists a variable with the same name
                $VariableNameOccurrences = $($Variables | Where-Object { $_ -eq $Parameter.Name }).count
                $ParameterName = $Parameter.Name

                if ($VariableNameOccurrences -gt 1)
                {
                    $ParameterName = $Parameter.ObjectName.Replace(".dtsx", "")+"."+$Parameter.Name
                }
            }

            if ($UseCustomFilter)
            {
                if ($ParameterName -match $CustomFilter)
                {
                    Write-Host "- $($Parameter.Name) skipped due to CustomFilters."
                    continue
                }
            }

            # get reference to variable
            $Variable = $Environment.Variables[$ParameterName]

            # Add variable to list of variable
            $UpsertedVariables += $ParameterName

            # check to see if the parameter exists
            if(!$Variable)
            {
                # add the environment variable
                Add-EnvironmentVariable -Environment $Environment -Parameter $Parameter -ParameterName $ParameterName

                # get reference to the newly created variable
                $Variable = $Environment.Variables[$ParameterName]
            }

            # set the environment variable value
            Set-EnvironmentVariableValue -Variable $Variable -Parameter $Parameter -ParameterName $ParameterName
        }

        # alter the package
        $Package.Alter()
    }

    # alter the environment
    $Environment.Alter()

    return $UpsertedVariables
}

Function Sync-EnvironmentVariables
{
    # define parameters
    Param($Environment, $VariablesToPreserveInEnvironment)

    foreach($VariableToEvaluate in $Environment.Variables)
    {
        if ($VariablesToPreserveInEnvironment -notcontains $VariableToEvaluate.Name)
        {
            Write-Host "- Removing environment variable: $($VariableToEvaluate.Name)"
            $VariableToRemove = $Environment.Variables[$VariableToEvaluate.Name]
            $Environment.Variables.Remove($VariableToRemove) | Out-Null
        }
    }

    # alter the environment
    $Environment.Alter()
}

Function Add-EnvironmentVariable
{
    # define parameters
    Param($Environment, $Parameter, $ParameterName)

    # display
    Write-Host "- Adding environment variable $($ParameterName)"

    # check to see if design default value is empty or null
    if([string]::IsNullOrEmpty($Parameter.DesignDefaultValue))
    {
        # give it something
        $DefaultValue = "" # sensitive variables will not return anything so when trying to use the property of $Parameter.DesignDefaultValue, the Alter method will fail.
    }
    else
    {
        # take the design
        $DefaultValue = $Parameter.DesignDefaultValue
    }

    # add variable with an initial value
    $Environment.Variables.Add($ParameterName, $Parameter.DataType, $DefaultValue, $Parameter.Sensitive, $Parameter.Description)
}

Function Set-EnvironmentVariableValue
{
     # define parameters
    Param($Variable, $Parameter, $ParameterName)

    # check to make sure variable value is available
    if (-not $OctopusParameters){
        Write-Host "[WARN] - OctopusParameters collection is empty"
    }
    else
    {
        if($OctopusParameters.ContainsKey($ParameterName))
        {
            # display
            Write-Host "[ OK ] - $($ParameterName) updated."

            # set the variable value
            $Variable.Value = $OctopusParameters["$($ParameterName)"]
        }
        else
        {
            # warning
            Write-Host "[WARN] - $($ParameterName) not in OctopusParameters collection."
        }
    }

    # Set reference
    $Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "$($ParameterName)")
}

Function Invoke-SsisProjectDeployment
{
    $LocalModules = (New-Item "$PSScriptRoot\Modules" -ItemType Directory -Force).FullName
    $pinnedSqlServerVersion = [version]'21.1.18256'
    $sqlConnection = $null
    $runtime = $null

    try
    {
        $runtime = Initialize-SsisDeploymentRuntime -LocalModulesPath $LocalModules -PinnedSqlServerVersion $pinnedSqlServerVersion

        # Store the IntegrationServices Assembly namespace to avoid typing it every time
        $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

        # ensure all boolean variables are true booleans
        $EnableCLR = [System.Convert]::ToBoolean("$($OctopusParameters['SSIS.Template.EnableCLR'])")
        $UseEnvironment = [System.Convert]::ToBoolean("$($OctopusParameters['SSIS.Template.UseEnvironment'])")
        $ReferenceProjectParametersToEnvironmentVairables = [System.Convert]::ToBoolean("$($OctopusParameters['SSIS.Template.ReferenceProjectParametersToEnvironmentVairables'])")

        $ReferencePackageParametersToEnvironmentVairables = [System.Convert]::ToBoolean("$($OctopusParameters['SSIS.Template.ReferencePackageParametersToEnvironmentVairables'])")
        $UseFullyQualifiedVariableNames = [System.Convert]::ToBoolean("$($OctopusParameters['SSIS.Template.UseFullyQualifiedVariableNames'])")
        $SyncEnvironment = [System.Convert]::ToBoolean("$($OctopusParameters['SSIS.Template.SyncEnvironment'])")
        # custom names for filtering out the excluded variables by design
        $UseCustomFilter = [System.Convert]::ToBoolean("$($OctopusParameters['SSIS.Template.UseCustomFilter'])")
        $CustomFilter = [System.Convert]::ToString("$($OctopusParameters['SSIS.Template.CustomFilter'])")
        # list of variables names to keep in target environment
        $VariablesToPreserveInEnvironment = @()
        $ssisPackageId = $OctopusParameters['SSIS.Template.ssisPackageId']

        # Get the extracted path
        $DeployedPath = $OctopusParameters["Octopus.Action.Package[$ssisPackageId].ExtractedPath"]

        # Get all .ispac files from the deployed path
        $IsPacFiles = Get-ChildItem -Recurse -Path $DeployedPath | Where {$_.Extension.ToLower() -eq ".ispac"}

        # display number of files
        Write-Host "$($IsPacFiles.Count) .ispac file(s) found."

        Write-Host "Connecting to server ..."

        # Create a connection to the server
        $sqlConnectionString = "Data Source=$($OctopusParameters['SSIS.Template.ServerName']);Initial Catalog=SSISDB;"

        if (![string]::IsNullOrEmpty($OctopusParameters['SSIS.Template.sqlAccountUsername']) -and ![string]::IsNullOrEmpty($OctopusParameters['SSIS.Template.sqlAccountPassword']))
        {
            # Add username and password to connection string
            $sqlConnectionString += "User ID=$($OctopusParameters['SSIS.Template.sqlAccountUsername']); Password=$($OctopusParameters['SSIS.Template.sqlAccountPassword']);"
        }
        else
        {
            # Use integrated
            $sqlConnectionString += "Integrated Security=SSPI;"
        }

        # Create new connection object with connection string
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

        # create integration services object
        $integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection

        # get reference to the catalog
        Write-Host "Getting reference to catalog $($OctopusParameters['SSIS.Template.CataLogName'])"
        $Catalog = Get-Catalog -CatalogName $OctopusParameters['SSIS.Template.CataLogName']

        # get folder reference
        $Folder = Get-Folder -FolderName $OctopusParameters['SSIS.Template.FolderName'] -Catalog $Catalog

        # loop through ispac files
        foreach($IsPacFile in $IsPacFiles)
        {
            # read project file
            $ProjectFile = [System.IO.File]::ReadAllBytes($IsPacFile.FullName)
            $ProjectName = $IsPacFile.Name.SubString(0, $IsPacFile.Name.LastIndexOf("."))

            # deploy project
            Write-Host "Deploying project $($IsPacFile.Name)..."
            $Folder.DeployProject($ProjectName, $ProjectFile) | Out-Null

            # get reference to deployed project
            $Project = $Folder.Projects[$ProjectName]

            # check to see if they want to use environments
            if($UseEnvironment)
            {
                # get environment reference
                $Environment = Get-Environment -Folder $Folder -EnvironmentName $OctopusParameters['SSIS.Template.EnvironmentName']

                # set environment reference
                Set-EnvironmentReference -Project $Project -Environment $Environment -Folder $Folder

                # check to see if the user wants to convert project parameters to environment variables
                if($ReferenceProjectParametersToEnvironmentVairables)
                {
                    # set environment variables
                    Write-Host "Referencing Project Parameters to Environment Variables..."
                    $VariablesToPreserveInEnvironment += Set-ProjectParametersToEnvironmentVariablesReference -Project $Project -Environment $Environment
                }

                # check to see if the user wants to convert the package parameters to environment variables
                if($ReferencePackageParametersToEnvironmentVairables)
                {
                    # set package variables
                    Write-Host "Referencing Package Parameters to Environment Variables..."
                    $VariablesToPreserveInEnvironment += Set-PackageVariablesToEnvironmentVariablesReference -Project $Project -Environment $Environment
                }

                # Removes all unused variables from the environment
                if ($SyncEnvironment)
                {
                    Write-Host "Sync package environment variables..."
                    Sync-EnvironmentVariables -Environment $Environment -VariablesToPreserveInEnvironment $VariablesToPreserveInEnvironment
                }
            }
        }
    }

    finally
    {
        # check to make sure sqlconnection isn't null
        if($sqlConnection)
        {
            # check state of sqlconnection
            if($sqlConnection.State -eq [System.Data.ConnectionState]::Open)
            {
                # close the connection
                $sqlConnection.Close()
            }

            # cleanup
            $sqlConnection.Dispose()
        }

        # cleanup temporary pinned SqlServer module only when it was actually used
        if ($runtime -and $runtime.UsedPinnedSqlServerModule)
        {
            Remove-TemporaryPinnedSqlServerModule -LocalModulesPath $LocalModules -PinnedVersion $pinnedSqlServerVersion
        }
    }
}

Invoke-SsisProjectDeployment

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": "27567d46-b935-4ee6-8b2d-8c165edada4e",
  "Name": "Deploy ispac SSIS project from a Package parameter",
  "Description": "This step template will deploy SSIS ispac projects to SQL Server Integration Services Catalog.  The template uses a referenced package and is Worker compatible.\n\nThis template will install the Nuget package provider if it is not present on the machine it is running on.\n\nNOTE:  The SqlServer PowerShell module this template utilizes removed the assemblies necessary to interface with SSIS as of version 22.0.59.  Version 21.1.18256 has been pinned and will be used if the SqlServer PowerShell module is not installed.",
  "Version": 7,
  "ExportedAt": "2026-03-13T09:17:00.0000000",
  "ActionType": "Octopus.Script",
  "Author": "bcullman",
  "Packages": [
    {
      "Id": "5ce9da08-a4ed-4b69-92d1-ab88c705cf08",
      "Name": "SSIS.Template.ssisPackageId",
      "PackageId": null,
      "FeedId": null,
      "AcquisitionLocation": "Server",
      "Properties": {
        "Extract": "True",
        "SelectionMode": "deferred",
        "PackageParameterName": "SSIS.Template.ssisPackageId"
      }
    }
  ],
  "Parameters": [
    {
      "Id": "53aa9e3e-1292-4e75-8095-3666ebd5886b",
      "Name": "SSIS.Template.ServerName",
      "Label": "Database server name (\\instance)",
      "HelpText": "Name of the SQL Server you are deploying to.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "c0605196-f184-44f6-9b3d-c043323e017c",
      "Name": "SSIS.Template.sqlAccountUsername",
      "Label": "SQL Authentication Username",
      "HelpText": "(Optional) Username of the SQL Authentication account.  Use this approach when deploying to Azure Databases with SSIS configured.  If SQL Authentication Username and Password are blank, Integrated Authentication is used.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "224b913f-657c-4d1c-a3d8-0f286203c1ec",
      "Name": "SSIS.Template.sqlAccountPassword",
      "Label": "SQL Authentication Password",
      "HelpText": "(Optional) Password of the SQL Authentication account.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Id": "0abbb06c-5f98-44d8-b20d-b811b6bf18da",
      "Name": "SSIS.Template.EnableCLR",
      "Label": "Enable SQL CLR",
      "HelpText": "This will reconfigure SQL Server to enable the SQL CLR.  It is highly recommended that this be previously authorized by your Database Administrator.",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "ebe9ab8b-3433-4aeb-bafb-642033a9b0a0",
      "Name": "SSIS.Template.CatalogName",
      "Label": "Catalog name",
      "HelpText": "Name of the catalog to create in Integration Services Catalogs on SQL Server.  When using the GUI, this value gets hardcoded to SSISDB and cannot be changed.  It is recommended that you do not change the default value.",
      "DefaultValue": "SSISDB",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "d111401b-504f-41f4-96de-b7667fcbf990",
      "Name": "SSIS.Template.CatalogPwd",
      "Label": "Catalog password",
      "HelpText": "Password to the Integration Services Catalog.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Sensitive"
      }
    },
    {
      "Id": "d2fefbf6-c68f-4a66-a5d1-3beb9e51a331",
      "Name": "SSIS.Template.FolderName",
      "Label": "Folder name",
      "HelpText": "Name of the folder to use within the Integration Services Catalog",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "ec3589c1-360b-44fc-a19c-f2de0f57a323",
      "Name": "SSIS.Template.UseEnvironment",
      "Label": "Use environment",
      "HelpText": "This will make a project reference to the defined environment.",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "718f2836-1c49-4119-a294-9b5488ead33b",
      "Name": "SSIS.Template.EnvironmentName",
      "Label": "Environment name",
      "HelpText": "Name of the environment to reference the project to. If the environment doesn't exist, it will create it.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "303dd486-a389-48c2-a653-fe881c3b7d9a",
      "Name": "SSIS.Template.ReferenceProjectParametersToEnvironmentVairables",
      "Label": "Reference project parameters to environment variables",
      "HelpText": "Checking this box will make Project Parameters reference Environment Variables.  If the Environment Variable doesn't exist, it will create it.  This expects that an Octopus variable of the same name exists.",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "06079e96-3c10-4137-9c99-83da2051f8be",
      "Name": "SSIS.Template.ReferencePackageParametersToEnvironmentVairables",
      "Label": "Reference package parameters to environment variables",
      "HelpText": "Checking this box will make Package Parameters reference Environment Variables.  If the Environment Variable doesn't exist, it will create it.  This expects than an Octopus variable of the same name exists.",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "8c219bf1-0715-4c21-a131-f7bfb44690cd",
      "Name": "SSIS.Template.UseFullyQualifiedVariableNames",
      "Label": "Use Fully Qualified Variable Names",
      "HelpText": "When true the package variables names must be represented in `dtsx_name_without_extension.variable_name`",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "6b29c969-3928-4faf-af17-0911d777d5b1",
      "Name": "SSIS.Template.UseCustomFilter",
      "Label": "Use Custom Filter for connection manager properties",
      "HelpText": "Custom filter should contain the regular expression for ignoring properties when setting will occur during the auto-mapping",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "b33575e2-7246-4c04-b4fa-cd58e2b5e516",
      "Name": "SSIS.Template.CustomFilter",
      "Label": "Custom Filter for connection manager properties",
      "HelpText": "Regular expression for filtering out the connection manager properties during the auto-mapping process. This string is used when `UseCustomFilter` is set to true",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "SingleLineText"
      }
    },
    {
      "Id": "92b546d9-abf3-45e4-a61b-0315c21455df",
      "Name": "SSIS.Template.SyncEnvironment",
      "Label": "Clean obsolete variables from environment",
      "HelpText": "When `true` synchronizes the environment:\n- Removes obsolete variables\n- Removes renamed variables\n- Replaces values of valid variables (also when `false`)",
      "DefaultValue": "False",
      "DisplaySettings": {
        "Octopus.ControlType": "Checkbox"
      }
    },
    {
      "Id": "6a1fa441-9e14-4c13-b7f3-9c932d9b4e8e",
      "Name": "SSIS.Template.ssisPackageId",
      "Label": "Package Id",
      "HelpText": "Id of the package to deploy, used to support deployment with Workers.",
      "DefaultValue": "",
      "DisplaySettings": {
        "Octopus.ControlType": "Package"
      }
    }
  ],
  "Properties": {
    "Octopus.Action.Script.Syntax": "PowerShell",
    "Octopus.Action.Script.ScriptBody": "function Add-TemporaryPinnedSqlServerModule\n{\n    # Define parameters\n    param(\n        $LocalModulesPath\n    )\n\n    $PowerShellModuleName = \"SqlServer\"\n    $RequiredVersion = [version]'21.1.18256'\n\n    # Check to see if the package provider has been installed\n    if ($null -eq (Get-PackageProvider -ListAvailable -Name Nuget -ErrorAction SilentlyContinue))\n    {\n        # Display that we need the nuget package provider\n        Write-Host \"Nuget package provider not found, installing ...\"\n\n        # Install Nuget package provider\n        Install-PackageProvider -Name Nuget -Force\n    }\n\n    # Save the module in the temporary location\n    Save-Module -Name $PowerShellModuleName -Path $LocalModulesPath -Force -RequiredVersion $RequiredVersion -Repository \"PSGallery\"\n\n    # Display\n    Write-Output \"Saved module $PowerShellModuleName v$RequiredVersion to $LocalModulesPath\"\n\n    # Import the module\n    Import-Module -Name $PowerShellModuleName\n}\n\nFunction Remove-TemporaryPinnedSqlServerModule\n{\n    param(\n        [Parameter(Mandatory = $true)][string]$LocalModulesPath,\n        [Parameter(Mandatory = $true)][version]$PinnedVersion\n    )\n\n    try { Remove-Module SqlServer -Force -ErrorAction SilentlyContinue } catch {}\n\n    $moduleRoot = Join-Path $LocalModulesPath 'SqlServer'\n    $pinnedPath = Join-Path $moduleRoot $PinnedVersion.ToString()\n\n    if (Test-Path $pinnedPath)\n    {\n        Write-Host \"Removing temporary pinned SqlServer module folder: $pinnedPath\"\n        Remove-Item -Path $pinnedPath -Recurse -Force -ErrorAction SilentlyContinue\n    }\n\n    # Optional: remove parent if empty\n    if (Test-Path $moduleRoot)\n    {\n        $remaining = Get-ChildItem -Path $moduleRoot -Force -ErrorAction SilentlyContinue\n        if (-not $remaining)\n        {\n            Remove-Item -Path $moduleRoot -Force -ErrorAction SilentlyContinue\n        }\n    }\n}\n\nFunction Test-IntegrationServicesTypeAvailable\n{\n    # True if the IntegrationServices type resolves in this PowerShell session, else false\n    $typeName = 'Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices'\n    $aqn = \"$typeName, Microsoft.SqlServer.Management.IntegrationServices\"\n\n    try\n    {\n        if ([Type]::GetType($aqn, $false)) { return $true }\n    }\n    catch {}\n\n    # Try to load assembly (strong name), may fail on some boxes\n    try { [void][Reflection.Assembly]::Load('Microsoft.SqlServer.Management.IntegrationServices') } catch {}\n\n    try\n    {\n        if ([Type]::GetType($aqn, $false)) { return $true }\n    }\n    catch {}\n\n    # Fall back to explicit GAC path load (LoadFrom) if present\n    $gacRoot = Join-Path $env:windir 'Microsoft.NET\\assembly\\GAC_MSIL\\Microsoft.SqlServer.Management.IntegrationServices'\n    if (Test-Path $gacRoot)\n    {\n        $gacDll = Get-ChildItem -Path $gacRoot -Recurse -Filter 'Microsoft.SqlServer.Management.IntegrationServices.dll' -File -ErrorAction SilentlyContinue |\n                  Sort-Object FullName -Descending |\n                  Select-Object -First 1\n\n        if ($null -eq $gacDll)\n        {\n            try\n            {\n                $asm = [Reflection.Assembly]::LoadFrom($gacDll.FullName)\n                if ($asm.GetType('Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices', $false)) { return $true }\n            }\n            catch {}\n        }\n    }\n\n    try\n    {\n        if ([Type]::GetType($aqn, $false)) { return $true }\n    }\n    catch {}\n\n    return $false\n}\n\nFunction Load-SqlServerAssemblies\n{\n    # Get the folder where the SqlServer module ended up in\n    $sqlServerModulePath = [System.IO.Path]::GetDirectoryName((Get-Module SqlServer).Path)\n\n    # Loop through the assemblies\n    foreach($assemblyFile in (Get-ChildItem -Path $sqlServerModulePath -Exclude msv*.dll | Where-Object {$_.Extension -eq \".dll\"}))\n    {\n        try\n        {\n            # Only load managed .NET assemblies (native DLLs will throw)\n            [void][System.Reflection.AssemblyName]::GetAssemblyName($assemblyFile.FullName)\n\n            # Load the managed assembly\n            [Reflection.Assembly]::LoadFrom($assemblyFile.FullName) | Out-Null\n        }\n        catch [System.BadImageFormatException]\n        {\n            # Native DLL (or wrong format), skip\n        }\n    }\n}\n\nFunction Initialize-SsisDeploymentRuntime\n{\n    param(\n        [Parameter(Mandatory = $true)][string]$LocalModulesPath,\n        [Parameter(Mandatory = $true)][version]$PinnedSqlServerVersion\n    )\n\n    # Define PowerShell Modules path\n    $env:PSModulePath = \"$LocalModulesPath;$env:PSModulePath\"\n\n    $runtime = [pscustomobject]@{\n        UsedPinnedSqlServerModule = $false\n        SsisTypeAvailable         = $false\n    }\n\n    # First preference: use SSIS assemblies already available on the machine/session\n    $runtime.SsisTypeAvailable = Test-IntegrationServicesTypeAvailable\n\n    if ($runtime.SsisTypeAvailable)\n    {\n        Write-Host \"SSIS IntegrationServices type is already available.\"\n\n        # Only need a SqlServer module for cmdlets such as Invoke-Sqlcmd\n        if ($null -ne (Get-Module -ListAvailable -Name \"SqlServer\"))\n        {\n            Import-Module -Name SqlServer -ErrorAction Stop\n        }\n        else\n        {\n            Write-Output \"PowerShell module SqlServer not present, downloading temporary pinned copy for cmdlets ...\"\n            [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [System.Net.SecurityProtocolType]::Tls12\n\n            Add-TemporaryPinnedSqlServerModule -LocalModulesPath $LocalModulesPath\n\n            $manifestPath = Join-Path (Join-Path (Join-Path $LocalModulesPath 'SqlServer') $PinnedSqlServerVersion.ToString()) 'SqlServer.psd1'\n            Write-Host \"Importing pinned SqlServer module from: $manifestPath\"\n            Import-Module -Name $manifestPath -Force -DisableNameChecking -ErrorAction Stop\n\n            $runtime.UsedPinnedSqlServerModule = $true\n        }\n\n        # SSIS type already resolved, no need to bulk-load module assemblies\n        return $runtime\n    }\n\n    # Second preference: fall back to pinned SqlServer module that still contains SSIS assemblies\n    Write-Output \"SSIS IntegrationServices type not available via installed components. Downloading pinned SqlServer module $PinnedSqlServerVersion temporarily ...\"\n    [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor [System.Net.SecurityProtocolType]::Tls12\n\n    $manifestPath = Join-Path (Join-Path (Join-Path $LocalModulesPath 'SqlServer') $PinnedSqlServerVersion.ToString()) 'SqlServer.psd1'\n    if (-not (Test-Path $manifestPath))\n    {\n        Add-TemporaryPinnedSqlServerModule -LocalModulesPath $LocalModulesPath\n    }\n\n    Write-Host \"Importing pinned SqlServer module from: $manifestPath\"\n    Import-Module -Name $manifestPath -Force -DisableNameChecking -ErrorAction Stop\n    Load-SqlServerAssemblies\n\n    $runtime.UsedPinnedSqlServerModule = $true\n    $runtime.SsisTypeAvailable = Test-IntegrationServicesTypeAvailable\n\n    return $runtime\n}\n\nFunction Get-Catalog\n{\n    # define parameters\n    Param ($CatalogName)\n    # NOTE: using $integrationServices variable defined in main\n\n    # define working variables\n    $Catalog = $null\n    # check to see if there are any catalogs\n    if($integrationServices.Catalogs.Count -gt 0 -and $integrationServices.Catalogs[$CatalogName])\n    {\n        # get reference to catalog\n        $Catalog = $integrationServices.Catalogs[$CatalogName]\n    }\n    else\n    {\n        if((Get-CLREnabled) -eq 0)\n        {\n            if(-not $EnableCLR)\n            {\n                # throw error\n                throw \"SQL CLR is not enabled.\"\n            }\n            else\n            {\n                # display sql clr isn't enabled\n                Write-Warning \"SQL CLR is not enabled on $($sqlConnection.DataSource).  This feature must be enabled for SSIS catalogs.\"\n\n                # enabling SQLCLR\n                Write-Host \"Enabling SQL CLR ...\"\n                Enable-SQLCLR\n                Write-Host \"SQL CLR enabled\"\n            }\n        }\n\n        # Provision a new SSIS Catalog\n        Write-Host \"Creating SSIS Catalog ...\"\n\n        $Catalog = New-Object \"$ISNamespace.Catalog\" ($integrationServices, $CatalogName, $OctopusParameters['SSIS.Template.CatalogPwd'])\n        $Catalog.Create()\n    }\n\n    # return the catalog\n    return $Catalog\n}\n\nFunction Get-CLREnabled\n{\n    # define parameters\n    # Not using any parameters, but am using $sqlConnection defined in main\n\n    # define working variables\n    $Query = \"SELECT * FROM sys.configurations WHERE name = 'clr enabled'\"\n\n    # execute script\n    $CLREnabled = Invoke-Sqlcmd -ServerInstance $sqlConnection.DataSource -Database \"master\" -Query $Query | Select value\n\n    # return value\n    return $CLREnabled.Value\n}\n\nFunction Enable-SQLCLR\n{\n    $QueryArray = \"sp_configure 'show advanced options', 1\", \"RECONFIGURE\", \"sp_configure 'clr enabled', 1\", \"RECONFIGURE \"\n    # execute script\n\n    foreach($Query in $QueryArray)\n    {\n        Invoke-Sqlcmd -ServerInstance $sqlConnection.DataSource -Database \"master\" -Query $Query\n    }\n\n    # check that it's enabled\n    if((Get-CLREnabled) -ne 1)\n    {\n        # throw error\n        throw \"Failed to enable SQL CLR\"\n    }\n}\n\nFunction Get-Folder\n{\n    # parameters\n    Param($FolderName, $Catalog)\n\n    $Folder = $null\n    # try to get reference to folder\n\n    if(!($Catalog.Folders -eq $null))\n    {\n        $Folder = $Catalog.Folders[$FolderName]\n    }\n\n    # check to see if $Folder has a value\n    if($Folder -eq $null)\n    {\n        # display\n        Write-Host \"Folder $FolderName doesn't exist, creating folder...\"\n\n        # create the folder\n        $Folder = New-Object \"$ISNamespace.CatalogFolder\" ($Catalog, $FolderName, $FolderName)\n        $Folder.Create()\n    }\n\n    # return the folder reference\n    return $Folder\n}\n\nFunction Get-Environment\n{\n    # define parameters\n    Param($Folder, $EnvironmentName)\n\n    $Environment = $null\n    # get reference to Environment\n    if(!($Folder.Environments -eq $null) -and $Folder.Environments.Count -gt 0)\n    {\n        $Environment = $Folder.Environments[$EnvironmentName]\n    }\n\n    # check to see if it's a null reference\n    if($Environment -eq $null)\n    {\n        # display\n        Write-Host \"Environment $EnvironmentName doesn't exist, creating environment...\"\n\n        # create environment\n        $Environment = New-Object \"$ISNamespace.EnvironmentInfo\" ($Folder, $EnvironmentName, $EnvironmentName)\n        $Environment.Create()\n    }\n\n    # return the environment\n    return $Environment\n}\n\nFunction Set-EnvironmentReference\n{\n    # define parameters\n    Param($Project, $Environment, $Folder)\n\n    # get reference\n    $Reference = $null\n\n    if(!($Project.References -eq $null))\n    {\n        $Reference = $Project.References[$Environment.Name, $Folder.Name]\n    }\n\n    # check to see if it's a null reference\n    if($Reference -eq $null)\n    {\n        # display\n        Write-Host \"Project does not reference environment $($Environment.Name), creating reference...\"\n\n        # create reference\n        $Project.References.Add($Environment.Name, $Folder.Name)\n        $Project.Alter()\n    }\n}\n\nFunction Set-ProjectParametersToEnvironmentVariablesReference\n{\n    # define parameters\n    Param($Project, $Environment)\n\n    $UpsertedVariables = @()\n\n    if($Project.Parameters -eq $null)\n    {\n        Write-Host \"No project parameters exist\"\n        return\n    }\n\n    # loop through project parameters\n    foreach($Parameter in $Project.Parameters)\n    {\n        # skip if the parameter is included in custom filters\n        if ($UseCustomFilter)\n        {\n            if ($Parameter.Name -match $CustomFilter)\n            {\n                Write-Host \"- $($Parameter.Name) skipped due to CustomFilters.\"\n                continue\n            }\n        }\n\n        # Add variable to list of variable\n        $UpsertedVariables += $Parameter.Name\n\n        $Variable = $null\n        if(!($Environment.Variables -eq $null))\n        {\n            # get reference to variable\n            $Variable = $Environment.Variables[$Parameter.Name]\n        }\n\n        # check to see if variable exists\n        if($Variable -eq $null)\n        {\n            # add the environment variable\n            Add-EnvironmentVariable -Environment $Environment -Parameter $Parameter -ParameterName $Parameter.Name\n\n            # get reference to the newly created variable\n            $Variable = $Environment.Variables[$Parameter.Name]\n        }\n\n        # set the environment variable value\n        Set-EnvironmentVariableValue -Variable $Variable -Parameter $Parameter -ParameterName $Parameter.Name\n    }\n\n    # alter the environment\n    $Environment.Alter()\n    $Project.Alter()\n\n    return $UpsertedVariables\n}\n\nFunction Set-PackageVariablesToEnvironmentVariablesReference\n{\n    # define parameters\n    Param($Project, $Environment)\n\n    $Variables = @()\n    $UpsertedVariables = @()\n\n    # loop through packages in project in order to store a temp collection of variables\n    foreach($Package in $Project.Packages)\n    {\n        # loop through parameters of package\n        foreach($Parameter in $Package.Parameters)\n        {\n            # add to the temporary variable collection\n            $Variables += $Parameter.Name\n        }\n    }\n\n    # loop through packages in project\n    foreach($Package in $Project.Packages)\n    {\n        # loop through parameters of package\n        foreach($Parameter in $Package.Parameters)\n        {\n            if ($UseFullyQualifiedVariableNames)\n            {\n                # Set fully qualified variable name\n                $ParameterName = $Parameter.ObjectName.Replace(\".dtsx\", \"\")+\".\"+$Parameter.Name\n            }\n            else\n            {\n                # check if exists a variable with the same name\n                $VariableNameOccurrences = $($Variables | Where-Object { $_ -eq $Parameter.Name }).count\n                $ParameterName = $Parameter.Name\n\n                if ($VariableNameOccurrences -gt 1)\n                {\n                    $ParameterName = $Parameter.ObjectName.Replace(\".dtsx\", \"\")+\".\"+$Parameter.Name\n                }\n            }\n\n            if ($UseCustomFilter)\n            {\n                if ($ParameterName -match $CustomFilter)\n                {\n                    Write-Host \"- $($Parameter.Name) skipped due to CustomFilters.\"\n                    continue\n                }\n            }\n\n            # get reference to variable\n            $Variable = $Environment.Variables[$ParameterName]\n\n            # Add variable to list of variable\n            $UpsertedVariables += $ParameterName\n\n            # check to see if the parameter exists\n            if(!$Variable)\n            {\n                # add the environment variable\n                Add-EnvironmentVariable -Environment $Environment -Parameter $Parameter -ParameterName $ParameterName\n\n                # get reference to the newly created variable\n                $Variable = $Environment.Variables[$ParameterName]\n            }\n\n            # set the environment variable value\n            Set-EnvironmentVariableValue -Variable $Variable -Parameter $Parameter -ParameterName $ParameterName\n        }\n\n        # alter the package\n        $Package.Alter()\n    }\n\n    # alter the environment\n    $Environment.Alter()\n\n    return $UpsertedVariables\n}\n\nFunction Sync-EnvironmentVariables\n{\n    # define parameters\n    Param($Environment, $VariablesToPreserveInEnvironment)\n\n    foreach($VariableToEvaluate in $Environment.Variables)\n    {\n        if ($VariablesToPreserveInEnvironment -notcontains $VariableToEvaluate.Name)\n        {\n            Write-Host \"- Removing environment variable: $($VariableToEvaluate.Name)\"\n            $VariableToRemove = $Environment.Variables[$VariableToEvaluate.Name]\n            $Environment.Variables.Remove($VariableToRemove) | Out-Null\n        }\n    }\n\n    # alter the environment\n    $Environment.Alter()\n}\n\nFunction Add-EnvironmentVariable\n{\n    # define parameters\n    Param($Environment, $Parameter, $ParameterName)\n\n    # display\n    Write-Host \"- Adding environment variable $($ParameterName)\"\n\n    # check to see if design default value is empty or null\n    if([string]::IsNullOrEmpty($Parameter.DesignDefaultValue))\n    {\n        # give it something\n        $DefaultValue = \"\" # sensitive variables will not return anything so when trying to use the property of $Parameter.DesignDefaultValue, the Alter method will fail.\n    }\n    else\n    {\n        # take the design\n        $DefaultValue = $Parameter.DesignDefaultValue\n    }\n\n    # add variable with an initial value\n    $Environment.Variables.Add($ParameterName, $Parameter.DataType, $DefaultValue, $Parameter.Sensitive, $Parameter.Description)\n}\n\nFunction Set-EnvironmentVariableValue\n{\n     # define parameters\n    Param($Variable, $Parameter, $ParameterName)\n\n    # check to make sure variable value is available\n    if (-not $OctopusParameters){\n        Write-Host \"[WARN] - OctopusParameters collection is empty\"\n    }\n    else\n    {\n        if($OctopusParameters.ContainsKey($ParameterName))\n        {\n            # display\n            Write-Host \"[ OK ] - $($ParameterName) updated.\"\n\n            # set the variable value\n            $Variable.Value = $OctopusParameters[\"$($ParameterName)\"]\n        }\n        else\n        {\n            # warning\n            Write-Host \"[WARN] - $($ParameterName) not in OctopusParameters collection.\"\n        }\n    }\n\n    # Set reference\n    $Parameter.Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, \"$($ParameterName)\")\n}\n\nFunction Invoke-SsisProjectDeployment\n{\n    $LocalModules = (New-Item \"$PSScriptRoot\\Modules\" -ItemType Directory -Force).FullName\n    $pinnedSqlServerVersion = [version]'21.1.18256'\n    $sqlConnection = $null\n    $runtime = $null\n\n    try\n    {\n        $runtime = Initialize-SsisDeploymentRuntime -LocalModulesPath $LocalModules -PinnedSqlServerVersion $pinnedSqlServerVersion\n\n        # Store the IntegrationServices Assembly namespace to avoid typing it every time\n        $ISNamespace = \"Microsoft.SqlServer.Management.IntegrationServices\"\n\n        # ensure all boolean variables are true booleans\n        $EnableCLR = [System.Convert]::ToBoolean(\"$($OctopusParameters['SSIS.Template.EnableCLR'])\")\n        $UseEnvironment = [System.Convert]::ToBoolean(\"$($OctopusParameters['SSIS.Template.UseEnvironment'])\")\n        $ReferenceProjectParametersToEnvironmentVairables = [System.Convert]::ToBoolean(\"$($OctopusParameters['SSIS.Template.ReferenceProjectParametersToEnvironmentVairables'])\")\n\n        $ReferencePackageParametersToEnvironmentVairables = [System.Convert]::ToBoolean(\"$($OctopusParameters['SSIS.Template.ReferencePackageParametersToEnvironmentVairables'])\")\n        $UseFullyQualifiedVariableNames = [System.Convert]::ToBoolean(\"$($OctopusParameters['SSIS.Template.UseFullyQualifiedVariableNames'])\")\n        $SyncEnvironment = [System.Convert]::ToBoolean(\"$($OctopusParameters['SSIS.Template.SyncEnvironment'])\")\n        # custom names for filtering out the excluded variables by design\n        $UseCustomFilter = [System.Convert]::ToBoolean(\"$($OctopusParameters['SSIS.Template.UseCustomFilter'])\")\n        $CustomFilter = [System.Convert]::ToString(\"$($OctopusParameters['SSIS.Template.CustomFilter'])\")\n        # list of variables names to keep in target environment\n        $VariablesToPreserveInEnvironment = @()\n        $ssisPackageId = $OctopusParameters['SSIS.Template.ssisPackageId']\n\n        # Get the extracted path\n        $DeployedPath = $OctopusParameters[\"Octopus.Action.Package[$ssisPackageId].ExtractedPath\"]\n\n        # Get all .ispac files from the deployed path\n        $IsPacFiles = Get-ChildItem -Recurse -Path $DeployedPath | Where {$_.Extension.ToLower() -eq \".ispac\"}\n\n        # display number of files\n        Write-Host \"$($IsPacFiles.Count) .ispac file(s) found.\"\n\n        Write-Host \"Connecting to server ...\"\n\n        # Create a connection to the server\n        $sqlConnectionString = \"Data Source=$($OctopusParameters['SSIS.Template.ServerName']);Initial Catalog=SSISDB;\"\n\n        if (![string]::IsNullOrEmpty($OctopusParameters['SSIS.Template.sqlAccountUsername']) -and ![string]::IsNullOrEmpty($OctopusParameters['SSIS.Template.sqlAccountPassword']))\n        {\n            # Add username and password to connection string\n            $sqlConnectionString += \"User ID=$($OctopusParameters['SSIS.Template.sqlAccountUsername']); Password=$($OctopusParameters['SSIS.Template.sqlAccountPassword']);\"\n        }\n        else\n        {\n            # Use integrated\n            $sqlConnectionString += \"Integrated Security=SSPI;\"\n        }\n\n        # Create new connection object with connection string\n        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString\n\n        # create integration services object\n        $integrationServices = New-Object \"$ISNamespace.IntegrationServices\" $sqlConnection\n\n        # get reference to the catalog\n        Write-Host \"Getting reference to catalog $($OctopusParameters['SSIS.Template.CataLogName'])\"\n        $Catalog = Get-Catalog -CatalogName $OctopusParameters['SSIS.Template.CataLogName']\n\n        # get folder reference\n        $Folder = Get-Folder -FolderName $OctopusParameters['SSIS.Template.FolderName'] -Catalog $Catalog\n\n        # loop through ispac files\n        foreach($IsPacFile in $IsPacFiles)\n        {\n            # read project file\n            $ProjectFile = [System.IO.File]::ReadAllBytes($IsPacFile.FullName)\n            $ProjectName = $IsPacFile.Name.SubString(0, $IsPacFile.Name.LastIndexOf(\".\"))\n\n            # deploy project\n            Write-Host \"Deploying project $($IsPacFile.Name)...\"\n            $Folder.DeployProject($ProjectName, $ProjectFile) | Out-Null\n\n            # get reference to deployed project\n            $Project = $Folder.Projects[$ProjectName]\n\n            # check to see if they want to use environments\n            if($UseEnvironment)\n            {\n                # get environment reference\n                $Environment = Get-Environment -Folder $Folder -EnvironmentName $OctopusParameters['SSIS.Template.EnvironmentName']\n\n                # set environment reference\n                Set-EnvironmentReference -Project $Project -Environment $Environment -Folder $Folder\n\n                # check to see if the user wants to convert project parameters to environment variables\n                if($ReferenceProjectParametersToEnvironmentVairables)\n                {\n                    # set environment variables\n                    Write-Host \"Referencing Project Parameters to Environment Variables...\"\n                    $VariablesToPreserveInEnvironment += Set-ProjectParametersToEnvironmentVariablesReference -Project $Project -Environment $Environment\n                }\n\n                # check to see if the user wants to convert the package parameters to environment variables\n                if($ReferencePackageParametersToEnvironmentVairables)\n                {\n                    # set package variables\n                    Write-Host \"Referencing Package Parameters to Environment Variables...\"\n                    $VariablesToPreserveInEnvironment += Set-PackageVariablesToEnvironmentVariablesReference -Project $Project -Environment $Environment\n                }\n\n                # Removes all unused variables from the environment\n                if ($SyncEnvironment)\n                {\n                    Write-Host \"Sync package environment variables...\"\n                    Sync-EnvironmentVariables -Environment $Environment -VariablesToPreserveInEnvironment $VariablesToPreserveInEnvironment\n                }\n            }\n        }\n    }\n\n    finally\n    {\n        # check to make sure sqlconnection isn't null\n        if($sqlConnection)\n        {\n            # check state of sqlconnection\n            if($sqlConnection.State -eq [System.Data.ConnectionState]::Open)\n            {\n                # close the connection\n                $sqlConnection.Close()\n            }\n\n            # cleanup\n            $sqlConnection.Dispose()\n        }\n\n        # cleanup temporary pinned SqlServer module only when it was actually used\n        if ($runtime -and $runtime.UsedPinnedSqlServerModule)\n        {\n            Remove-TemporaryPinnedSqlServerModule -LocalModulesPath $LocalModules -PinnedVersion $pinnedSqlServerVersion\n        }\n    }\n}\n\nInvoke-SsisProjectDeployment",
    "Octopus.Action.Script.ScriptSource": "Inline"
  },
  "Category": "SQL Server",
  "HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates/ssis-deploy-ispac-from-package-parameter.json",
  "Website": "/step-templates/27567d46-b935-4ee6-8b2d-8c165edada4e",
  "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 Friday, March 13, 2026