Showing posts with label ENVIRONMENTS. Show all posts
Showing posts with label ENVIRONMENTS. Show all posts
Case
I want to import and export Environments to/from my SSIS Catalog. Doing it manually in SSMS takes ages. How can you do that more quickly?
I want to export this environment



















Solution
I will ago I created a couple of PowerShell scripts to deploy environments to your SSIS Catalog with a CSV file, database table or an array as source. The script below is a follow up that allows you to export one or more environments as json files, but also has an import method to deploy those exported environments to a Catalog: Get-CatalogEnvironment and Set-CatalogEnvironment.

This is an example of how you execute the two methods. It first starts with importing a separate script file with various methods and then you can either execute the Get or the Set method:
# PowerShell code
# If you have trouble executing a PowerShell due an Execution Policy then run
# the following script. Note that you need to run PowerShell as administrator
# More information: https://technet.microsoft.com/nl-nl/library/ee176961.aspx
# Set-ExecutionPolicy Unrestricted

# Include functions from a secondairy file
. "$PSScriptRoot\Ssisfunctions.ps1"

# Download example
Get-CatalogEnvironment -SsisServer "mySqlServer\myInstance" -ExportPath "c:\backup\" -FolderName MyEnvFolder -EnvironmentName MyEnvName -Verbose

# Upload example
Set-CatalogEnvironment -SsisServer "mySqlServer\myInstance" -FolderName MyEnvFolder -EnvironmentName MyEnvName -ImportFilePath "C:\temp\employees.json" -DeleteExistingEnvironment $true -Verbose
Example of execution














The environment json files look like this:
[
{
"Name":"FolderStageFiles",
"Description":"Location of stage files",
"Type":"String",
"Sensitive":false,
"Value":"d:\\sources\\"
},
{
"Name":"FtpPassword",
"Description":"Secret FTP password",
"Type":"String",
"Sensitive":true,
"Value":$3cr3t
},
{
"Name":"MIS_STG_Connectionstring",
"Description":"Connectionstring to stage database",
"Type":"String",
"Sensitive":false,
"Value":"Data Source=.\\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
},
{
"Name":"NumberOfRetries",
"Description":"Number of retries for Webservice Task",
"Type":"Int16",
"Sensitive":false,
"Value":3
}
]

You can also get detailed help information and instructions with the standard PowerShell method. Get-Help. It allows you to see examples or the see which parameter is mandatory or optional.
# PowerShell code
# Getting help about the commands
Get-Help Set-CatalogEnvironment -detailed
Get-Help Get-CatalogEnvironment -example

And this is the content of the Ssisfunctions.ps1 file containing the various methods. Take a look and let me know if you have any improvements
# PowerShell code: Ssisfunctions.ps1 (v0.1)
<#
.Synopsis
Download one or more environments from an SSIS Catalog as JSON files

.DESCRIPTION
This functions allows you to download an Environment from the SSIS Catalog. By leaving out the foldername or environmentname you can also download
multiple files. All files are downloaded as JSON files in the format [FolderName].[EnvironmentName].json
Example file of export:

[
{
"Name":"FolderStageFiles",
"Description":"Location of stage files",
"Type":"String",
"Sensitive":false,
"Value":"d:\\sources\\"
},
{
"Name":"FtpPassword",
"Description":"Secret FTP password",
"Type":"String",
"Sensitive":true,
"Value":null
},
{
"Name":"MIS_STG_Connectionstring",
"Description":"Connectionstring to stage database",
"Type":"String",
"Sensitive":false,
"Value":"Data Source=.\\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
},
{
"Name":"NumberOfRetries",
"Description":"Number of retries for Webservice Task",
"Type":"Int16",
"Sensitive":false,
"Value":3
}
]

.PARAMETER SsisServer
Mandatory: The name of the SQL Server instance that runs the SSIS Catalog

.PARAMETER FolderName
Optional: The name of the Catalog folder that contains the Environment

.PARAMETER EnvironmentName
Optional: The name of the Environment

.PARAMETER ExportPath
Optional: The fully qualified path where the json files will be saved. Default value: c:\temp\

.PARAMETER Verbose
Optional: Get more logging information on the screen

.EXAMPLE
Get-CatalogEnvironment -SsisServer "myServer\myInstance" -ExportPath "c:\backup\" -FolderName myCatalogFolder -EnvironmentName myEnvironmentName

.EXAMPLE
Get-CatalogEnvironment -SsisServer "myServer\myInstance" -ExportPath "c:\backup\" -Verbose

.NOTES
You cannot get the value of sensitive variables.The value will be NULL in the export file.
Current scripts works for SSIS 2016. Change version number in code to use an other version of SSIS.

.LINK
https://server.hoit.asia/
#>
Function Get-CatalogEnvironment
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true, Position=0)]
[ValidateLength(1,50)] # String must be between 1 and 50 chars long
[string]$SsisServer,

[Parameter(Mandatory=$false, Position=1)]
[ValidateLength(1,128)] # String must be between 1 and 128 chars long
[string]$FolderName,

[Parameter(Mandatory=$false, Position=2)]
[ValidateLength(1,128)] # String must be between 1 and 128 chars long
[string]$EnvironmentName,

[Parameter(Mandatory=$false, Position=3)]
[string]$ExportPath = "C:\temp\"
)

# Don't continue after error
$ErrorActionPreference = "Stop"

#################################################
############## SHOW ALL PARAMETERS ##############
#################################################
Write-Verbose "========================================================="
Write-Verbose "==     Used parameters - Get-CatalogEnvironment       =="
Write-Verbose "========================================================="
Write-Verbose "SSISServer              : $($SsisServer)"
Write-Verbose "FolderName         : $($FolderName)"
Write-Verbose "EnvironmentName : $($EnvironmentName)"
Write-Verbose "ExportPath      : $($ExportPath)"
Write-Verbose "========================================================="

 
#################################################
############### ADD SSIS ASSEMBLY ###############
#################################################
# Change assembly version number to use an other SSIS version
# 13.0.0.0 = SSIS 2016
# 12.0.0.0 = SSIS 2014
# 11.0.0.0 = SSIS 2012
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
Add-Type -AssemblyName "$($SsisNamespace), Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"


#################################################
############ CONNECT TO SSIS SERVER #############
#################################################
# First create a connection to SQL Server
$SqlConnectionstring = "Data Source=$($SsisServer);Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Then use that SQL connection to create an
# Integration Services object.
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if connection succeeded
If (!$IntegrationServices)
{
Throw [System.Exception] "Failed to connect to server $($SsisServer)"
}
Else
{
Write-Verbose "Connected to server $($SsisServer)"
}


#################################################
########### CONNECT TO SSIS CATALOG #############
#################################################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
If (!$Catalog)
{
# Catalog does not exist.
Throw [System.Exception] "SSISDB catalog does not exist"
}
Else
{
Write-Verbose "SSISDB catalog found"
}


#################################################
############## CHECK EXPORT FOLDER ##############
#################################################
# Check if folder exists
If (-Not (Test-Path $ExportPath))
{
# Create new folder
New-Item -ItemType directory -Path $ExportPath | Out-Null
Write-Host "Folder created: " $ExportPath
}
Else
{
Write-Verbose "Folder $($ExportPath) found"
}


