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:
- Store values in (multi-)array
- Store values in database table
- Store values in CSV file
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:
#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.