In a previous article, SQL Server Agent Jobs - returning full error details, I showed how to obtain fuller logging information from the msdb tables that SQL Server Agent uses when running agent jobs. This article will use this data to handle another problem that can arise in production systems: tracking the ongoing status of a SQL Server Agent job.

The scenario is the following: I have a series of data sources (flat files, Access databases, Excel files) that contain data that must be loaded into SQL Server. Organisational and administrative criteria mean that no automation is possible. These files arrive by email from different people every time or can be sent on CD, by USB key, etc. Carrier pigeon has not happened yet, but we are still waiting...

Anyway, manual intervention is required, following which a load process must be triggered by a user. For various reasons we have stored all the SSIS packages that load this diverse data in SQL Server, and want to trigger them using SQL Server agent jobs. No problems so far. However, when launching a SQL Server Agent job from an ASP.Net page, users can be perplexed by a lack of feedback and can run jobs several times unless there is adequate on-screen feedback concerning the status of the job they have started. In most cases, this is at best a wasted of time, and at worst extremely detrimental to the purity of our data and capable of causing much trouble further down the line in an ETL process.

So the question is - how can you get job status, both while running and once completed, from SQL Server Agent? Once again the answers are in the msdb tables Sysjobs, Sysjobsteps, Sysjobactivity, Sysjobhistory and sysjobstepslogs. Or more specifically, in two of them, whose uses and subtleties we need to understand:

• Sysjobactivity: This table displays the current status of each job. This is particularly useful for seeing if a job is running, or when it last ran. The three fields that I find the most useful are:

1. start_execution_date - the data and time that an job started
2. stop_execution_date - the data and time that an job ended

Essentially, you can see if a job is running by checking these two date fields. A non-NULL start_execution_date and a NULL stop_execution_date means that a job is running. Also note that each job appears only once in the table. So you only get the execution and timings of the last run - or running - job.

• Sysjobhistory: The history of each job when it ran, including all job steps, until the table is truncated (this is what you see in the Log File viewer). The interesting field here is:

Message - which gives the status of each step once it has run as well as the final status of a job once it has run (for a step_id = 0).

So know we know where to get the data, how do we get it to return the job status - running or finished, and if it is finished - did it succeed?

The solution is in the following stored procedure, which can be used to poll a job status:

CREATE PROCEDURE [dbo].[pr_TestJobProgress]
(
@JobName VARCHAR(200)
)
AS
/*
This procedure tests status of a running job. If the job has run, then it tests 
the outcome (success/failure)
*/DECLARE @OutputCode TINYINT
DECLARE @FinalOutputCode TINYINT
DECLARE @Start DATETIME
DECLARE @End DATETIME
DECLARE @Job_ID UNIQUEIDENTIFIER
DECLARE @Message VARCHAR(200)
-- TEST
-- pr_TestJobProgress 'TestTimer'
/*
Messages are:
0 - Not yet Running
1 - Running
2 - Finished Successfully
3 - Finished Unsuccessfully
4 - Error
*/-- 1. Get the latest message concerning the job status:
select @Start = start_execution_date, 
 @End = stop_execution_date, 
 @Job_ID = JO.Job_ID
FROM msdb.dbo.sysjobactivity JA inner join msdb.dbo.sysjobs JO
ON JA.Job_ID = JO.Job_ID
WHERE JO.Name = @JobName
-- 2. Process the message to return a status code
SET 
@OutputCode = 
CASE 
WHEN @Start IS NULL AND @End IS NULL THEN 0 -- Not yet Running
WHEN @Start IS NOT NULL AND @End IS NULL THEN 1 -- Running
WHEN @Start IS NOT NULL AND @End IS NOT NULL THEN 2 -- Finished running
ELSE 3
END
IF @OutputCode = 0 SET @FinalOutputCode = 0
IF @OutputCode = 1 SET @FinalOutputCode = 1
IF @OutputCode = 2
BEGIN
SELECT TOP 1 @Message = Message
FROM msdb.dbo.sysjobactivity JA inner join msdb.dbo.sysjobs JO
ON JA.Job_ID = JO.Job_ID LEFT OUTER join msdb.dbo.sysjobhistory JH
ON JA.Job_ID = JH.Job_ID
WHERE JH.Job_ID = @Job_ID
ORDER BY instance_id DESC
SET 
@FinalOutputCode = 
 CASE 
 WHEN @Message LIKE '%job succeeded%' THEN 2
 WHEN @Message LIKE '%job failed%' THEN 3
 ELSE 4
 END
END
IF @OutputCode = 3 SET @FinalOutputCode = 4
SELECT @FinalOutputCode AS OutputCode

Code Explanation

This procedure is destined to be run against a SQL Server Agent job several times. It is essentially in two parts:

1) Detect if the procedure is still running or has finished
2) If it has finished - get the outcome

This is extremely simple - but very efficient.

So now, how would you use it from an ASP.Net application to poll the result on a regular basis? The answer is, very easily, and here's how:

