Set-Location C:

Over the years of using PowerShell with SQL Server I have learned a few things of how to use the PowerShell step type in a SQL Agent job.  When running a job step of PowerShell there are a few things you should know.  There is a post by SQLHammer that illustrates one of them here. The one that shows that if you are using a PowerShell job step, you will want to use a Set-Location C: at the top of the script, especially if you are going to do anything in the FILESYSTEM as the SQL Server Provider does not like the interaction.

Error Handling in the Step

In the job step you will see the dialog below:

What you don’t see is the way you have the job step succeed or fail. When using most commands in modules, you may find that errors still cause the step to fail because of the way they report the failure (some kind of throw or a Stop condition outside your control). So if you want things to fail that normally would show red on the screen but things would continue, remember that the default ErrorAction is Continue, so even though you get an error, PowerShell will just continue.

The tip is that when you want to have something like a dir c:\dbaduck fail even though it is not a critical error and if you did run this in a job step, by default it would show success in the job step even if the command failed because the directory does not exist.  So on those statements that you would like to cause the job to fail and stop you need to use the –ErrorAction Stop on your command.

dir c:\dbaduck –ErrorAction Stop

The above command would stop the job if dbaduck was not a directory in the file system. So remember that failing the job is important when wanting to govern the outcome or the actions after the step.