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")
$environment = $folder.Environments[$EnvironmentName]
# Creates the Environment
$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
# Add the variable to the environment
$environment.Variables.Add("Variable2", "String", "MyValue", $false, "This is my variable")
# Print the variable to make sure it exists
# Delete the variable
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?