Invoking (running) SQL Server Agent Jobs using PowerShell

As I navigate the meandering thread of complexities that is the identity management project I inevitably find myself using SQL Server more and more.  Forgone is the text delimited management agent; embraced is the database management agent (specifically, SQL Server).  CSV files are pushed into staging tables, pre-processed (manipulated) and consumed by the FIM Synchronization Service.  The process of transforming the data from file into SQL (simply documented here) must then be invoked in one of two ways:

  1. From synchronisation scripts, i.e. before the “HR” MA import runs; or
  2. From SQL Server, i.e. the agent itself drives the synchronisation scripts.

If you favour the former then the following script, barely more than a formatted and commented wrapper around samples readily available across social.technet.microsoft.com and elsewhere on the Internet, might be worth a download.

## ExecSqlServerAgentJob.ps1 v1.0ps1
##   Paul Williams (pawill@microsoft.com) Microsoft Services Sep. 2011
##
## Simple script, based on snippets scattered across social.technet.microsoft.com
## that invokes a SQL Server Agent job.
##
## Two optional parameters can be used:
##  -HostName supplies the name of the SQL Server where the job exists.  The expected
##   formats for this are either the alias name, hostname or hostname\instance name.
##
##  -JobName supplies the name of the job to run.  Defined as optional, but actually
##  rather necessary if you want to invoke a job.  If the job contains spaces in the
##  name enclose in either single or double quotes.
##
## The script makes use of SQL Server Management Objects (SMO) thus must be run from
## a computer that has SQL SMO installed.  This script was written to be run locally on
## a SQL server to invoke agent jobs for the purpose of FIM deployments.  The author is
## not a SQL Server expert, rather an occasional user for the greater good of FIM and ILM
## projects.
##

PARAM
(
    [Parameter(Mandatory = $false)]
    [String]$HostName = (gc Env:\COMPUTERNAME),

    [Parameter(Mandatory = $false)]
    [String]$JobName = [String]::Empty
);

Write-Host "`nExecSqlServerAgentJob.ps1 v1.0ps1 Paul Williams (pawill@microsoft.com) Sep. 2011";

if([String]::IsNullOrEmpty($JobName))
{
    Write-Host "`nNo job name provided.  Provide a job name using the -JobName parameter.`n";
    Exit;
}

Write-Host "`nLoading dependent assemblies and instantiating objects (this can take several seconds)...";

# Load SMO and instantiate the server object
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") |Out-Null;
[Microsoft.SqlServer.Management.Smo.Server]$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $HostName;

if([String]::IsNullOrEmpty($sqlServer.Urn))
{
    Write-Host "`nThe hostname provided is not a valid SQL Server instance.  Did you mistype the alias or forget to add the instance name?`n";
    Exit;
}
else
{ # This block uses rudimentary string manipulation to extract the actual host [+instance] of the
  # server.  This is especially useful if you use aliases.  This is for output purposes only.
    [String]$databaseInstance = $sqlServer.JobServer.Urn.Value.Substring($sqlServer.JobServer.Urn.Value.IndexOf("'") + 1, `
        $sqlServer.JobServer.Urn.Value.IndexOf("'", ($sqlServer.JobServer.Urn.Value.IndexOf("'")) `
            - ($sqlServer.JobServer.Urn.Value.IndexOf("'"))));
}

Write-Host "Enumerating jobs on server ...";
# Match the job name (if possible) from the list of jobs (readable by the user context)
[Microsoft.SqlServer.Management.Smo.Agent.Job]$job = ($sqlServer.JobServer.Jobs | ? { $_.Name -eq $JobName });

if($job -eq $null)
{
    Write-Host "`nNo such job on the server.`n";
    Exit;
}

# Finally invoke the job.  Use the job history (in SSMS) to verify the success of the job.
Write-Host "Executing job (the script doesn't wait for the job to finish)...`n`n";
$job.Start();

As the comments imply, run from a box running the SQL Server Native Client and specify a hostname in the form of either the hostname (in which only supply this argument if you want to qualify the hostname as the computer name is the default), the hostname and instance separated by a backslash (hostname\instance) or an alias (my preference) and a job name.  Here’s an example:

PS C:\dev\lang\ps1\sql> .\ExecSqlServerAgentJob.ps1 -HostName idmsys -JobName importhrdatafromcsv

ExecSqlServerAgentJob.ps1 v1.0ps1 Paul Williams (pawill@microsoft.com) Sep. 2011

Loading dependent assemblies and instantiating objects (this can take several seconds)...
Enumerating jobs on server ...
Executing job (the script doesn't wait for the job to finish)...

PS C:\dev\lang\ps1\sql>

Nothing overly new here, but hopefully handy –I certainly use this now, anyway.

Advertisements

About Paul Williams

IT consultant working for Microsoft specialising in Identity Management and Directory Services.
This entry was posted in Scripting, SQL Server and tagged , , , , , , , , . Bookmark the permalink.

One Response to Invoking (running) SQL Server Agent Jobs using PowerShell

  1. Kiran says:

    Hi Paul Williams,
    I am excited to see your script on your blog.I have a requirement like can we
    Run a particular step only in a SQL JOB using powershell script.
    Example
    step1
    step2
    step3
    step4
    i want to run step2 and later step 4 like random pick..

    Please let me know and please post your views on the requirement above.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s