2008-08-14

SQL Server Agent PowerShell job step

This seems like a valid and simple way to execute a PowerShell script in a SQL Server Agent job step.
The script I've been testing with looks like this (ExecuteablePS.ps1):
param( [string]$script:myParam = $( throw "Error: Command statement is missing." ) )

Write-Output "Hello to output."
Write-Host "Hello to host."

Write-Output "The parameter 'myParam' was given the value '$myParam'."
It is on purpose that the script requires a parameter, while most - if not all - my automation scripts is parametrized on the physical elements like server (host).
The script also demonstrates that both the Output stream and the Host stream is caught by SQL Server Agent.

The SQL Server Agent job step is defined as a CmdExec step.
The step only has one line:
powershell.exe -NoLogo -NoProfile -Command "&'C:\SQLAdmin\Script\ExecuteablePS.ps1' 'CoolValue'"
I spend some time getting the " and ' right...

When the job is executed, the history is:
Executed as user: Sandbox\Frodo. Hello to output. Hello to host. The parameter 'myParam' was given the value 'CoolValue'. Process Exit Code 0. The step succeeded.

I use CmdExec in stead of PowerShell as job step type, so that the solution can be used below SQL Server 2008.
Also I like to use powershell.exe instead of sqlps.exe. This makes the solution valid on PowerShell v2.

1 comment:

Adam Mikolaj said...

I spend some time getting the " and ' right...

Yea...you and me both. Luckily I found how you did it after failing for an hour. It was driving me CRAZY.