将变量传递给 SSIS 包中的 powershell 脚本 [英] Passing variables to a powershell script within a SSIS package

问题描述

我正在尝试将 SSIS 变量传递到通过 SSIS 中的进程任务运行的 PowerShell 脚本中，如果有任何区别，我将使用 SSIS 2008

I am trying to pass a SSIS variable into a PowerShell script that is running via a Process Task within SSIS, im using SSIS 2008 if that makes any difference

这是我使用的 powershell 脚本的副本，在使用硬编码值执行时运行良好

Here is a copy of the powershell script im using that runs fine when executed with hardcoded values

param ([string]$SourceServer, [string]$DestinationServer, [string]$Filename )

$SourceServer = "SERVERA"
$DestinationServer = "SERVERB"
$Filename = "DbNAME.mdf"

$SourcePath = "\D$\Data\"
$DestinationPath = "\D$\Data\Backups\"

$Source = '\\' + $SourceServer + $SourcePath + $Filename
$Destination = '\\' + $DestinationServer + $DestinationPath + $Filename

copy-item -path $Source -destination $Destination -verbose

如果我对 param's 进行硬编码，我可以让 PowerShell 脚本正常运行，但是一旦我将其更改为变量，变量值就不会通过

I can get the PowerShell script to run fine if I hardcode the param's , however as soon as I change it to a variable the variable value isn't being passed through

在流程任务中，这是可执行文件

Within the process task this is the executable

"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe"

参数字符串被正确构建，所以我知道变量值被传入

The arguments string is being built correctly so I know the variable value is being passed in

-ExecutionPolicy Unrestricted -File "C:\Qarefresh.ps1" "DbaseA.mdf"

这里是表达式的代码

"-ExecutionPolicy Unrestricted -File \"" +  "C:\\Qarefresh.ps1\" \"" + @[User::QA_FileName] + "\""

我对 PowerShell 比较陌生，所以如果我错过了一些基本的东西，请道歉，但我已经接近用这个来拔头发了

I'm relatively new to PowerShell so apologise if I have missed something basic , but i'm close to pulling out my hair with this one

预先感谢您提供的任何帮助

Thanks in advance for any help given

推荐答案

调用PowerShell时需要指定参数名称.

You need to specify the parameter names when you invoke PowerShell.

这是 PowerShell 脚本.

This is the PowerShell script.

param ([string]$SourceServer, [string]$DestinationServer, [string]$Filename)

[string]$SourcePath = "\I$\StackOverflow\Xp\XpSsisPowerShell\Input\";
[string]$DestinationPath = "\I$\StackOverflow\Xp\XpSsisPowerShell\Output\";

[string]$Source = "\\" + $SourceServer + $SourcePath + $Filename;
[string]$Destination = "\\" + $DestinationServer + $DestinationPath + $Filename;

Copy-Item -Path $Source -Destination $Destination;

然后您可以像这样从命令提示符测试脚本，传递参数.

Then you can test the script from a command prompt like this, passing parameters.

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -ExecutionPolicy Unrestricted -File I:\StackOverflow\Xp\XpSsisPowerShell\Script\CopyFile.ps1 -SourceServer Baobab -DestinationServer Baobab -Filename TestData.txt

然后文件被复制到输出文件夹.

And the file gets copied to the output folder.

要从 SSIS 包调用 PowerShell 脚本，首先要设置必要的变量.

To invoke the PowerShell script from an SSIS package, first set the necessary variables.

然后添加一个执行流程任务.在进程选项卡上，设置可执行字段.

Then add an Execute Process Task. On the Process tab, set the Executable field.

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

使用以下表达式设置 Arguments 字段:

Set the Arguments field with the following expression:

"-ExecutionPolicy Unrestricted -File I:\\StackOverflow\\Xp\\XpSsisPowerShell\\Script\\CopyFile.ps1 -SourceServer " + @[User::SourceServer] + " -DestinationServer " + @[User::DestinationServer] + " -Filename " +  @[User::Filename]

在运行包之前确保输出文件夹为空.(这里没有作弊！)

Make sure the output folder is empty before running the package. (No cheating here!)

执行 SSIS 包.

并将文件复制到输出文件夹.

And the file is copied to the output folder.

顺便说一句，将 $ 放在双引号中时需要小心，因为变量名会被替换，如下所示:

Incidentally, you need to be careful when putting $ within double quotes, because variable names get substituted, like this:

这篇关于将变量传递给 SSIS 包中的 powershell 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！