LLI Wiki — My Digital Knowledge Base

SQL Server Agent: run a PowerShell script from a job

Two ways to run a PowerShell script in a SQL Server Agent job step, with error handling.

This page shows two ways to run a PowerShell script from a SQL Server Agent job step, plus a few commands useful to prepare the environment.

Run the script in a separate PowerShell process

The script is run by a new powershell.exe. With $ErrorActionPreference = "Stop" and Throw, an error makes the step fail.

$ErrorActionPreference = "Stop"

try
{
    Set-Location C:\PowerShell\Scripts
    powershell.exe -c "C:\PowerShell\Scripts\refresh.ps1"
}
catch
{
    Throw
}

Run the script in the current session

The script is loaded by dot sourcing (.), without an extra process.

$ErrorActionPreference = "Stop"

try
{
    Set-Location C:\PowerShell\Scripts
    . C:\PowerShell\Scripts\refresh.ps1
}
catch
{
    Throw
}

Other useful commands

Call the PowerShell executable directly with a script:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe D:\Scripts\PowerShell\Restore.ps1

Load a functions file:

. D:\PS\Functions.ps1

Allow local scripts for the current user:

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser -Force

Install the NTFSSecurity module at a specific version:

Install-Module -Name NTFSSecurity -RequiredVersion 4.2.4
Mastodon LLIMastodon SocialFosstodon