Powershell 错误设置 SSIS 包执行的环境变量 [英] Powershell error setting Environment variable for an SSIS package execution

问题描述

我的 Powershell 脚本执行 SSIS 包，但首先覆盖环境变量.EnvironmentInfo 对象上的 Alter 方法失败并显示一般错误消息:对象 [EnvironmentInfo[@Name='MyVariable']' 上的操作‘Alter’在执行期间失败."

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."

我还尝试删除环境变量并更改 Project 参数，但在 Project 对象的 Alter 方法上收到相同的错误.

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.

我怀疑这是 1) 使用 32 位版本的 SQL Server 2012 的缺点，或 2) 权限问题.

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

我已确保正在执行的 Windows 帐户对 SSISDB 数据库和 SSIS 目录项目以及子文件夹、环境等具有完全权限.

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.

关于错误的任何想法或如何获得更多详细信息?我在 Windows 事件日志中没有看到任何内容.

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

这是我的代码:

# 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."

推荐答案

所以通过一些挖掘，我找到了错误的答案.结果我在我的 GAC(Windows\assembly)中有多个版本(11.0.0.0、12.0.0.0、13.0.0.0)的程序集 Microsoft.SqlServer.Management.IntegrationServices.检查 SSISDB 目录的架构，它是版本 12.0.5000.0，这意味着我需要 12.0.0.0 版本的程序集.我使用的代码:

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")

加载了错误的版本(可能是 13.0.0.0)，所以我需要显式加载与此 SSIS 安装匹配的程序集版本，即 12.0.0.0:

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")

这篇关于Powershell 错误设置 SSIS 包执行的环境变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！