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 90 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
• 41,470 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,110 points
0 votes

Have a look at this blog for detailed explanation.

answered Oct 16, 2018 by Hannah
• 15,120 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,880 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
54 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,270 points
257 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
1,020 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
177 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 665 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 293 views