SQL Server 2016 CTP 2.3 Custom Logging Levels

Case
In CTP 2.3 Custom logging levels where added. How does it work?

Solution
You can set it up in the Catalog with SQL Server Management Studio.

1) Customized Logging Level
Right click on the SSISDB Catalog and choose Customized Logging Level. A new window will be opened where you can add and change Customized Logging Levels.

Right click the SSISDB Catalog

2) Create
Click on the create button to add a name and description of your new Customized Logging Level. In this case we will create a logging level that only shows OnError events.

Only OnError

3) Edit
A new logging level has been created. You can click it to view and change the properties. Our first Customized Logging Level has ID 1.

Edit Customized Logging Level

4) Statistics
Go to the second tab to change the Statistics. In CTP 2.3 the checboxes on the individual rows seems to be missing. So I added them manually to the screenshot, but they do work when you use your Arrow keys to select the cell where they supose to be and then press [Space] to (un)check the individual item.


Select Statistics options

5) Events
Now go to the third tab to select the events you want to log. Same problem with the missing checkboxes here, but the same workaround works. In this case I only checked OnError events. When you're ready click on the Save button to save all changes. 

Select the Events

6) Execute
Now right click on your package to execute it and go to the Advanced tab to select the Logging Level. In the drop down list select the bottom item <Select customized logging level...>.

Select customized logging level...

7) Select customized logging level
A new popup window appears when you can select your newly created Cusomized Loging Level. Again there seems to be missing a checkbox here. By default the row seems to be already selected, so you don't have to selected it. If you do try to select it, you will uncheck it and you will get a warning:
No customized logging level is specified. Please select a customized logging level value before performing this operation.

8) View details customized logging level
When you click on the button with the three dots on the right side of the popup window, you will be able to see the details. Here the checkboxes are visible!

View details of your customized logging level

9) Execution results
Now it's time to see the result. As you can see: a customized logging level was selected and only errors are shown.

10) Feature request
Besides the hidden checkboxes I also would like to make my Customized Logging Level the default for the catalog. At the moment this seems not to be possible.

Default logging level

Update: Nice youtube video with Matt Masson showing the custom logging levels

Deploying environments and variables with PowerShell (C: csv)

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.

Deploying environments and variables with PowerShell (B: table)

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.

Deploying environments and variables with PowerShell (A: array)

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.

Renaming project during deployment Ispac file

Case
I have an SSIS project in Visual Studio which is called "01 StageFinance". The prefix is to preserve the order of projects in the Visual Studio solution.

Solution Explorer inVisual Studio

When I manually deploy the Ispac file to the Catalog, I have the possibility to rename the project from "01 StageFinance" to "StageFinance".


Possible to rename project in Wizard

But when I deploy the Ispac file via Powershell or TSQL then it returns an error when I rename the prject name: Failed to deploy the project. Fix the problems and try again later.:The specified project name, StageFinance, does not match the project name in the deployment file. Why do I need to provide the project name if I can't change it?

Solution
I used the SQL Server Profiler to see what happens during deployment. Both the Wizard and the PowerShell script call the stored procedure deploy_project

exec [SSISDB].[catalog].[deploy_project]
@folder_name=N'Finance',@project_name=N'StageFinance',@project_stream=0x504B0304

What struc to me was that the Wizard called the stored procedure with the new/changed name and then it didn't fail. And when I tried that with powershell it failed.

$NewProjectName = "StageFinance"
[byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath)
$Folder.DeployProject($NewProjectName, $IspacFile) 

Conclusion: The ispac file was probably modified by the Wizard. Next I unzipped the Ispac file and edited the file @Project.manifest and saw that line 4 contained the project name.

@Project.manifest

I changed that project name to "StageFinance", zipped all the files and renamed the zipfile to .Ispac. Then I deployed the new Ispac file without any errors! Doing that manually each time the project needs to be deployed is annoying.

Automate rename with PowerShell
Recently I posted a PowerShell deployment script for automatic deployment. I added a sectio to that script to do the project rename:

# Partial Script
############################
########## RENAME ##########
############################
# If the filename and projectname are different
# Then we need to rename the internal projectname
# before deploying it.

# Derive projectname from ISPAC filename
$CurrentProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)

# Check if rename is necessary
If (-not($CurrentProjectName -eq $ProjectName))
{
    # Split filepath of ispac file in folder and file
    $TmpUnzipPath = split-path $IspacFilePath -Parent
    # Determine the filepath of the new ispac file
    $NewIspacFilePath = $TmpUnzipPath + "\" + $ProjectName + ".ispac"
    # Determine the path of the unzip folder
    $TmpUnzipPath = $TmpUnzipPath + "\" + $CurrentProjectName
    
    # Catch unexpected errors and stop script
    Try
    {
        # Check if new ispac already exists
        if (Test-Path $NewIspacFilePath)
        {
            [System.IO.File]::Delete($NewIspacFilePath)
        }

        # Search strings
        $SearchStringStart = ''
        $SearchStringEnd = ''
 
        # Add reference to compression namespace
        Add-Type -assembly "system.io.compression.filesystem"

        # Extract ispac file to temporary location (.NET Framework 4.5) 
        [io.compression.zipfile]::ExtractToDirectory($IspacFilePath, $TmpUnzipPath)

        # Replace internal projectname with new projectname
        $EditFile = $TmpUnzipPath + "\@Project.manifest"
        (get-content $EditFile).replace($SearchStringStart + $CurrentProjectName + $SearchStringEnd, $SearchStringStart + $ProjectName + $SearchStringEnd) | set-content $EditFile

        # Zip temporary location to new ispac file (.NET Framework 4.5) 
        [io.compression.zipfile]::CreateFromDirectory($TmpUnzipPath, $NewIspacFilePath)

        # Delete temporary location
        [System.IO.Directory]::Delete($TmpUnzipPath, $True)

        # Replace ispac parameter
        $IspacFilePath = $NewIspacFilePath
    }
    Catch [System.Exception]
    {
        Throw  [System.Exception] "Failed renaming project in $IspacFileName : $_.Exception.Message "
    }
}

The complete script can be downloaden here.



Thanks to Bill Fellows for pointing me in the right direction.