Checking Job Step Output Mid-Job using PowerShell

Words: 627

Time to read: ~ 3 minutes

The XY Problem

Them: We have this job…

Me: Right…

Them: … and one of the steps in the job creates files…

Me: Okay…

Them: … and we need you to check if it creates the files, otherwise we don’t need to do any of the rest of the steps.

Me: Why don’t use just error out if that step fails?

Them: Cause there are other errors in that step but they don’t mean it failed

Me: … say what?

Pretty close representation of the conversation

Investigation

I’m going to ignore the whole “there are other errors” for the moment and actually attempt this task. First, let’s try to see if there is a way to get the last time a job step ran.

I already have a test SQL Agent job called “TestEmail” so let’s use that for our test.

(Get-DbaAgentJob -SqlInstance localhost -Job 'TestEmail').JobSteps

Glancing at the output, it appears that we’re looking for the LastRunDate property. In this screenshot, it shows 1/1/0001 12:00:00 AM which means it’s never run before.

Creating Files

We need a way to create files, and not create files, on demand.

Vaguely in the back of my head (and I apologise for not remembering whom), I remembered someone using the presence of a temp table to fire or not fire a trigger. We’re going to use that premise here.

In a SSMS window, we have this code:

USE __DBA;
GO

/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;

/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);

If we want to create files from the PowerShell script, we need to drop the table.
If we don’t want to create files from the PowerShell script, we need to ensure the table exists.

Next, we create this PowerShell file which I’ve called “CreatePowerShellFiles.ps1“.

$Query = @'
IF EXISTS (SELECT 1/0 FROM [sys].[tables] WHERE [name] = N'DoNotCreateFiles')
BEGIN
    SELECT CreateFiles = 0;
END; ELSE
BEGIN
    SELECT CreateFiles = 1;
END;
'@

[bool]$CreateFiles = (Invoke-DbaQuery -SqlInstance localhost -Database __DBA -Query $Query).CreateFiles

if ($CreateFiles) {
    [PSCustomObject]@{
        Name = 'CreatedFile'
        DateCreated = Get-Date
        Nonce = New-Guid
    } | Export-Csv -NoTypeInformation -Path "C:\Users\shane.oneill\Desktop\TestPowerShellCreatedCode_$(Get-Date -Format FileDateTime).csv"
}

Adding this file as a step in our job, it checks for the existence of our table – if the table exists it does nothing otherwise it creates a sample csv file.

Now for the main course

We’re going to add another step now. This one will check for files created after the previous step has run.

First, we’ll create a PowerShell file (“CheckPowerShellFiles.ps1“).

param (
    [Parameter(Mandatory)]
    $JobName,

    [Parameter(Mandatory)]
    $StepName,

    [Parameter(Mandatory)]
    [ValidateScript({ Test-Path -Path $PSItem })]
    $FileDirectory
)

$Jobs = Get-DbaAgentJob -SqlInstance localhost -Job $JobName

$LastStep = $Jobs.JobSteps |
    Where-Object Name -eq $StepName

$FilesExist = Get-ChildItem -Path $FileDirectory |
    Where-Object LastWriteTime -ge $LastStep.LastRunDate

if (-not $FilesExist) {
    $ErrorMessage = 'Files were not created after {0}' -f $LastStep.LastRunDate
    throw $ErrorMessage
}

And add it to the job, passing in the parameters that we want:

Test Run

We’ve got two states that we want to test

1. The files get created.
   1. Job should succeed.
2. The files don’t get created.
   1. Job should fail.

Let’s run the first test:

• Make sure the table is dropped so we create the files:

USE __DBA;
GO

/* Create the files */
DROP TABLE IF EXISTS dbo.DoNotCreateFiles;

• Now run the job:

Success!

Now to check that the job will fail if no files get created:

• Make sure the table exists so no files get created:

/* Do not create the files */
CREATE TABLE dbo.DoNotCreateFiles (DoNotCreateFilesID int NOT NULL);

• Now run the job:

Taking a look at the job history, we can see our error message:

Finally

Well, we’ve proved that this method works!

I can pass on “CheckPowerShellFiles.ps1” to the people who requested the check telling them that they only need to add in the right values for the parameters…

Along with a polite note along the lines of “you should really fix your errors”.