Welcome to my latest article teaching you to execute a PowerShell script via the SSIS Process Task. The Execute Process Task may be used to execute just about any EXE process and has a number of inputs, amongst which are the binary path and command line arguments.

The issue when attempting to execute a PoSh process arises when the arguments string (or expression if you're using one) is not properly formed. Indeed this has caught out many people in the past, so I thought it was worth visiting to detail exactly how to configure the process itself. The proper forming of the arguments string is achieved by way of correctly escaping any characters that may otherwise be interpreted differently.

For this demonstration, I'm going to create a basic package. I'm going to use an expression based argument string that will be formed using package variables and normal text. Let's look at the package quickly now. We'll need to add an "xecute Process Task to start. You can see the task in Diagram 1.

Diagram 1

Now that the task has been added, as shown in Diagram 2, save the package.

Diagram 2

We now need to add the required variables that will be used to supply the Powershell executable Arguments parameters, adding package variables is a fairly standard process. We need three variables.

• First Name     Type=String
• Surname        Type=String
• Script path     Type=String

Once you have the variables added we'll configure the process task. Here we see the variables created with some default values.

Diagram 3

Now let's edit the task. The first tab is the General tab, where we give the task a meaningful name.

Diagram 4

Our task will be expression based, so go to the "Expressions" tab.

Diagram 5

After selecting the "Arguments" property, click the elipsis button to open the expression builder. Here we build and evaluate the Arguments expression. This is shown in Diagram 6.

Diagram 6

Let's take a moment to disect the string I'm using. Here's the whole string

"-ExecutionPolicy Unrestricted -File \"" + @[User::ScriptPath] + "\\exec posh script.ps1\" \"" + @[User::FName] + "\" \"" + @[User::SName] + "\""

Those of you who are used to building dynamic SQL strings will understand the process of escaping single quotes when string building. We have to perfom similar escaping in SSIS to make the string legible to the PowerShell subsystem. in SSIS escape sequences use a backslash ( \).

The escape sequences are listed as

Now since PowerShell uses quotation marks for parameter passing, if these quotation marks are not correctly escaped in the string expression, the command line arguments will be nonsense. In that case, Powershell will fail the command. Had I not escaped the above string correctly and passed this

"-ExecutionPolicy Unrestricted -File "" + @[User::ScriptPath] + "\exec posh script.ps1" "" + @[User::FName] + "" "" + @[User::SName] + """

The expression builder would fail to evaluate and save the string. This is how the expression should look once saved and evaluated

Diagram 7

Finally, on the Process tab of the Execute Process TaskEditor dialog, we see the full EXE and Argument parameters.

Diagram 8

The PowerShell script itself that I'm calling is shown below.

Diagram 9

The PowerShell script content is detailed here.

#declare calling params 
param( [string] $Firstname, [string] $Surname ) 
#end calling params 
#main body 
#Get the current date 
$rundate = Get-Date -displayhint date 
#Now write a message 
Write-Output " Hello $($Firstname) $($Surname) thank you for running this 
script via`r`nthe SSIS subsystem on $($rundate)" | out-file -filepath 
"C:\support\poshscriptout.log"

Once saved and compiled\built, the package may either be uploaded to a SQL Server instance or stored on the NTFS file system. I've stored mine in SQL Server and configured my SQL Server agent job as shown in Diagram 10. Notice the new values I've passed to the variables.

Diagram 10

Now when my agent job runs, the PowerShell script is picked up from the new location, and I get an output file in the location specified in the PowerShell scipt. This path is: C:\support\poshscriptout.log

The defaults I used for the variables in my package were

Firstname = Perry

Surname = Whittle

ScriptPath = C:

The output file contains the followng text:

Diagram 11

Now we have seen how to setup an "Execute Process Task" to run a PowerShell script, I do hope you find this useful, it will hopefully save you hours of hair pulling trying to work out why your string is invalid. Farewell until next time, and as always, post in the discussion, and I'll help all I can with any queries.