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?