Run SSIS from SQL Server agent

So, assuming that I have a web form, or a Winforms application which allows a user to choose files to load, and performs any file manipulation that the ETL process requires, what happens when the process is run? Well, firstly, there is no direct call to a stored procedure, or even to DTEXEC.EXE from the web application, because I have created a SQL Server agent job to run the DTS package.

Out of a sense of completeness, this is the simple way to call a SSIS package from SQL Server agent:

1. Expand the SQL Server Agent node in SQL Server Management Studio, and right-click the "Jobs" node.
2. Select "New Job" and give the job a name.
3. Select "Steps", click "New", and give the step a name.
4. Select SQL Server Integration Services Package as the Type, choose an account for it to run under, and select the package to run. This package can be stored in the file system or in SQL Server or in the SSIS Package store. You should end up with a dialog looking something like this:

   

Finally, confirm the step creation, and the job itself. You can also select a dtsconfig file to provide run-time parameters, if your SSIS package is configured to use them.

That is all - your SSIS package will now run from SQL Server Agent, in the security context of the account that you selected.

Polling status from ASP.Net

So now we need to call the SQL Server Agent job from ASP.Net. Fortunately, Microsoft have made all our lives much simpler by providing a stored procedure "pr_RunAgentJob" which does all the work for us. We will therefore call this Sproc just as we would call any stored procedure from ASP.Net.

Here is a sample code snippet to call the SQL Server Agent job from ASP.Net. It contains basic status information, because if the job has failed to start, then there will be nothing to poll - and we don't want to leave users in the dark should this happen, either!

 Private Sub cmdProcessFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdProcessFile.Click
 ' First call an SQL Agent job that runs the SSIS package
 Dim jobConnection As SqlConnection
 Dim jobCommand As SqlCommand
 Dim jobReturnValue As SqlParameter
 Dim jobResult As Integer
 jobConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MyAppConnectionString").ToString())
 jobConnection.Open()
 jobCommand = New SqlCommand("pr_RunAgentJob", jobConnection)
 jobCommand.CommandType = CommandType.StoredProcedure
 jobReturnValue = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
 jobReturnValue.Direction = ParameterDirection.ReturnValue
 jobCommand.Parameters.Add(jobReturnValue)
 Dim parmJobName As SqlParameter = jobCommand.Parameters.Add("@JobName", Data.SqlDbType.VarChar, 200)
 parmJobName.Value = "LoadMyETL"
 jobCommand.ExecuteNonQuery()
 jobResult = DirectCast(jobCommand.Parameters("@RETURN_VALUE").Value, Integer)
 jobConnection.Close()
 Select Case jobResult ' If succesful - then use data
 Case 0
 Me.lblErrors.Text = "The package started successfully."
 TimerImage.Visible = True
 Timer1.Enabled = True
 Case 1
 Me.lblErrors.Text = "The package is already running."
 Case Else
 Me.lblErrors.Text = "The package failed to start."
 Timer1.Enabled = False
 TimerImage.Visible = False
 End Select
 End Sub

Now we get to the interesting part - polling the job once it is running.

Polling is triggered once the job has been started, using a function that I have called PollSQLServerAgent.

To display the results of the regular poll have also created an ASP label named "lblErrors" to display the feedback from the polling process.

The code which polls the status of the SQL Server Agent job is as follows. Note that you have to specify the name of the job to poll:

 Public Sub PollSQLServerAgent()
 Dim pollStatus As String = ""
 Dim pollConnection As SqlConnection
 pollConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MyETLConnString").ToString())
 pollConnection.Open()
 Dim pollTest As SqlCommand
 pollTest = New SqlCommand("pr_TestJobProgress", pollConnection)
 Dim parmJobName As SqlParameter = pollTest.Parameters.Add("@JobName", Data.SqlDbType.VarChar, 200)
 parmJobName.Value = "LoadMyETL"
 pollTest.CommandType = CommandType.StoredProcedure
 pollResult = pollTest.ExecuteScalar()
 pollConnection.Close()
 Select Case pollResult
 Case 0
 Me.lblErrors.Text = "The process is starting."
 Case 1
 Me.lblErrors.Text = "The process is running."
 Case 2
 Me.lblErrors.Text = "The package succeeded."
 Timer1.Enabled = False
 TimerImage.Visible = False
 Case 3
 Me.lblErrors.Text = "The package failed to complete."
 Timer1.Enabled = False
 TimerImage.Visible = False
 End Select
 End Sub

Of course, you will have to edit or add your own connection string to the web.config file, or place the connection string directly in the sub routine if you prefer.

The actual polling occurs because the timer has been implemented, using this code snippet:

 Protected Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
 PollSQLServerAgent()
 End Sub

Conclusion

With a little applied ingenuity you can return process information from SQL Server Agent jobs to a caller webforms app, and reassure your users as to the status of the job that they are running. You can also poll the status from Winforms using the same stored procedure, but using slightly different calling code.