SSIS 从 PowerShell 配置参数值 [英] SSIS configuring parameter value from PowerShell

问题描述

我正在尝试在 powershell 中使用以下代码设置参数值

I am trying to set parameter value using the following code in powershell

$IspacFilePath = "D:\SSIS\MyFirstSSIS.ispac"
$ProjectName = "MyFirstSSIS"
$SsisServer = "localhost"
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$assembly = [System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) #| Out-Null;
# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=testdb;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring
# Create the Integration Services object
$ssisServer = New-Object $SsisNamespace".IntegrationServices" $sqlConnection

$Catalog = $ssisServer.Catalogs["SSISDB"]
$Folder = $Catalog.Folders[$FolderName]

if($Folder.Projects.Contains($ProjectName)) {
    Write-Host "Deploying" $ProjectName "to" $FolderName "(REPLACE)"
}
else
{
    Write-Host "Deploying" $ProjectName "to" $FolderName "(NEW)"
}

[byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath)
$Folder.DeployProject($ProjectName, $IspacFile)
$Project = $Folder.Projects[$ProjectName]
foreach ($Parameter in $Project.Parameters)
{
    $Parameter.ReferencedVariableName ="$SqlConnectionstring"
}

这给了我所附的结果，我没有得到所需的配置，所以有人可以告诉我如何设置这个

Which is giving me the result as attached, I am not getting the required configuration so can some one tell me how can I set this

推荐答案

下面是我用来设置包级参数的代码，我假设它对于项目级参数是相同的:

Below is the code I would use to set a package level parameter, I'm assuming that it would be the same for a project level parameter:

$project.Parameters['param_name'].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal, $param_value)
$project.Alter()

同样对于任何连接，您不必显式创建参数，因为这些参数是自动为您创建的.例如，如果您有一个名为 targetserver 的连接，SSIS 将向参数集合添加一个名为:CN.targetserver.ConnectionString 的参数.

Also for any connections you do not have to explicitly create parameters as these are created automatically for you. So for example if you had a connection called targetserver, SSIS will add a parameter to the Parameters collection called: CN.targetserver.ConnectionString.

这篇关于SSIS 从 PowerShell 配置参数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！