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:

Push parameters up to the project level as much as possible. Package level parameters are in the package xml, but that's a bit of a pain to go hunting for versus the project.params file.
Always use environments to configure parameters. Configuring parameters manually at the project or package level is counter to automation and, in the case of packages, seems to hide things in unexpected places
Put environments in the same respective folder as the project. Any different is confusing as to where a project is being configured from
Put all the projects in one folder. I mean unless there are a million projects. And if there are a million projects, consider how these might be consolidated into smaller groups.
Name the environment the same as the project

In this step, we need to:

Create a Tokenized Parameter file
Create an Environment
Attach a reference to the project
add the parameters to the environment with their environment-specific values
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="www.microsoft.com/SqlServer/SSIS">

<SSIS:Parameter

SSIS:Name="MySourceServer">

<SSIS:Properties>

<SSIS:Property

SSIS:Name="ID">{a38046e0-a627-4d09-8aa7-bf290e6adae6}</SSIS:Property>

<SSIS:Property

SSIS:Name="CreationName"></SSIS:Property>

<SSIS:Property

SSIS:Name="Description"></SSIS:Property>

<SSIS:Property

SSIS:Name="IncludeInDebugDump">0</SSIS:Property>

<SSIS:Property

SSIS:Name="Required">1</SSIS:Property>

<SSIS:Property

SSIS:Name="Sensitive">0</SSIS:Property>

<SSIS:Property

SSIS:Name="Value">localhost</SSIS:Property>

<SSIS:Property

SSIS:Name="DataType">18</SSIS:Property>

</SSIS:Properties>

</SSIS:Parameter>

</SSIS:Parameters>

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.:

SSIS:Name="Value">#{MyServer}</SSIS:Property>

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:

<#==========================================

Functions

===========================================#>

#create a hashtable to translate datatypes from the params file

$dataTypes = @{ 3="Boolean";

6="Byte";

16="Datetime";

15="Decimal";

14="Double";

7="Int16";

9="Int32";

11="Int64";

5="SByte";

13="Single";

18="String";

10="UInt32";

12="UInt64"

}

function createEnvironment($folder, [string]$EnvName)

{

if ($folder.Environments[$EnvName] -eq $null)

{

$e = new-object "$ISNamespace.EnvironmentInfo" ($folder, $EnvName, "")

$e.Create()

}

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.Remove($name)

$env.Alter()

}

$env.Variables.Add($name, $type, $value, $sensitive, $desc)

$env.Alter()

}

function getType([int]$id)

{

return $dataTypes[$id]

}

function addEnvironmentToProject($project, $environmentName)

{

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

{

$project.References.Add($environmentName)

$project.Alter()

}

function addReferenceToParameter($project, $parameterName)

{

$project.Parameters[$parameterName].Set("Referenced",$parameterName)

$project.Alter()

}

<#==========================================

Main

===========================================#>

#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="www.microsoft.com/SqlServer/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:

Functions are declared at the topic because powershell likes it that way
We create a hashtable to look up the integer datatype and translate it to a string
We use the params.deploy files as a driver. We take the name from the file (which should match the project name) and create the environment.
We immediately add a reference to the empty environment in the project
Then we load the deploy file contents into an xml object and start looping through all the parameters, grabbing the necessary values as we go.
Once the parameter is created in the environment, we also configure it on the project
Finally, we delete the deploy files altogether. This is important because they could contain sensitive values stored in plain text. i.e. passwords that were tokenized will get clear text values in the variable replacement process of OctopusDeploy

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.

m

Labels: continuous deployment, Continuous Integration, Powershell, SSIS