2012-02-14

SQL Server Agent PowerShell job step

Some time ago I wrote about this in another blog entry, and I do it again while there are some major changes in what I want to use.
First of all I want the SQL Server Agent jobstep to be defined as a PowerShell job step, not a CmdExec job step.
Also I have some other issues I want to handle.
  1. The job step is defined in a script file, not a script in the job step command field.
  2. The script file takes input by named parameters.
  3. There are one or more spaces in the script file path, e.g. "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\JOBS".
When I looked around on the internet, two different solutions were used.
One using the PowerShell call operator ampersand (&).
One using the Invoke-Expression CmdLet. The last should be avoided while it has some risks. These risks are described in the book "Windows PowerShell in Action, Second Edition" (ISBN-13 978-1-935182-13-9) by Bruce Payette in section 21.6.4. This book is most recommend.

Actually it turned out to be quite simple, when I had the quotation marks in place.
& "D:\Microsoft SQL Server\MSSQL10.SANDY\MSSQL\JOBS\SQLPS_test.ps1" -param_00 'Zero' -param_01 'One'
The trick is to put the full name of the script file in quotation marks, and the parameter names and values outside the quotation marks.

Whe you define the job step usin the stored procedure msdb.dbo.sp_add_jobstep, the value of the parameter @command has a lot of quotation marks.
@command=N'& "D:\Microsoft SQL Server\MSSQL10.SANDY\MSSQL\JOBS\SQLPS_test.ps1" -param_00 ''Zero'' -param_01 ''One'''
Please notice that some quotation marks are single and some are double.

The script file is like this.
param(
  [Parameter(Mandatory=$true)][string]$param_00,
  [Parameter(Mandatory=$true)][string]$param_01
)
"{0:s}  Hello." -f $([System.DateTime]::Now)
"{0:s}  Parameter value 00 = $param_00" -f $([System.DateTime]::Now)
"{0:s}  Parameter value 01 = $param_01" -f $([System.DateTime]::Now)
"{0:s}  Goodbye." -f $([System.DateTime]::Now)

and the output is like
2012-02-14T07:50:03  Hello.
2012-02-14T07:50:03  Parameter value 00 = Zero
2012-02-14T07:50:03  Parameter value 01 = One
2012-02-14T07:50:03  Goodbye.

No comments: