How to Copy/Clone an SSIS Environment in Powershell

Mark Wojciechowicz

On an SSIS dev server, it may be helpful to copy or clone an environment so that you can quickly apply different settings to an SSIS execution.  I had the recent task of creating multiple environments, which were all but identical except for the target database.  This would allow test teams to independently test on the server and not affect each other's work.

You cannot just right click and copy the environment.  You could hack around with the tables which store the environment info, but you might be out of luck if you do not have admin access.  So the below approach is a modification of this post my Matt Mason which shows how to create an environment in powershell.

Note that there is special handling for sensitive variables.  This is because powershell returns a null value when you read it, but you cannot have a null value in a variable when you create an environment.

#Load SSIS Assembly
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($ISNamespace) | Out-Null;

$sqlConnStr = "Data Source=localhost\SQL_2014;Initial Catalog=Master;Integrated Security=SSPI;"
$conn = New-Object System.Data.SqlClient.SqlConnection $sqlConnStr

#Create SSIS object
$IS = New-Object "$ISNamespace.IntegrationServices" $conn
$catalog = $IS.Catalogs["SSISDB"]

#Get the folder that we are working with
$folder = $catalog.Folders["Test"]

#Get the environment
$sourceEnv = $folder.Environments["Env1"]
$targetEnv = New-Object "$ISNamespace.EnvironmentInfo" ($folder, "Env2", "My New Environment Description")

#copy each variable to the new environment
foreach ($var in $sourceEnv.Variables)
       if ($var.Value.ToString() -eq "")
            { $finalValue = ""}
        else {$finalValue = $var.Value}

       $targetEnv.Variables.Add($var.Name, $var.Type, $finalValue, $var.Sensitive, $var.Description)


Labels: ,