can t remove SSIS variables using remove method on PowerShell

Question

I am working on the implementation of Devops pipelines for Microsoft BI projects with SQL Server 2016, mainly with VSTS and PowerShell. For the SSIS release process, I use the integration services assembly.

I have a problem with the  Remove method from the assembly to work properly. I want developers to define variables in an XML file in SSDT and the Release PowerShell script to delete a variable if it exists (and the value is different in the XML code). It will then be recreated with the new values (there is no alter-method for a single variable that I can see).

Here is a code sample which simplifies what I am trying to do:

$FolderName = "VRTest1"
$EnvironmentName = "Development"

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

# Store the IntegrationServices Assembly namespace
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

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

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

$catalog = $integrationServices.Catalogs["SSISDB"]
$folder = $integrationServices.Catalogs["SSISDB"].Folders[$FolderName]

# Creates the folder
$folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
      $folder.Create()

$environment = $folder.Environments[$EnvironmentName]

# Creates the Environment
$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
    $environment.Create()

# Add the variable to the environment
$environment.Variables.Add("Variable2", "String", "MyValue", $false, "This is my variable")
$environment.Alter()

# Print the variable to make sure it exists
Write-Host $environment.Variables.Name

# Delete the variable
$environment.Variables.Remove("Variable2")

This will create the required objects in the Integration Services Catalog and should create, then delete the variable but it doesn't. The variable is created but not deleted.

Am I calling the remove method in the wrong place or is it something else?

Answer 1

you're missing the additional $environment.Alter() after your removal!

# Delete the variable
$environment.Variables.Remove("Variable2")
$environment.Alter()

Answer 2

The following command is used to remove/delete an environment variable where ExampleVar is just an environment variable

Remove-Item Env:\ExampleVar

Answer 3

I totally agree with @Kalgi's answer, but would like to add further. 

The following can also be achieved using the .Net method

[Environment]::SetEnvironmentVariable("ExampleVar",$null,"User")

When working with .Net Framework, you have access to various variables which you can use to your advantage. The following command invokes the .NET component and sets the environment variable to null, also ensuring that the User-level environment variable is removed.

Answer 4

Have a look at this blog for a detailed explanation.

Answer 5

Use the following syntax:

Remove-Variable 
      [-Name] <String[]>
      [-Include <String[]>]
      [-Exclude <String[]>]
      [-Force]
      [-Scope <String>]
      [-WhatIf]
      [-Confirm]
      [<CommonParameters>]