#################################################
############# LOOP THROUGH FOLDERS ##############
#################################################
# Loop though all folder or filter on a folder name
Foreach ($Folder in $Catalog.Folders | WHERE {$_.Name -eq $FolderName -or (!$FolderName)})
{
# Loop though all environments or filter on a environment name
Foreach ($Environment in $Folder.Environments | WHERE {$_.Name -eq $EnvironmentName -or (!$EnvironmentName)})
{
Write-Host "Exporting $($ExportPath)$($Folder.Name).$($Environment.Name).json"
$Environment.Variables | Select-Object -Property Name,Description,@{Name='Type';Expression={"$($_.Type)"}},Sensitive,Value | ConvertTo-Json -Compress | Out-File "$($ExportPath)$($Environment.Parent.Name).$($Environment.Name).json"

# Show warnings if the environment contains sensitive variables
$Environment.Variables | Select-Object -Property Name,Sensitive | Where {$_.Sensitive -eq $True} | ForEach-Object {
Write-Warning "Variable $($_.Name) is sensitive. Cannot retrieve its value"
}
}
}

}

<#
.Synopsis
Upload a json environment file to an SSIS Catalog

.DESCRIPTION
This functions allows you to upload an Environment to the SSIS Catalog. It can update (no deletes) or replace an existing environment.
Example file which can be imported:

[
{
"Name":"FolderStageFiles",
"Description":"Location of stage files",
"Type":"String",
"Sensitive":false,
"Value":"d:\\sources\\"
},
{
"Name":"FtpPassword",
"Description":"Secret FTP password",
"Type":"String",
"Sensitive":true,
"Value":$3cr3t
},
{
"Name":"MIS_STG_Connectionstring",
"Description":"Connectionstring to stage database",
"Type":"String",
"Sensitive":false,
"Value":"Data Source=.\\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
},
{
"Name":"NumberOfRetries",
"Description":"Number of retries for Webservice Task",
"Type":"Int16",
"Sensitive":false,
"Value":3
}
]

.PARAMETER SsisServer
Mandatory: The name of the SQL Server instance that runs the SSIS Catalog

.PARAMETER FolderName
Mandatory: The name of the Catalog folder where the Evironment will be stored

.PARAMETER EnvironmentName
Mandatory: The name of the Environment

.PARAMETER ImportFilePath
Mandatory: The fully qualified path of the json file that needs to be imported

.PARAMETER DeleteExistingEnvironment
Optional: Setting to $true first deletes an existing environment. Default value: $false

.PARAMETER Verbose
Optional: Get more logging information on the screen

.EXAMPLE
Set-CatalogEnvironment -SsisServer "MYSERVER\myInstance" -FolderName MyEnvFolder -EnvironmentName MyEnvName -ImportFilePath "C:\backup\Environments.Generic.json" -DeleteExistingEnvironment $true

.EXAMPLE
Set-CatalogEnvironment -SsisServer "MYSERVER\myInstance" -FolderName MyEnvFolder -EnvironmentName MyEnvName -ImportFilePath "C:\backup\Environments.Generic.json" -Verbose

.NOTES
You cannot insert null values. The will be skipped with a warning
Current scripts works for SSIS 2016. Change version number in code
to use an other version of SSIS.

.LINK
https://server.hoit.asia/
#>
Function Set-CatalogEnvironment
{
[CmdletBinding()]
Param
(
[Parameter(Mandatory=$true, Position=0)]
[ValidateLength(1,50)] # String must be between 1 and 50 chars long
[string]$SsisServer,

[Parameter(Mandatory=$true, Position=1)]
[ValidateLength(1,128)] # String must be between 1 and 128 chars long
[string]$FolderName,

[Parameter(Mandatory=$true, Position=2)]
[ValidateLength(1,128)] # String must be between 1 and 128 chars long
[string]$EnvironmentName,

[Parameter(Mandatory=$true, Position=3)]
[ValidateScript({Test-Path -Path $_ -PathType Leaf})] # File must exist
[ValidatePattern(‘.json$’)] # Extension must be .json
[string]$ImportFilePath,

[Parameter(Mandatory=$false, Position=4)]
[bool]$DeleteExistingEnvironment = $false
)

# Don't continue after error
$ErrorActionPreference = "Stop"

#################################################
############## SHOW ALL PARAMETERS ##############
#################################################
Write-Verbose "========================================================="
Write-Verbose "==     Used parameters - Set-CatalogEnvironment       =="
Write-Verbose "========================================================="
Write-Verbose "SSISServer              : $($SsisServer)"
Write-Verbose "FolderName         : $($FolderName)"
Write-Verbose "EnvironmentName : $($EnvironmentName)"
Write-Verbose "ImportFilePath      : $($ImportFilePath)"
Write-Verbose "DeleteExistingEnvironment : $($DeleteExistingEnvironment)"
Write-Verbose "========================================================="


#################################################
############### ADD SSIS ASSEMBLY ###############
#################################################
# Change assembly version number to use an other SSIS version
# 13.0.0.0 = SSIS 2016
# 12.0.0.0 = SSIS 2014
# 11.0.0.0 = SSIS 2012
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
Add-Type -AssemblyName "$($SsisNamespace), Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"


#################################################
############ CONNECT TO SSIS SERVER #############
#################################################
# First create a connection to SQL Server
$SqlConnectionstring = "Data Source=$($SsisServer);Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Then use that SQL connection to create an
# Integration Services object.
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if connection succeeded
If (!$IntegrationServices)
{
Throw [System.Exception] "Failed to connect to server $($SsisServer)"
}
Else
{
Write-Verbose "Connected to server $($SsisServer)"
}


#################################################
########### CONNECT TO SSIS CATALOG #############
#################################################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
If (!$Catalog)
{
# Catalog does not exist. Different name used?
Throw [System.Exception] "SSISDB catalog does not exist"
}
Else
{
Write-Verbose "SSISDB catalog found"
}


#################################################
################## CHECK FOLDER #################
#################################################
# Create object to the (new) folder
$Folder = $Catalog.Folders[$FolderName]
 
# Check if folder exists
If (!$Folder)
{
     # Folder doesn't exists, so create the new folder.
     Write-Host "Creating new folder $($FolderName)"
     $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $FolderName, $FolderName)
     $Folder.Create()
}
Else
{
     Write-Verbose "Folder $($FolderName) found"
}


#################################################
################## ENVIRONMENT ##################
#################################################
# Create object for the (new) environment
$Environment = $Folder.Environments[$EnvironmentName]

# Check if folder already exists
If (-not $Environment)
{
Write-Host "Creating new environment $($EnvironmentName) in $($FolderName)"

$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
$Environment.Create()
}
ElseIf($DeleteExistingEnvironment -and $Environment)
{
Write-Verbose "Environment $($EnvironmentName) found with $($Environment.Variables.Count) existing variables"
Write-Host "Dropping and recreating environment $($EnvironmentName) in $($FolderName)"
$Environment.Drop()
$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
$Environment.Create()
}
Else
{
Write-Verbose "Environment $($EnvironmentName) found with $($Environment.Variables.Count) existing variables"
}


#################################################
############### GET FILE CONTENT ################
#################################################
Write-Verbose "Reading $($ImportFilePath)"
$EnvironmentInput = Get-Content -Raw -Path $ImportFilePath | ConvertFrom-Json


#################################################
################### VARIABLES ###################
#################################################
# Keep track of number of updates and inserts
$InsertCount = 0
$UpdateCount = 0

# Loop through file content
$EnvironmentInput | Select-Object -Property Name,Description,Type,Sensitive,Value | ForEach-Object {

# Get variablename from json and try to find it in the environment
$Variable = $Environment.Variables[$_.Name]

# Make sure each variable has a value
If ($_.Value.ToString().Length -eq 0)
{
Write-Warning "Variable $($_.Name) skipped because it has no value"
}
else
{
# Check if the variable exists
If (-not $Variable)
{
# Insert new variable
Write-Verbose "Variable $($_.Name) added"
$Environment.Variables.Add($_.Name, $_.Type, $_.Value, $_.Sensitive, $_.Description)

$InsertCount = $InsertCount + 1
}
else
{
# Update existing variable
Write-Verbose "Variable $($_.Name) updated"
$Variable.Type = $_.Type
$Variable.Value = $_.Value
$Variable.Description = $_.Description
$Variable.Sensitive = $_.Sensitive

$UpdateCount = $UpdateCount + 1
}
}
}
$Environment.Alter()

