Passing variables to a powershell script within a SSIS package

Question

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

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

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"

And here is the code for the expression

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

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

Answer 1

You need to specify the parameter names when you invoke 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.

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

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

Execute the SSIS package.

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: