Automating Deployment of SSIS 3) Creating an Environment

This post follows on publishing an SSIS Project and continues the automation process.

Why ispac files do not facilitate the generation of an Environment, I have no idea.  But Environments are essential.  They provide a way to store runtime parameters for a project, enabling a way to encrypt sensitive values.  This post will focus on automatically creating an environment during deployment.  My recommendations about environments and parameters are:
In this step, we need to:

  1. Create a Tokenized Parameter file
  2. Create an Environment
  3. Attach a reference to the project
  4. add the parameters to the environment with their environment-specific values
  5. Configure the project to use each parameter in the environment
I will repeat some of the code from the previous post so that this can run on its own.  If you were to consolidate these steps, the code for loading the IntegrationServices assembly and creating objects would only be stated once.

Tokenizing a Parameter File

For an environment, we need to know all the parameter names, datatypes, values and whether they are sensitive or not.  In an SSIS project, there is a file called project.params, which stores the project level parameters and is used for loading default values during debugging.  To allow the ability to debug in the project, we cannot directly change the project.params file.  Instead, we will make a copy of it and rename it <Project Name>.params.deploy.  We will add to the build process to look for *.deploy as well as *.ispac files.

The contents of the file look like this:
<?xml version="1.0"?>
<SSIS:Parameters xmlns:SSIS="">


Looking at the contents, we have everything we need, with the exception of datatype which will need to be translated from the its integer code.

The Tokenizing bit is now very simple.  In my case, I am using OctopusDeploy, so I need to add a variable to Octopus with the respective values for each environment and then put a token that Octopus understands, with the same name into the file.  i.e.:


When I setup a deployment step for this, I will have octopus copy the deploy file to the target server and then substitute variables in the file.  If I were deploying to my laptop, the file would show localhost for the value property.

Here's the code, I'll walk through it below:


#create a hashtable to translate datatypes from the params file
$dataTypes = @{ 3="Boolean";
function createEnvironment($folder, [string]$EnvName)
        if ($folder.Environments[$EnvName] -eq $null)
            $e = new-object "$ISNamespace.EnvironmentInfo" ($folder, $EnvName, "")
        Return $folder.Environments[$EnvName]

function addVariablesToEnvironment($env, $name, $type, $value, $sensitive, $desc)
        $v = $env.Variables[$name]
        if ($v.Type -ne $type -or $v.Value -ne $value -or $v.Description -ne $desc)
                if ($v -ne $null)
                $env.Variables.Add($name, $type, $value, $sensitive, $desc)


function getType([int]$id)
        return $dataTypes[$id]

function addEnvironmentToProject($project, $environmentName)

        if ($project.References.Name -eq $null)

function addReferenceToParameter($project, $parameterName)


#Load the integration services .net assembly and create a folder object
#in this case, we are deploying to a folder called 'ETL'
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$sqlConnectionString = "Data Source=" + $SSIS_server + ";Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$integrationServices = New-Object "$ISNamespace.IntegrationServices" $sqlConnection
$catalog = $integrationServices.Catalogs["SSISDB"]
$folder = $catalog.Folders["ETL"]

#loop through parameter files
foreach ($ParameterFile in Get-ChildItem "$($AppRootFolder):\SSIS_Packages\*" -include *.params.deploy)

        #foreach parameter file, create an environment based off the first part of the file name
        $Project = $folder.Projects[$ParameterFile.Name.Replace(".params.deploy","") ]
        $env = createEnvironment $folder $Project.Name
        #add a refernce to the project
        addEnvironmentToProject $Project $env.Name

        #xml parsing, yay!!!
        [xml]$EnvironmentValues = get-content $ParameterFile
        $namespace = @{SSIS=""}
        $parameters = $EnvironmentValues | select-xml -Namespace $namespace -XPath "//SSIS:Parameter"

        foreach($parameter in $parameters)
            #foreach parameter, get its values from the xml and add it to the environment
            $name = $parameter.Node.Name
            $type = getType ($parameter.Node.Properties.Property | ? {$_.Name -eq "DataType"}).'#text'
            $value = ($parameter.Node.Properties.Property | ? {$_.Name -eq "Value"}).'#text'
            [bool]$sensitive = [int]($parameter.Node.Properties.Property | ? {$_.Name -eq "Sensitive"}).'#text'
            $desc = ($parameter.Node.Properties.Property | ? {$_.Name -eq "Description"}).'#text'
            addVariablesToEnvironment $env $name $type $value $sensitive $desc
            #while we're at it, add configure the parameter in the project
            addReferenceToParameter $Project $name
#clean up your sensitive stuff
Get-ChildItem "$($AppRootFolder):\SSIS_Packages\*" -include *.params.deploy | Remove-item

The key things to understand in this powershell script are:

Well that was quite a bit, wasn't it?  The beauty of it is, if you author your build and deploy process correctly, they search for file patterns to drive the process.  Adding new projects and configuring them becomes a snap.  Deploying and configuring environments on all the servers becomes just a matter of adding the variable values in the deployment server.  The savings from voiding manual entry and mistakes is well worth the work.


Labels: , , ,