Powershell error setting Environment variable for

Question

My Powershell script executes an SSIS package, but first over-rides an Environment variable. The Alter method on the EnvironmentInfo object fails with a generic error message: "Operation 'Alter' on object [EnvironmentInfo[@Name='MyVariable']' failed during execution."

I also tried removing the environment variable and changing the Project parameter, but received the same error on the Alter method for the Project object.

I suspect this is either 1) a shortcoming of using the 32-bit version of SQL Server 2012, or 2) a permissions issue.

I've made sure the executing Windows Account has full privileges on the SSISDB database and the SSIS Catalog project, and the child folder, environment, etc.

Any ideas on the error or how I can get more details? I don't see anything in the Windows Event Logs.

Here's my code:

# Variables
$ServerInstance = "MyServer"
$32bitSQLServer = "false" #use #null for 32-bit
$SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$FolderName = "MyFolder"
$ProjectName = "MyProject"
$PackageName = "MyPackage.dtsx"
$EnvironmentName = "MyEnvironment"
$VariableName = "MyVariable"
$VariableValue = Read-Host "What is the new environment variable value? "

# Create a connection to the server - Have to use Windows Authentication in order to Execute the Package
$sqlConnectionString = `
    "Data Source=" + $ServerInstance + ";Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")  
$integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection

# Get the Integration Services catalog
$catalog = $integrationServices.Catalogs["SSISDB"]

# Get the folder
$folder = $catalog.Folders[$FolderName]

# Get the project
$project = $folder.Projects[$ProjectName]

# Get the environment
$environment = $folder.Environments[$EnvironmentName]

# Get the environment reference
$environmentReference = $project.References.Item($EnvironmentName, $FolderName)            
$environmentReference.Refresh() 

# Get the package
$package = $project.Packages[$PackageName]

# Set the Environment Variable
$environment.Variables[$VariableName].Value = $VariableValue
$environment.Alter()

# Execute the package
Write-Host "Running Package " $PackageName "..."
$result = $package.Execute($32bitSQLServer, $environmentReference)

# Alternate approach, also not working
# $project.Parameters[$VariableName].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$VariableValue)
# $project.Alter()
# $result = $package.Execute($32bitSQLServer, $environmentReference)

Write-Host "Done."

Answer 1

Just alter the Parameter on the package and don't worry about the environment variable. I am sure it doesn't change the value stored in the package on the server, just the object held in your$package variable. Something like this:

$package.Parameters[$VariableName].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal, $VariableValue)
$package.Alter()

$execution = $integrationServices.Catalogs['SSISDB'].Executions[$package.Execute($false, ($package.Parent.References[$package_name, $folder_Name]))]

do {
    $execution.Refresh()
} until ($execution.Completed)

Answer 2

So with some digging I found the answer to my error. Turned out I had multiple versions (11.0.0.0, 12.0.0.0, 13.0.0.0) of the assembly Microsoft.SqlServer.Management.IntegrationServices in my GAC (Windows\assembly). Examining the schema of the SSISDB catalog, it was version 12.0.5000.0, which meant I needed the 12.0.0.0 version of the assembly. The code I was using:

[Reflection.Assembly]::LoadWithPartialName( "Microsoft.SqlServer.Management.IntegrationServices")

was loading the wrong version (probably 13.0.0.0), so I needed to explicitly load the assembly version matching this installation of SSIS, which was 12.0.0.0:

[System.Reflection.Assembly]::Load("Microsoft.SqlServer.Management.IntegrationServices, Version=12.0.0.0, Culture=neutral, processorArchitecture=MSIL, PublicKeyToken=89845dcd8080cc91")