Write-Host "Finished, total inserts $($InsertCount) and total updates $($UpdateCount)"
}

Later on I will add various extra methods for example to test the existence of an environment, to delete an environment, to move an environment, to copy an environment, to rename an environment or to connect an environment to a project. Please let me know if you have any suggestions for extra functionality or improvements!
Case
I want to deploy an environment with variables to the Integration Services Catalog. How do I do that?
Environment with variables














Solution
You can't create them outside SQL Server Management Studio and then deploy them to the catalog. The environment isn't part of the ISPAC file. But it's possible to generate some TSQL code and then execute those Store Procedure calls on all environments of the DTAP.
Or you can use PowerShell script to generate an environment with variables. This blog post shows three different options to use PowerShell to create environments:
  1. Store values in (multi-)array
  2. Store values in database table
  3. Store values in CSV file
C: Store values in CSV file
This example works with two powershell files. The first only contains four parameters. The second file contains the real logic and is called by the first. A benefit of two separate files is that you can use the scripts for multiple environments, but all logic is stored in a single file. In this example the variables are stored in a CSV file instead of an array or database table. You can of course still store the values in a central database and then export the records to a CSV file for the PowerShell script.

A list of all parameters:


  • SsisServer contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.
  • EnvironmentName contains the name of the environment. If the environment already exists then it just adds or updates variables. There are deliberately no deletes. but that's possible.
  • EnvironmentFolderName contains the foldername where the environment is located. If the folder doesn't exists, it will be created.
  • FilepathCsv contains the path of the CSV file. This example uses $PSScriptRoot to get the location of the current PowerShell file and then concatenates the filename of the CSV to it. You can also use a regular path like d:\folder\EnvironmentVariables.csv.


  • This example uses strings, Int16 and Boolean, but there are other datatypes like DataTime, Int32, Int64, etc.). The script contains a lot of feedback. If it's to excessive for you, you can skip a couple of Write-Host lines by adding a hashtag in front of it. For deploying it's not necessary to change anything in the second file.


    #PowerShell: ParamsForGeneralEnvironmentDeploymentWithCsv.ps1
    #################################################################################################
    # Change source and destination properties
    #################################################################################################
    # Ssis
    $SsisServer ="."
    $EnvironmentFolderName = "Environments"
    $EnvironmentName = "Generic"

    # Path of CSV containing variables (you can also use format d:\file.csv)
    $FilepathCsv = "$PSScriptRoot\EnvironmentVariables.csv"

    # Execute deployment script
    . "$PSScriptRoot\GeneralEnvironmentDeploymentWithCsv.ps1" $SsisServer $EnvironmentFolderName $EnvironmentName $FilepathCsv



    Second file:


    #PowerShell: GeneralEnvironmentDeploymentWithCsv.ps1
    ################################
    ########## PARAMETERS ##########
    ################################
    [CmdletBinding()]
    Param(
    # SsisServer is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$SsisServer,

    # EnvironmentFolderName is required
    [Parameter(Mandatory=$True,Position=2)]
    [string]$EnvironmentFolderName,

    # EnvironmentName is required
    [Parameter(Mandatory=$True,Position=3)]
    [string]$EnvironmentName,

    # FilepathCsv is required
    [Parameter(Mandatory=$True,Position=4)]
    [string]$FilepathCsv
    )

    clear
    Write-Host "========================================================================================================================================================"
    Write-Host "== Used parameters =="
    Write-Host "========================================================================================================================================================"
    Write-Host "SSIS Server :" $SsisServer
    Write-Host "Environment Name :" $EnvironmentName
    Write-Host "Environment Folder Path :" $EnvironmentFolderName
    Write-Host "Filepath of CSV file :" $FilepathCsv
    Write-Host "========================================================================================================================================================"


    #########################
    ########## CSV ##########
    #########################
    # Check if ispac file exists
    if (-Not (Test-Path $FilepathCsv))
    {
    Throw [System.IO.FileNotFoundException] "CSV file $FilepathCsv doesn't exists!"
    }
    else
    {
    $FileNameCsv = split-path $FilepathCsv -leaf
    Write-Host "CSV file" $FileNameCsv "found"
    }


    ############################
    ########## SERVER ##########
    ############################
    # Load the Integration Services Assembly
    Write-Host "Connecting to SSIS server $SsisServer "
    $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    [System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

    # Create a connection to the server
    $SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

    # Create the Integration Services object
    $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

    # Check if connection succeeded
    if (-not $IntegrationServices)
    {
    Throw [System.Exception] "Failed to connect to SSIS server $SsisServer "
    }
    else
    {
    Write-Host "Connected to SSIS server" $SsisServer
    }


    #############################
    ########## CATALOG ##########
    #############################
    # Create object for SSISDB Catalog
    $Catalog = $IntegrationServices.Catalogs["SSISDB"]

    # Check if the SSISDB Catalog exists
    if (-not $Catalog)
    {
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages or environments.
    Throw [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
    }
    else
    {
    Write-Host "Catalog SSISDB found"
    }


    ############################
    ########## FOLDER ##########
    ############################
    # Create object to the (new) folder
    $Folder = $Catalog.Folders[$EnvironmentFolderName]

    # Check if folder already exists
    if (-not $Folder)
    {
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $EnvironmentFolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $EnvironmentFolderName, $EnvironmentFolderName)
    $Folder.Create()
    }
    else
    {
    Write-Host "Folder" $EnvironmentFolderName "found"
    }


    #################################
    ########## ENVIRONMENT ##########
    #################################
    # Create object for the (new) environment
    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]

    # Check if folder already exists
    if (-not $Environment)
    {
    Write-Host "Creating environment" $EnvironmentName in $EnvironmentFolderName

    $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
    $Environment.Create()
    }
    else
    {
    Write-Host "Environment" $EnvironmentName "found with" $Environment.Variables.Count "existing variables"
    # Optional: Recreate to delete all variables, but be careful:
    # This could be harmful for existing references between vars and pars
    # if a used variable is deleted and not recreated.
    #$Environment.Drop()
    #$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
    #$Environment.Create()
    }


    ###############################
    ########## VARIABLES ##########
    ###############################
    $InsertCount = 0
    $UpdateCount = 0


    Import-CSV $FilepathCsv -Header Datatype,ParameterName,ParameterValue,ParameterDescription,Sensitive -Delimiter ';' | Foreach-Object{
    If (-not($_.Datatype -eq "Datatype"))
    {
    #Write-Host $_.Datatype "|" $_.ParameterName "|" $_.ParameterValue "|" $_.ParameterDescription "|" $_.Sensitive
    # Get variablename from array and try to find it in the environment
    $Variable = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName].Variables[$_.ParameterName]


    # Check if the variable exists
    if (-not $Variable)
    {
    # Insert new variable
    Write-Host "Variable" $_.ParameterName "added"
    $Environment.Variables.Add($_.ParameterName, $_.Datatype, $_.ParameterValue, [System.Convert]::ToBoolean($_.Sensitive), $_.ParameterDescription)

    $InsertCount = $InsertCount + 1
    }
    else
    {
    # Update existing variable
    Write-Host "Variable" $_.ParameterName "updated"
    $Variable.Type = $_.Datatype
    $Variable.Value = $_.ParameterValue
    $Variable.Description = $_.ParameterDescription
    $Variable.Sensitive = [System.Convert]::ToBoolean($_.Sensitive)

    $UpdateCount = $UpdateCount + 1
    }
    }
    }
    $Environment.Alter()


    ###########################
    ########## READY ##########
    ###########################
    # Kill connection to SSIS
    $IntegrationServices = $null
    Write-Host "Finished, total inserts" $InsertCount " and total updates" $UpdateCount


    CSV file:



    CSV: EnvironmentVariables.csv
    Datatype;Parameter Name;Parameter Value;Parameter Description;Sensitive (true or false)
    String;MIS_STG_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to stage database;false
    String;MIS_HST_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_HST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to historical stage database;false
    String;MIS_DWH_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_DWH;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to data warehouse database;false
    String;MIS_MTA_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_MTA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to metadata database;false
    String;MIS_DM_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_DM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to data mart database;false
    String;FtpPassword; 53cr3t!;Secret FTP password;true
    String;FtpUser;SSISJoost;Username for FTP;false
    String;FtpServer;ftp://SSISJoost.nl;FTP Server;false
    String;FolderStageFiles;d:\sources\;Location of stage files;false
    Boolean;EnableRetry; true;Enable retry for Webservice Task;false
    Int16;NumberOfRetries;3;Number of retries for Webservice Task;false
    Int16;SecsPauseBetweenRetry;30;Number of seconds between retry;false


    New environment

    Existing environment





























    Download scripts

    Tip: if you keep the parameter names in your SSIS projects equal to the variable names in the environment, then you could automatically references them during deployment using this PowerShell script.
    Case
    I want to deploy an environment with variables to the Integration Services Catalog. How do I do that?
    Environment with variables














    Solution
    You can't create them outside SQL Server Management Studio and then deploy them to the catalog. The environment isn't part of the ISPAC file. But it's possible to generate some TSQL code and then execute those Store Procedure calls on all environments of the DTAP.
    Or you can use PowerShell script to generate an environment with variables. This blog post shows three different options to use PowerShell to create environments:
    1. Store values in (multi-)array
    2. Store values in database table
    3. Store values in CSV file
    B: Store values in database table
    This second example works with two powershell files. The first only contains five parameters. The second file contains the real logic and is called by the first. A benefit of two separate files is that you can use the scripts for multiple environments, but all logic is stored in a single file. In this example the variables are stored in a table instead of an array. An array can't be used as parameter between two separate files.

    A list of all parameters:


  • SsisServer contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.
  • EnvironmentName contains the name of the environment. If the environment already exists then it just adds or updates variables. There are deliberately no deletes. but that's possible.
  • EnvironmentFolderName contains the foldername where the environment is located. If the folder doesn't exists, it will be created.
  • SqlServer contains the servername (and instance name) of the Sql Server that has a database with the table containing the variables. Note that this server should be reachable from all environments where you want to run this PowerShell script.
  • SqlDatabase contains the database name that has the table containing the variables. 

  • This example uses strings, Int16 and Boolean, but there are other datatypes like DataTime, Int32, Int64, etc.). The script contains a lot of feedback. If it's to excessive for you, you can skip a couple of Write-Host lines by adding a hashtag in front of it. For deploying it's not necessary to change anything in the second file.


    #PowerShell: ParamsForGeneralEnvironmentDeploymentWithTable.ps1
    #################################################################################################
    # Change source and destination properties
    #################################################################################################
    # Ssis
    $SsisServer ="localhost"
    $EnvironmentFolderName = "Environments"
    $EnvironmentName = "Generic"

    # SqlServer (config table)
    $SqlServer ="localhost"
    $SqlDatabase ="ssisjoost"

    # Execute deployment script
    . "$PSScriptRoot\GeneralEnvironmentDeploymentWithTable.ps1" $SsisServer $EnvironmentFolderName $EnvironmentName $SqlServer $SqlDatabase



    Second file:


    #PowerShell: GeneralEnvironmentDeploymentWithTable.ps1
    ################################
    ########## PARAMETERS ##########
    ################################
    [CmdletBinding()]
    Param(
    # SsisServer is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$SsisServer,

    # EnvironmentFolderName is required
    [Parameter(Mandatory=$True,Position=2)]
    [string]$EnvironmentFolderName,

    # EnvironmentName is required
    [Parameter(Mandatory=$True,Position=3)]
    [string]$EnvironmentName,

    # SqlServer is required
    [Parameter(Mandatory=$True,Position=4)]
    [string]$SqlServer,

    # SqlDatabase is required
    [Parameter(Mandatory=$True,Position=5)]
    [string]$SqlDatabase
    )

    clear
    Write-Host "========================================================================================================================================================"
    Write-Host "== Used parameters =="
    Write-Host "========================================================================================================================================================"
    Write-Host "SSIS Server :" $SsisServer
    Write-Host "Environment Name :" $EnvironmentName
    Write-Host "Environment Folder Path :" $EnvironmentFolderName
    Write-Host "Sql Server (for config) :" $SqlServer
    Write-Host "Database (for config) :" $SqlDatabase
    Write-Host "========================================================================================================================================================"


    ###############################
    ########## VARIABLES ##########
    ###############################
    # Get variables from table

    # Load SqlServer.SMO assembly to get data out of SQL Server
    Write-Host "Connecting to SSIS server $SqlServer "
    Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

    # Create SQL Server object using integrated security
    $SqlServerConf = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer

    # Check if connection succeeded
    if (-not $SqlServerConf)
    {
    Throw [System.Exception] "Failed to connect to SQL server $SqlServer "
    }
    else
    {
    Write-Host "Connected to SQL server" $SqlServer
    }

    # Specify which database to use for the query
    $SqlDatabaseConf = $SqlServerConf.Databases.Item($SqlDatabase)
    # Specify query to get parameters out of helper table
    $SqlQueryConf = "SELECT Datatype, ParameterName, ParameterValue, ParameterDescription, Sensitive FROM EnvironmentVariables"
    # Execute query an put the result in a table object
    $TableConf = $SqlDatabaseConf.ExecuteWithResults($SqlQueryConf).Tables[0]

    Write-Host "Found" $TableConf.Rows.Count "variables in config table"


    ############################
    ########## SERVER ##########
    ############################
    # Load the Integration Services Assembly
    Write-Host "Connecting to SSIS server $SsisServer "
    $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    [System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

    # Create a connection to the server
    $SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

    # Create the Integration Services object
    $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

    # Check if connection succeeded
    if (-not $IntegrationServices)
    {
    Throw [System.Exception] "Failed to connect to SSIS server $SsisServer "
    }
    else
    {
    Write-Host "Connected to SSIS server" $SsisServer
    }


    #############################
    ########## CATALOG ##########
    #############################
    # Create object for SSISDB Catalog
    $Catalog = $IntegrationServices.Catalogs["SSISDB"]

    # Check if the SSISDB Catalog exists
    if (-not $Catalog)
    {
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages or environments
    Throw [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
    }
    else
    {
    Write-Host "Catalog SSISDB found"
    }


    ############################
    ########## FOLDER ##########
    ############################
    # Create object to the (new) folder
    $Folder = $Catalog.Folders[$EnvironmentFolderName]

    # Check if folder already exists
    if (-not $Folder)
    {
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $EnvironmentFolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $EnvironmentFolderName, $EnvironmentFolderName)
    $Folder.Create()
    }
    else
    {
    Write-Host "Folder" $EnvironmentFolderName "found"
    }


    #################################
    ########## ENVIRONMENT ##########
    #################################
    # Create object for the (new) environment
    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]

    # Check if folder already exists
    if (-not $Environment)
    {
    Write-Host "Creating environment" $EnvironmentName in $EnvironmentFolderName

    $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
    $Environment.Create()
    }
    else
    {
    Write-Host "Environment" $EnvironmentName "found with" $Environment.Variables.Count "existing variables"
    # Recreate to delete all variables, but be careful:
    # This could be harmful for existing references between vars and pars
    # if a used variable is deleted and not recreated.
    #$Environment.Drop()
    #$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
    #$Environment.Create()
    }


    ###############################
    ########## VARIABLES ##########
    ###############################
    $InsertCount = 0
    $UpdateCount = 0


    foreach ($Row in $TableConf)
    {
    # Get variablename from array and try to find it in the environment
    $Variable = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName].Variables[$Row.Item("ParameterName")]

    # Check if the variable exists
    if (-not $Variable)
    {
    # Insert new variable
    Write-Host "Variable" $Row.Item("ParameterName") "added"
    $Environment.Variables.Add($Row.Item("ParameterName"), $Row.Item("Datatype"), $Row.Item("ParameterValue"), $Row.Item("Sensitive"), $Row.Item("ParameterDescription"))

    $InsertCount = $InsertCount + 1
    }
    else
    {
    # Update existing variable
    Write-Host "Variable" $Row.Item("ParameterName") "updated"
    $Variable.Type = $Row.Item("Datatype")
    $Variable.Value = $Row.Item("ParameterValue")
    $Variable.Description = $Row.Item("ParameterDescription")
    $Variable.Sensitive = $Row.Item("Sensitive")

    $UpdateCount = $UpdateCount + 1
    }
    }
    $Environment.Alter()


    ###########################
    ########## READY ##########
    ###########################
    # Kill connection to SSIS
    $IntegrationServices = $null
    Write-Host "Finished, total inserts" $InsertCount " and total updates" $UpdateCount



    The table create script and some insert statements to fill the table with sample data.


    --TSQL: CreateTableEnvironmentVariables.sql
    USE [ssisjoost]
    GO

    /****** Generate Table [dbo].[EnvironmentVariables] ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[EnvironmentVariables](
    [Datatype] [varchar](50) NULL,
    [ParameterName] [varchar](50) NULL,
    [ParameterValue] [varchar](255) NULL,
    [ParameterDescription] [varchar](255) NULL,
    [Sensitive] [bit] NULL
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO
    -- Insert Test data
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_STG_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to stage database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_HST_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_HST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to historical stage database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_DWH_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_DWH;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to data warehouse database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_MTA_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_MTA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to metadata database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_DM_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_DM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to data mart database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpPassword', N'53cr3t!', N'Secret FTP password', 1)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpUser', N'SSISJoost', N'Username for FTP', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpServer', N'ftp://SSISJoost.nl', N'FTP Server', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FolderStageFiles', N'd:\sources\', N'Location of stage files', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Boolean', N'EnableRetry', N'true', N'Enable retry for Webservice Task', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Int16', N'NumberOfRetries', N'3', N'Number of retries for Webservice Task', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Int16', N'SecsPauseBetweenRetry', N'30', N'Number of seconds between retry', 0)
    GO


    New environment

    Existing environment





























    Download scripts

    Tip: if you keep the parameter names in your SSIS projects equal to the variable names in the environment, then you could automatically references them during deployment using this PowerShell script.
    Case
    I want to deploy an environment with variables to the Integration Services Catalog. How do I do that?
    Environment with variables














    Solution
    You can't create them outside SQL Server Management Studio and then deploy them to the catalog, because the environment isn't part of the ISPAC file. But it's possible to generate some TSQL code and then execute those Store Procedure calls on all environments of the DTAP.
    Or you can use PowerShell script to generate an environment with variables. This blog post shows three different options to use PowerShell to create environments:
    1. Store values in (multi-)array
    2. Store values in database table
    3. Store values in CSV file
    A: Store values in (multi-)array
    This first example works with a single powershell file with 4 adjustable parameters on top. The variable names and values for this example are stored in an array within the script.

    A list of all parameters:


  • SsisServer contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.
  • EnvironmentName contains the name of the environment. If the environment already exists then it just adds or updates variables. There are deliberately no deletes. but that's possible.
  • EnvironmentFolderName contains the foldername where the environment is located. If the folder doesn't exists, it will be created.
  • EnvironmentVars is an array of variables with the datatype, name, value, description and an indication whether it is a sensitive parameters. The example uses String, Int16 and Boolean, but there are other datatypes possible like DataTime, Int32, Int64, etc. You could store these variables in a database table or an excel sheet and then use some string concatenation to generate the PowerShell code lines for this array.


  • The script contains a lot of feedback. If it's to excessive for you, you can skip a couple of Write-Host lines by adding a hashtag in front of it. For deploying it's not necessary to change any lines below the parameters.


    #PowerShell: GeneralEnvironmentDeploymentWithArray.ps1
    ################################
    ########## PARAMETERS ##########
    ################################
    # Change Server, enviroment folder and enviroment name
    $SsisServer = "localhost"
    $EnvironmentFolderName = "Environments"
    $EnvironmentName = "GenericEnv"

    # Change Variables (mind the commas at the end, not on last line)
    # Columns: Datatype, Name, Value, Description, Sensitive
    $EnvironmentVars = @(
    ("String","MIS_STG_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to stage database",$false),
    ("String","MIS_HST_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_HST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to historical stage database",$false),
    ("String","MIS_DWH_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_DWH;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to data warehouse database",$false),
    ("String","MIS_MTA_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_MTA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to metadata database",$false),
    ("String","MIS_DM_Connectionstring","Data Source=.\sql2016;Initial Catalog=MIS_DM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;","Connectionstring to data mart database",$false),
    ("String","FtpPassword","53cr3t!","Secret FTP password",$true),
    ("String","FtpUser","SSISJoost","Username for FTP",$false),
    ("String","FtpServer","ftp://SSISJoost.nl","FTP Server",$false),
    ("String","FolderStageFiles","d:\sources\","Location of stage files",$false),
    ("Boolean","EnableRetry",$true,"Enable retry for Webservice Task",$false),
    ("Int16","NumberOfRetries", 3,"Number of retries for Webservice Task",$false),
    ("Int16","SecsPauseBetweenRetry", 30,"Number of seconds between retry",$false)
    )

    #################################################
    ########## DO NOT EDIT BELOW THIS LINE ##########
    #################################################
    clear
    Write-Host "========================================================================================================================================================"
    Write-Host "== Used parameters =="
    Write-Host "========================================================================================================================================================"
    Write-Host "SSIS Server :" $SsisServer
    Write-Host "Environment Name :" $EnvironmentName
    Write-Host "Environment Folder Path :" $EnvironmentFolderName
    Write-Host "Number of Variables :" $EnvironmentVars.Count
    Write-Host "========================================================================================================================================================"


    ############################
    ########## SERVER ##########
    ############################
    # Load the Integration Services Assembly
    Write-Host "Connecting to server $SsisServer "
    $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    [System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

    # Create a connection to the server
    $SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

    # Create the Integration Services object
    $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

    # Check if connection succeeded
    if (-not $IntegrationServices)
    {
    Throw [System.Exception] "Failed to connect to server $SsisServer "
    }
    else
    {
    Write-Host "Connected to server" $SsisServer
    }


    #############################
    ########## CATALOG ##########
    #############################
    # Create object for SSISDB Catalog
    $Catalog = $IntegrationServices.Catalogs["SSISDB"]

    # Check if the SSISDB Catalog exists
    if (-not $Catalog)
    {
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages or environments.
    Throw [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
    }
    else
    {
    Write-Host "Catalog SSISDB found"
    }


    ############################
    ########## FOLDER ##########
    ############################
    # Create object to the (new) folder
    $Folder = $Catalog.Folders[$EnvironmentFolderName]

    # Check if folder already exists
    if (-not $Folder)
    {
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $EnvironmentFolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $EnvironmentFolderName, $EnvironmentFolderName)
    $Folder.Create()
    }
    else
    {
    Write-Host "Folder" $EnvironmentFolderName "found"
    }


    #################################
    ########## ENVIRONMENT ##########
    #################################
    # Create object for the (new) environment
    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]

    # Check if folder already exists
    if (-not $Environment)
    {
    Write-Host "Creating environment" $EnvironmentName in $EnvironmentFolderName

    $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
    $Environment.Create()
    }
    else
    {
    Write-Host "Environment" $EnvironmentName "found with" $Environment.Variables.Count "existing variables"
    # Optional: Recreate to delete all variables, but be careful:
    # This could be harmful for existing references between vars and pars
    # if a used variable is deleted and not recreated.
    #$Environment.Drop()
    #$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
    #$Environment.Create()
    }


    ###############################
    ########## VARIABLES ##########
    ###############################
    $InsertCount = 0
    $UpdateCount = 0

    # Loop through the (multi-)array EnvironmentVars that contains all variables
    for($i=0
    $i -le $EnvironmentVars.Count-1
    $i++)
    {
    # Get variablename from array and try to find it in the environment
    $Variable = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName].Variables[$EnvironmentVars[$i][1]]

    # Check if the variable exists
    if (-not $Variable)
    {
    # Insert new variable
    Write-Host "Variable" $EnvironmentVars[$i][1] "added"
    $Environment.Variables.Add($EnvironmentVars[$i][1], $EnvironmentVars[$i][0], $EnvironmentVars[$i][2], $EnvironmentVars[$i][4], $EnvironmentVars[$i][3])

    $InsertCount = $InsertCount + 1
    }
    else
    {
    # Update existing variable
    Write-Host "Variable" $EnvironmentVars[$i][1] "updated"
    $Variable.Type = $EnvironmentVars[$i][0]
    $Variable.Value = $EnvironmentVars[$i][2]
    $Variable.Description = $EnvironmentVars[$i][3]
    $Variable.Sensitive = $EnvironmentVars[$i][4]

    $UpdateCount = $UpdateCount + 1
    }
    }
    $Environment.Alter()


    ###########################
    ########## READY ##########
    ###########################
    # Kill connection to SSIS
    $IntegrationServices = $null
    Write-Host "Finished, total inserts" $InsertCount " and total updates" $UpdateCount


    New environment

    Existing environment





























    Download scripts

    Tip: if you keep the parameter names in your SSIS projects equal to the variable names in the environment, then you could automatically references them during deployment using this PowerShell script.
    Case
    I have an Integration Services catalog with multiple projects and one generic environment. This environment contains all possible variables to support all projects.

    Multiple project using the same generic environment














    How do I deploy my ispac files with PowerShell and automatically create a reference to this generic environment. (All parameter in my project are available as variable in the environment, but not all variables in my generic environment are available as parameter in my project.)


    Solution
    You can use the following PowerShell scripts as a base. Feel free to post changes in the comments below. This example uses two scripts. The first one is a simple script where you only store parameter values:

    1. IspacFilePath is mandatory. Is contains the full path of the ispac file.
    2. SsisServer is mandatory. It contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.
    3. FolderName is mandatory. It contains the name of the catalog folder. If it doesn't exist then it will be created
    4. ProjectName is optional. If it is empty the filename from the ispac file will be used as projectname. However, unfortunately it must be equal to the internal projectname of the ispac file. The ispac wizard is able to change the projectname, but that doesn't seem to be possible with PowerShell (Edit: rename solution).
    5. EnvironmentName is optional. If it is empty then no environment will be referenced
    6. EnvironmentFolderName is optional. If it is empty then the script will search the environment in the project folder.
    #PowerShell: finance.ps1
    #################################################################################################
    # Change source, destination and environment properties
    #################################################################################################

    # Source
    $IspacFilePath = "d:\projects\Finance\bin\Development\Finance.ispac"

    # Destination
    $SsisServer =".\sql2016"
    $FolderName = "Finance"
    $ProjectName = ""

    # Environment
    $EnvironmentName = "Generic"
    $EnvironmentFolderName = "Environments"

    #################################################################################################
    # Execute generic deployment script
    . "$PSScriptRoot\generalDeployment.ps1" $IspacFilePath $SsisServer $FolderName $ProjectName $EnvironmentName $EnvironmentFolderName


    The second script is the generic deployment script which is called by the first script. Developers only change the parameters in the first script and pass it through to the server administrator who executes it.

    #PowerShell: generalDeployment.ps1
    ################################
    ########## PARAMETERS ##########
    ################################
    [CmdletBinding()]
    Param(
    # IsPacFilePath is required
    [Parameter(Mandatory=$True,Position=1)]
    [string]$IspacFilePath,

    # SsisServer is required
    [Parameter(Mandatory=$True,Position=2)]
    [string]$SsisServer,

    # FolderName is required
    [Parameter(Mandatory=$True,Position=3)]
    [string]$FolderName,

    # ProjectName is not required
    # If empty filename is used
    [Parameter(Mandatory=$False,Position=4)]
    [string]$ProjectName,

    # EnvironmentName is not required
    # If empty no environment is referenced
    [Parameter(Mandatory=$False,Position=5)]
    [string]$EnvironmentName,

    # EnvironmentFolderName is not required
    # If empty the FolderName param is used
    [Parameter(Mandatory=$False,Position=6)]
    [string]$EnvironmentFolderName
    )

    # Replace empty projectname with filename
    if (-not $ProjectName)
    {
    $ProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)
    }
    # Replace empty Environment folder with project folder
    if (-not $EnvironmentFolderName)
    {
    $EnvironmentFolderName = $FolderName
    }

    clear
    Write-Host "========================================================================================================================================================"
    Write-Host "== Used parameters =="
    Write-Host "========================================================================================================================================================"
    Write-Host "Ispac File Path : " $IspacFilePath
    Write-Host "SSIS Server : " $SsisServer
    Write-Host "Project Folder Path : " $FolderName
    Write-Host "Project Name : " $ProjectName
    Write-Host "Environment Name : " $EnvironmentName
    Write-Host "Environment Folder Path: " $EnvironmentFolderName
    Write-Host "========================================================================================================================================================"
    Write-Host ""

    ###########################
    ########## ISPAC ##########
    ###########################
    # Check if ispac file exists
    if (-Not (Test-Path $IspacFilePath))
    {
    Throw [System.IO.FileNotFoundException] "Ispac file $IspacFilePath doesn't exists!"
    }
    else
    {
    $IspacFileName = split-path $IspacFilePath -leaf
    Write-Host "Ispac file" $IspacFileName "found"
    }


    ############################
    ########## SERVER ##########
    ############################
    # Load the Integration Services Assembly
    Write-Host "Connecting to server $SsisServer "
    $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    [System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;

    # Create a connection to the server
    $SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

    # Create the Integration Services object
    $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

    # Check if connection succeeded
    if (-not $IntegrationServices)
    {
    Throw [System.Exception] "Failed to connect to server $SsisServer "
    }
    else
    {
    Write-Host "Connected to server" $SsisServer
    }


    #############################
    ########## CATALOG ##########
    #############################
    # Create object for SSISDB Catalog
    $Catalog = $IntegrationServices.Catalogs["SSISDB"]

    # Check if the SSISDB Catalog exists
    if (-not $Catalog)
    {
    # Catalog doesn't exists. The user should create it manually.
    # It is possible to create it, but that shouldn't be part of
    # deployment of packages.
    Throw [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
    }
    else
    {
    Write-Host "Catalog SSISDB found"
    }


    ############################
    ########## FOLDER ##########
    ############################
    # Create object to the (new) folder
    $Folder = $Catalog.Folders[$FolderName]

    # Check if folder already exists
    if (-not $Folder)
    {
    # Folder doesn't exists, so create the new folder.
    Write-Host "Creating new folder" $FolderName
    $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $FolderName, $FolderName)
    $Folder.Create()
    }
    else
    {
    Write-Host "Folder" $FolderName "found"
    }


    #############################
    ########## PROJECT ##########
    #############################
    # Deploying project to folder
    if($Folder.Projects.Contains($ProjectName)) {
    Write-Host "Deploying" $ProjectName "to" $FolderName "(REPLACE)"
    }
    else
    {
    Write-Host "Deploying" $ProjectName "to" $FolderName "(NEW)"
    }
    # Reading ispac file as binary
    [byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath)
    $Folder.DeployProject($ProjectName, $IspacFile)
    $Project = $Folder.Projects[$ProjectName]
    if (-not $Project)
    {
    # Something went wrong with the deployment
    # Don't continue with the rest of the script
    return ""
    }


    #################################
    ########## ENVIRONMENT ##########
    #################################
    # Check if environment name is filled
    if (-not $EnvironmentName)
    {
    # Kill connection to SSIS
    $IntegrationServices = $null

    # Stop the deployment script
    Return "Ready deploying $IspacFileName without adding environment references"
    }

    # Create object to the (new) folder
    $EnvironmentFolder = $Catalog.Folders[$EnvironmentFolderName]

    # Check if environment folder exists
    if (-not $EnvironmentFolder)
    {
    Throw [System.Exception] "Environment folder $EnvironmentFolderName doesn't exist"
    }

    # Check if environment exists
    if(-not $EnvironmentFolder.Environments.Contains($EnvironmentName))
    {
    Throw [System.Exception] "Environment $EnvironmentName doesn't exist in $EnvironmentFolderName "
    }
    else
    {
    # Create object for the environment
    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]

    if ($Project.References.Contains($EnvironmentName, $EnvironmentFolderName))
    {
    Write-Host "Reference to" $EnvironmentName "found"
    }
    else
    {
    Write-Host "Adding reference to" $EnvironmentName
    $Project.References.Add($EnvironmentName, $EnvironmentFolderName)
    $Project.Alter()
    }
    }


    ########################################
    ########## PROJECT PARAMETERS ##########
    ########################################
    $ParameterCount = 0
    # Loop through all project parameters
    foreach ($Parameter in $Project.Parameters)
    {
    # Get parameter name and check if it exists in the environment
    $ParameterName = $Parameter.Name
    if ($ParameterName.StartsWith("CM.","CurrentCultureIgnoreCase"))
    {
    # Ignoring connection managers
    }
    elseif ($ParameterName.StartsWith("INTERN_","CurrentCultureIgnoreCase"))
    {
    # Internal parameters are ignored (where name starts with INTERN_)
    Write-Host "Ignoring Project parameter" $ParameterName " (internal use only)"
    }
    elseif ($Environment.Variables.Contains($Parameter.Name))
    {
    $ParameterCount = $ParameterCount + 1
    Write-Host "Project parameter" $ParameterName "connected to environment"
    $Project.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)
    $Project.Alter()
    }
    else
    {
    # Variable with the name of the project parameter is not found in the environment
    # Throw an exeception or remove next line to ignore parameter
    Throw [System.Exception] "Project parameter $ParameterName doesn't exist in environment"
    }
    }
    Write-Host "Number of project parameters mapped:" $ParameterCount


    ########################################
    ########## PACKAGE PARAMETERS ##########
    ########################################
    $ParameterCount = 0
    # Loop through all packages
    foreach ($Package in $Project.Packages)
    {
    # Loop through all package parameters
    foreach ($Parameter in $Package.Parameters)
    {
    # Get parameter name and check if it exists in the environment
    $PackageName = $Package.Name
    $ParameterName = $Parameter.Name
    if ($ParameterName.StartsWith("CM.","CurrentCultureIgnoreCase"))
    {
    # Ignoring connection managers
    }
    elseif ($ParameterName.StartsWith("INTERN_","CurrentCultureIgnoreCase"))
    {
    # Internal parameters are ignored (where name starts with INTERN_)
    Write-Host "Ignoring Package parameter" $ParameterName " (internal use only)"
    }
    elseif ($Environment.Variables.Contains($Parameter.Name))
    {
    $ParameterCount = $ParameterCount + 1
    Write-Host "Package parameter" $ParameterName "from package" $PackageName "connected to environment"
    $Package.Parameters[$Parameter.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, $Parameter.Name)
    $Package.Alter()
    }
    else
    {
    # Variable with the name of the package parameter is not found in the environment
    # Throw an exeception or remove next line to ignore parameter
    Throw [System.Exception] "Package parameter $ParameterName from package $PackageName doesn't exist in environment"
    }
    }
    }
    Write-Host "Number of package parameters mapped:" $ParameterCount


    ###########################
    ########## READY ##########
    ###########################
    # Kill connection to SSIS
    $IntegrationServices = $null


    Return "Ready deploying $IspacFileName "

    The result
    When executing the PowerShell script you get some feedback from the deployment process.
    Executing PowerShell Deployment Script



















    When finished the project will be deployed and all parameters are mapped to variables from the generic environment.
    Parameters mapped














    Case
    Parameters and environments are a handy new feature in SSIS 2012, but creating environments with variables and referencing the environment to the project and the variables to the parameters is a lot of effort. No problem if you have to do it once, but if you deploy your packages through the DTAP servers you have to redo it on every single server. Is there a way to deploy environments?
    
    No export option for Environments















    Solution
    Although it is easy to deploy packages through DTAP servers. Deploying environments is not  possible. That could get a little tiresome especially if you have a lot of parameters in your project.

    I have created a script/stored procedure that uses the project identifier as input, loops through the tables of the SSISDB and prints SQL-statements to execute SSISDB Stored Procedures.
    How to get the project identifier
















    For every environment, variable and reference to this project it will generate creation scripts. You can copy these stored procedure calls, adjust the values where needed and execute them on the next environment.
    Execute script then copy and execute output

























    Release notes
    1. There are no checks or validations in this version. So it doesn't check whether objects already exist before calling the SSISDB Stored Procedures.
    2. First deploy the SSIS project before executing the stored procedure calls on the next server.
    3. Make sure the folder name is equal on the new server or change it in the stored procedure calls.
    4. Make sure to check sensitive variables values (you can't get the value from the tables).


    Here is how you call the stored procedure to generate the scripts. If you don't want to add the new stored procedure then you could just use the script inside the stored procedure.
    exec catalog.deploy_environment 11

    Add the following stored procedure to the SSISDB or use the TSQL code inside.
    USE SSISDB;
    GO

    -- USE AT OWN RISK! This stored procedure was created on the SSISDB on SQL Server version:
    -- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
    -- Dec 28 2012 20:23:12
    -- Copyright (c) Microsoft Corporation
    -- Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1)


    -- Drop any previous versions of this stored procedure
    IF OBJECT_ID ( 'catalog.deploy_environment', 'P' ) IS NOT NULL
    DROP PROCEDURE catalog.deploy_environment;
    GO

    -- project_id is the identifier in the properties of a project
    CREATE PROCEDURE catalog.deploy_environment
    @project_id bigint
    AS

    -- Internal variables used within the cursor
    Declare @environment_name as nvarchar(128);
    Declare @project_name as nvarchar(128);
    Declare @folder_name as nvarchar(128);
    Declare @environment_folder_name as nvarchar(128);
    Declare @reference_type as char(1);
    Declare @folder_id as bigint;
    Declare @environment_description as nvarchar(1024);
    Declare @environment_id as bigint;


    DECLARE ref_environment_cursor CURSOR FOR
    -- Loop through all in the project referenced Environments
    SELECT r.environment_name
    , p.name as project_name
    , ISNULL(r.environment_folder_name, f.name) as folder_name
    , ISNULL(r.environment_folder_name, f.name) as environment_folder_name -- for @reference_type = A
    , r.reference_type as reference_type
    , f.folder_id
    , e.description as environment_description
    , e.environment_id
    FROM [SSISDB].[internal].environment_references as r
    INNER JOIN [SSISDB].[internal].projects as p
    on r.project_id = p.project_id
    INNER JOIN [SSISDB].[internal].folders as f
    on p.folder_id = f.folder_id
    INNER JOIN [SSISDB].[internal].environments as e
    on e.folder_id = f.folder_id
    and e.environment_name = r.environment_name
    WHERE r.project_id = @project_id

    OPEN ref_environment_cursor

    FETCH NEXT FROM ref_environment_cursor
    INTO @environment_name, @project_name, @folder_name, @environment_folder_name, @reference_type, @folder_id, @environment_description, @environment_id;

    Print '-- Create scripts for deploying enviroments'
    Print '-- Project ID: ' + CAST(@project_id as varchar(5)) + ' - Project name: ' + @project_name
    Print ''

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Create environment
    Print '-- Create environment: ' + @environment_name
    Print 'EXEC [SSISDB].[catalog].[create_environment]'
    Print ' @environment_name=N''' + @environment_name + ''''
    Print ', @environment_description=N''' + @environment_description + ''''
    Print ', @folder_name=N''' + @folder_name + ''''
    Print 'GO'
    Print ''

    -- Create reference from environment to project. Relative or Absolute
    Print '-- Reference environment ' + @environment_name + ' to project ' + @project_name
    IF @reference_type = 'R'
    BEGIN
    -- Reference Relative
    Print 'Declare @reference_id bigint'
    Print 'EXEC [SSISDB].[catalog].[create_environment_reference]'
    Print ' @environment_name=N''' + @environment_name + ''''
    Print ', @reference_id=@reference_id OUTPUT'
    Print ', @project_name=N''' + @project_name + ''''
    Print ', @folder_name=N''' + @folder_name + ''''
    Print ', @reference_type=R'
    Print 'GO'
    Print ''
    END
    ELSE
    BEGIN
    -- Reference Absolute
    Print 'Declare @reference_id bigint'
    Print 'EXEC [SSISDB].[catalog].[create_environment_reference]'
    Print ' @environment_name=N''' + @environment_name + ''''
    Print ', @environment_folder_name=N''' + @environment_folder_name + ''''
    Print ', @reference_id=@reference_id OUTPUT'
    Print ', @project_name=N''' + @project_name + ''''
    Print ', @folder_name=N''' + @folder_name + ''''
    Print ', @reference_type=A'
    Print 'GO'
    Print ''
    END


    -- Internal variables used within the cursor
    Declare @environment_value as sql_variant--nvarchar(max); -- SQL_VARIANT
    Declare @variable_name as nvarchar(128);
    Declare @sensitive as bit;
    Declare @variable_description as nvarchar(1024);
    Declare @variable_type as nvarchar(128);

    DECLARE environment_var_cursor CURSOR FOR
    -- Loop through all in the variables of the active environment
    SELECT CAST(ev.value as varchar(255)) as environment_value
    , ev.name as variable_name
    , ev.sensitive
    , ev.description as variable_description
    , ev.type as variable_type
    FROM [SSISDB].[catalog].environment_variables as ev
    WHERE environment_id = @environment_id

    OPEN environment_var_cursor

    FETCH NEXT FROM environment_var_cursor
    INTO @environment_value, @variable_name, @sensitive, @variable_description, @variable_type;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Environments variables
    Print '-- Create variables for environment: ' + @environment_name + ' - ' + @variable_name

    -- Variable declaration depending on the type within the environment
    IF @variable_type = 'Boolean'
    BEGIN
    Print 'DECLARE @var bit = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'Byte'
    BEGIN
    Print 'DECLARE @var tinyint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'DateTime'
    BEGIN
    Print 'DECLARE @var datetime = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'Decimal'
    BEGIN
    Print 'DECLARE @var decimal(38,18) = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'Double'
    BEGIN
    Print 'DECLARE @var float = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'Int16'
    BEGIN
    Print 'DECLARE @var smallint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'Int32'
    BEGIN
    Print 'DECLARE @var int = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'Int64'
    BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'SByte'
    BEGIN
    Print 'DECLARE @var smallint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'Single'
    BEGIN
    Print 'DECLARE @var float = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'String'
    BEGIN
    Print 'DECLARE @var sql_variant = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'UInt32'
    BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END
    ELSE IF @variable_type = 'UInt64'
    BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
    END

    Print 'EXEC [SSISDB].[catalog].[create_environment_variable]'
    Print ' @variable_name=N''' + @variable_name + ''''
    IF @sensitive = 0
    BEGIN
    Print ', @sensitive=False'
    END
    ELSE
    BEGIN
    Print ', @sensitive=True'
    END
    Print ', @description=N''' + @variable_description + ''''
    Print ', @environment_name=N''' + @environment_name + ''''
    Print ', @folder_name=N''' + @folder_name + ''''
    Print ', @value=@var'
    Print ', @data_type=N''' + @variable_type + ''''
    Print 'GO'
    Print ''

    FETCH NEXT FROM environment_var_cursor
    INTO @environment_value, @variable_name, @sensitive, @variable_description, @variable_type;
    END
    CLOSE environment_var_cursor;
    DEALLOCATE environment_var_cursor;
    -- End Environments variables

    -- Parameter - Variable mapping
    Declare @object_type as smallint
    Declare @parameter_name as nvarchar(128);
    Declare @object_name as nvarchar(260);
    Declare @folder_name2 as nvarchar(128);
    Declare @project_name2 as nvarchar(128);
    Declare @value_type as char(1)
    Declare @parameter_value as nvarchar(128);

    DECLARE parameter_var_cursor CURSOR FOR
    -- Loop through variables referenced to a parameter
    SELECT op.object_type
    , parameter_name
    , [object_name]
    , f.name as folder_name
    , p.name as project_name
    , value_type
    , referenced_variable_name as parameter_value
    FROM [SSISDB].[internal].object_parameters as op
    INNER JOIN [SSISDB].[internal].projects as p
    on p.project_id = op.project_id
    INNER JOIN [SSISDB].[internal].folders as f
    on p.folder_id = f.folder_id
    WHERE op.project_id = @project_id
    AND referenced_variable_name is not null

    OPEN parameter_var_cursor

    FETCH NEXT FROM parameter_var_cursor
    INTO @object_type, @parameter_name, @object_name, @folder_name2, @project_name2, @value_type, @parameter_value;

    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- Reference variables
    Print '-- Reference variable ' + @parameter_value + ' to parameter ' + @parameter_name
    Print 'EXEC [SSISDB].[catalog].[set_object_parameter_value]'
    Print ' @object_type=' + CAST(@object_type as varchar(5))
    Print ', @parameter_name=N''' + @parameter_name + ''''
    Print ', @object_name=N''' + @object_name + ''''
    Print ', @folder_name=N''' + @folder_name2 + '''' ----
    Print ', @project_name=N''' + @project_name2 + '''' ---
    Print ', @value_type=' + @value_type
    Print ', @parameter_value=N''' + @parameter_value + ''''
    Print 'GO'
    Print ''

    FETCH NEXT FROM parameter_var_cursor
    INTO @object_type, @parameter_name, @object_name, @folder_name2, @project_name2, @value_type, @parameter_value;
    END
    CLOSE parameter_var_cursor;
    DEALLOCATE parameter_var_cursor;
    -- End Parameter - Variable mapping

    FETCH NEXT FROM ref_environment_cursor
    INTO @environment_name, @project_name, @folder_name, @environment_folder_name, @reference_type, @folder_id, @environment_description, @environment_id;
    END
    CLOSE ref_environment_cursor;
    DEALLOCATE ref_environment_cursor;
    GO
    Download as SQL file

    NOTE: Please use at own risk and let me know it things could be improved!