Powershell Scripting In SQL Agent - (Test-Path Check if file exists)

In this tutorial we take a look at Powershell scripting in SQL Agent and to do this we use the simple example of the test=path cmdlet.

Test-Path if exists

The Powershell test-path cmdlet lets us do exactly what it says on the tin! It enables us to test a path or a file and check if it exists. The result of this is true or false.
This is such an easy cmdlet to use and so useful and its a great way to show the benefits of using it within SQL Agent.

Here we run test-path to check the file exists, if so then it moves onto the next step of the job. Secondly we use test-path if not exists so if the file isn't there we throw an error.

Powershell Scripts in SQL Agent

We then move the script into SQL Agent and use it as a priliminary check. If the cmdlet results return true to step is marked as a success and then moved onto step 2 of the agent job. If its false we use THROW and this will fail the SQL Agent job.
I've recently used this where I had to check to see whether a backup file exists, if it does then the next step restores the backup. It meant that I was able to run the job without having failures for intermittent missing files (it wasn't a Live environment so it was essential a file existed).

How To Run Powershell in SQL Agent.

Although I show you the script being run within SQL I don't cover how to set the job up (which I can cover in a seperate tutorial), however to do this setup a SQL Agent job in the usual way but set the type as 'Powershell'. This then allows us to use powershell scripts instead of SQL Code.
The other potential issue is if your SQL Server databases aren't setup against the C: drive then you may find errors when executing scripts, in which case you will need to change the directory at the start of the script using CD C:

Thanks for watching and if you have any requests then please let know in the comment section below.

Content

00:00 Overview
00:40 Using Test-Path
01:29 If not test-path
02:26 Throw error powershell script
03:13 Using Powershell in SQL Agent

Links you may find useful

Powershell test-path

Powershell Check if file exists

[vivrycc]

I like your example. I am trying to do the same thing. I am trying set up a SQL Server Agent to run a Powershell script. For some reason the script works perfectly in Windows Powershell ISE but when I put it into the Job Step Properties it fails. Any ideas?