can't remove SSIS variables using remove method on PowerShell

+6 votes

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?

Mar 27, 2018 in DevOps & Agile by DragonLord999
• 8,380 points

retagged Oct 16, 2018 by Kalgi 78 views

5 answers to this question.

0 votes
Best answer

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

# Delete the variable
$environment.Variables.Remove("Variable2")
$environment.Alter()
answered Mar 27, 2018 by ajs3033
• 7,280 points

selected Oct 16, 2018 by Kalgi
0 votes

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

Remove-Item Env:\ExampleVar
answered Oct 16, 2018 by Kalgi
• 39,310 points
0 votes

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.

answered Oct 16, 2018 by lina
• 8,100 points
0 votes

Have a look at this blog for detailed explanation.

answered Oct 16, 2018 by Hannah
• 14,090 points
0 votes

Use the following syntax:

Remove-Variable 
      [-Name] <String[]>
      [-Include <String[]>]
      [-Exclude <String[]>]
      [-Force]
      [-Scope <String>]
      [-WhatIf]
      [-Confirm]
      [<CommonParameters>]
answered Oct 16, 2018 by Nilesh
• 6,900 points

Related Questions In DevOps & Agile

0 votes
1 answer

Deploying web app on Azure using VSTS

You can find the following guide helpful ...READ MORE

answered Aug 6, 2018 in DevOps & Agile by DareDev
• 6,810 points
48 views
0 votes
1 answer

How to restart docker for windows using Powershell?

Try this: $processes = Get-Process "*docker for windows*" if ...READ MORE

answered Oct 23, 2018 in DevOps & Agile by Tyrion anex
• 8,280 points
173 views
+5 votes
7 answers

Docker swarm vs kubernetes

Swarm is easy handling while kn8 is ...READ MORE

answered Aug 27, 2018 in Docker by Mahesh Ajmeria
981 views
+13 votes
2 answers

Git management technique when there are multiple customers and need multiple customization?

Consider this - In 'extended' Git-Flow, (Git-Multi-Flow, ...READ MORE

answered Mar 26, 2018 in DevOps & Agile by DragonLord999
• 8,380 points
120 views
+2 votes
2 answers

how to set different provisioning profiles for different targets using Xcode Build

For multiple targets, each task has to ...READ MORE

answered Mar 26, 2018 in DevOps & Agile by ajs3033
• 7,280 points

edited Oct 16, 2018 by Kalgi 585 views
+5 votes
3 answers

Steps to Call Python method in BuildBot

To run python code, you must write ...READ MORE

answered Mar 27, 2018 in DevOps & Agile by ajs3033
• 7,280 points

edited Oct 12, 2018 by Kalgi 258 views