adTempus - Sending dynamic responses

Jason (2 posts)
March 21, 2012 07:39 AM
Accepted Answer

I need to setup an adTempus job that sends the client an email with the results of a count(*) on a particular table.


 There are a couple of pieces of functionality that I’d like to use, but not sure if they exist in adTempus. Please let me know if you know the answer.    

1.)    Write contents of the resulting SQL log file to a variable that will be displayed in the email message. a.       Example: If the results of my count = 2. My message could say “There are 2 records in table XYZ today.”

2.)    If contents of the resulting SQL log file are =0 don’t send a response.

This topic has an accepted answer. Jump to it.
Bill Staff (601 posts)
March 21, 2012 02:44 PM
Accepted Answer

You can do this by writing a script in adTempus that queries the database.

Here's one approach:

In your job, create a step that sends a notification message. You'll need to set the client up as a notification recipient in adTempus.

In the message body for the notification message, include the token "%TableRecordCount%" at the point where you want to have the record count. For example:

There are %TableRecordCount% records in table XYZ today

Then go to the Conditions tab for this step and add a condition that runs a script. Use VB.NET or C#. In your script you'll need to write the code to connect to the database and do the record count query.

If the table has records, you need to do two things:

  • Define the "TableRecordCount" variable with the record count, so that it will be inserted in your message where you have the "%TableRecordCount%" token. Assuming you've got the record count in an integer variable named recordCount, the syntax for this from within the script would be:

    adTempus.JobVariables.Add("TableRecordCount",recordCount.ToString(),false)
  • The script function must return a true result, which tells adTempus to go ahead and do the notification.

If the table has no records, your script simply returns a false result. This tells adTempus not to do the notification.

Now schedule the job to run at whatever time is appropriate.

Here is an example of the condition script. Set the Language to VB.NET. You'll also need to add "System.Data" in the Referenced Assemblies section. This assumes your database is SQL Server.

'This script gets the record count from a table and sets the TableRecordCount variable based on the record count.
'The script is intended to be used as a condition script
'Modify the serverName, databaseName, and tableName settings below to meet your needs.
'The script connects to SQL Server using integrated Windows security, so the job must run under an
'account that has the necessary permissions on the database server.
 
Imports System
Imports System.Collections
Imports ArcanaDevelopment.adTempus.Server
Imports System.Data.SqlClient
 
Public Class UserScript
    Inherits ArcanaDevelopment.adTempus.ScriptEngine.UserScriptBase
 
    Public Overrides Function Run() As Object
        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim recordCount As Integer
         
        Dim serverName As String="servername"
        Dim databaseName As String="databasename"
        Dim tableName As String="tablename"
         
        Dim connectionString As String
        connectionString=String.Format("Data Source={0}; Initial Catalog={1}; Integrated Security=SSPI",serverName,databaseName)
         
        connection=New SqlConnection(connectionString)
        connection.Open()
        command=New SqlCommand("select count(*) from " & tableName,connection)
        recordCount=CInt(command.ExecuteScalar())
 
        If recordCount=0 Then
            'no records; tell adTempus not to run step
            Return False
        Else
            'define the TableRecordCount variable to be used in the notification message
            adTempus.JobVariables.Add("TableRecordCount",recordCount.ToString(),False)
             
            'return True to tell adTempus to run the notification
            Return True
        End If
    End Function
End Class
Jason (2 posts)
March 23, 2012 05:46 AM
Accepted Answer

Thank you the insight.

Bill Staff (601 posts)
August 2, 2013 02:06 PM
Accepted Answer

Beginning with adTempus 4, you can use the Database Operation Task to get the record count for you.

To do it this way, you would insert a new step that runs the Database Operation task. Use the "Select a scalar value into a Job Variable" option to execute your query and put the record count in the "TableRecordCount" variable.

On your notification step, instead of the script condition described above, use a Job Variable Condition that checks to see if the "TableRecordCount" value is greater than 0. This way the notification will be sent only if there are records.

 

Replies are disabled for this topic.