SQL Server agent job calling powershell script with parameter

Question

Hi,
I need to run below powershell script by passing current date (2022-03-14) in Sql server job as 1st step
Powershell.exe D:\Processes\SQLJobs\Rpts\01\xxs.ps1 -pRunDate @pRunDate

I tried this:
Step Type: Operating System (cmdExec)
Run AS: Sql Server Agent Service Account

declare @apl date
set @pRunDate = GETDATE()

Powershell.exe D:\Processes\SQLJobs\Rpts\01\xxs.ps1 -pRunDate @pRunDate

This is not working. How can I pass value to script parameter

Answer 1

Why don't you just add "Get-Date . . ." to the script????

You're going to run PowerShell from the SQL agent by using CMD.EXE and PowerShell is going to receive that "D:\Processes\SQLJobs\Rpts\01\xxs.ps1" as a positional parameter, not as a named parameter. Try adding "-File" (a parameter name) before the "D:\Processes\SQLJobs\Rpts\01\xxs.ps1".

From Help About_PowerShell:

When the value of File is a file path, File must be the last parameter in the command because any characters typed after the File parameter name are interpreted as the script file path followed by the script parameters.

PowerShell.exe -File D:\Processes\SQLJobs\Rpts\01\xxs.ps1 -pRunDate @pRunDate

I don't know how SQL agent will handle the value "@pRunDate" though. If it substitutes a string in its place it should work.