Creating a SQL Agent Job Wrapper with PowerShell and SMO

I’ve seen this question asked a number of times over the past couple weeks and I thought I would share an approach using PowerShell and SMO. But first, let’s take a step back and try to understand why someone would want to do this. What’s wrong with SQL Agent?

Well there’s really nothing wrong with SQL Agent. The main reason I have seen people asking this question is because their company is looking into using an enterprise job scheduler. An enterprise job scheduler gives an operations group a single location to manage jobs across their whole environment. It allows them to see all the moving parts from a batch processing perspective, even across dissimilar platforms. It also allows them to create more complex workflows across multiple platforms.

So if a company is looking to switch to an enterprise job scheduling solution they will need a way to transition any of their existing SQL Agent jobs to run through the new scheduler. One way to accomplish this is to create a wrapper. A wrapper is nothing more than a script or program that calls another script or program, or in this case, a SQL Agent job.

USE [msdb]

/****** Object:  Job [TestJob]    Script Date: 5/10/2015 9:06:29 PM ******/
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 5/10/2015 9:06:29 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TestJob', 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [TestStep]    Script Date: 5/10/2015 9:06:30 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'TestStep', 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'select @@servername
WAITFOR DELAY ''00:00:10''', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave
function Start-SQLAgentJob
     NAME: Start-SQLAgentJob
     AUTHOR: Chris Sommer
     Version: 1.0
     CREATED: 2015-05-09

     Start a SQL Server Agent job.

     Start a SQL Agent job and wait for its completion. This function relies on the SQL Agent to be up and running.

     .Parameter SQLServer
     SQL Server Name

     .Parameter JobName
     SQL Agent job name

     Start-SQLAgentJob -SQLServer "localhost" -JobName "TestJob"
    param (
        [Parameter(Mandatory=$true)][string]$SQLServer ,
    # Load the SQLPS module
    Push-Location; Import-Module SQLPS -DisableNameChecking; Pop-Location

    $ServerObj = New-Object Microsoft.SqlServer.Management.Smo.Server($SQLServer)
    $JobObj = $ServerObj.JobServer.Jobs | Where-Object {$_.Name -eq $JobName}

    # If the job is and enabled and not currently executing start it
    if ($JobObj.IsEnabled -and $JobObj.CurrentRunStatus -ne "Executing") {

    # Wait until the job completes. Check every second.
    do {
        Start-Sleep -Seconds 1
        # You have to run the refresh method to reread the status
    } While ($JobObj.CurrentRunStatus -eq "Executing")

    # Get the run duration by adding all of the step durations
    $RunDuration = 0
    foreach($JobStep in $JobObj.JobSteps)     {
        $RunDuration += $JobStep.LastRunDuration

    $JobObj | select Name,CurrentRunStatus,LastRunOutcome,LastRunDate,@{Name="LastRunDurationSeconds";Expression={$RunDuration}}
# Setup pathing and environment based on the script location
$Invocation = (Get-Variable MyInvocation -Scope 0).Value
$ScriptLocation = Split-Path $Invocation.MyCommand.Path

# Load the Start-SQLAgentJob cmdlet
. "$ScriptLocation\Start-SQLAgentJob.ps1"

# Set a couple variables for testing and call the cmdlet
$SQLServer = "localhost\inst1"
$JobName = "TestJob"
Start-SQLAgentJob -SQLServer $SQLServer -JobName $JobName

And here are my results:

As always, Happy Scripting!

See also