Summary

This article describes how to create an adTempus job that executes a database query and sends the results in an e-mail message.

Sample Job

An adTempus export file is available containing the Script Library and sample job discussed in this article. Download the database table to email export file and import it into your adTempus server. Then find the "Database table to email" Job and the "DataSetUtility" Script Library.

Discussion

The adTempus Database Operation Task can be used to query a database and return the results as a .NET DataSet. In this example we use a script to format the data into an HTML table, which is then used to send an HTML formatted notification message. We use this approach:

  • Execute the Database Operation Task to fetch the data and send it to a script.
  • In the script, format the data into an HTML table.
  • Put the resulting HTML code into a Job Variable.
  • Execute a Notification Task to send an e-mail message to the appropriate recipients. In this message we insert the HTML from the Job Variable.

Formatting the data

To format the data into an HTML table we will use a VB.NET script. To make the solution reusable, we create a Script Library named DataSetUtility, so that the formatting functions can be called by scripts in many jobs.

To create the Script Library:

  1. Go to the Script Libraries view in the Console, then right-click and select New.
  2. In the Script Library editor, set the Name to "DataSetUtility".
  3. Make sure the language is set to "VB.NET".
  4. Replace the sample code in the code editor with the following code:
Imports System
Imports System.Data
Imports System.Text
Imports System.Collections.Generic
Imports ArcanaDevelopment.adTempus.Shared
Imports ArcanaDevelopment.adTempus.ApplicationIntegration
 
Public Module DataSetUtility
    Public Function DataSetToHtmlTable(dataset As Dataset) As String
        Return DataTableToHtmlTable(dataset.Tables(0))
    End Function
 
    Public Function DataTableToHtmlTable(table As DataTable) As String
        Dim html=New StringBuilder()
         
        html.Append("<table><thead><tr>")
        'add column names to header
         
        For Each col As DataColumn In table.Columns
            html.AppendFormat("<td>{0}</td>",col.ColumnName)
        Next
         
        html.Append("</tr></thead><tbody>")
         
        'add data for each row
        For Each row As DataRow In table.Rows
            html.Append("<tr>")
            For Each item As Object In row.ItemArray
                html.AppendFormat("<td>{0}</td>",item)
            Next
            html.Append("</tr>")
        Next
         
        html.Append("</tbody></table>")
        Return html.ToString()
    End Function
End Module

These functions format a DataTable (or the first DataTable in a DataSet) into an HTML table. It constructs a header row using the column names, and then adds an HTML row for each row in the DataTable.

Create the job

Next we create a job to retrieve, format, and send the data.

Variable for the HTML Table

Add a job-level variable named "DataSetHtmlTable" (string type; leave the value empty). This will receive the formatted HTML table from the script.

Retrieve the data

The first step of the job uses a Database Operation Task to query the database. Set the appropriate database connection details on the Database Connection tab. On the Database Operation tab, select the "Select data into a DataSet" option. Enter the appropriate query to fetch the required data.

Format the data

In the DataSet Selection section of the Database Operation tab, select the "script" option and click New to create a new script. Each time this step runs, adTempus will execute your query, then send the resulting data (as a DataSet) to the script.

In the Script Editor, select the DataSetUtility library in the Included Script Libraries section.

Replace the script body with the following code:

Imports System
Imports System.Collections.Generic
Imports ArcanaDevelopment.adTempus.Shared
Imports ArcanaDevelopment.adTempus.ApplicationIntegration
Imports System.Data
 
Public Class UserScript
    Inherits ArcanaDevelopment.adTempus.ApplicationIntegration.DatabaseTaskScriptBase
 
    Public Overrides Function Run() As Object
 
        'The Parameters.DataSet object provides access to the data fetched from the database.
         
        If Parameters.DataSet.Tables.Count=0 OrElse Parameters.DataSet.Tables(0).Rows.Count=0 Then
            'no data returned. Don't set the variable. Notification step can use empty variable condition to skip sending message.
            Return True
        End If
         
        'Call the helper function to turn the data into an HTML table
        Dim htmlTable=DataSetUtility.DataSetToHtmlTable(Parameters.DataSet)
         
        'put the HTML into a variable to be used in the notification task
        adTempus.JobVariables("DataSetHtmlTable")=htmlTable
         
        Return True
    End Function
End Class

This code retrieves the DataSet passed by the task. If the DataSet does not contain any data (no rows returned by the query), the script returns without setting a value for the DataSetHtmlTable variable.

If data is present, it calls the DataSetToHtmlTable function from our script library, which formats the data into an HTML table. It then places the HTML markup into the DataSetHtmlTable variable for use later in the job.

Send the message

The second step of the job uses a Notification Task to send an e-mail message containing the HTML table.

If you only want to send the message if the query in step 1 returned data, add a Condition to this step to check the value of the DataSetHtmlTable Job Variable. The step should only run if the variable is not empty.

For the Message Body, we need to use HTML markup to create  the message. We include the "%DataSetHtmlTable%" token to insert the HTML table in the appropriate location. This example creates a message with minimal content and formatting:


<html>
<body>
<p>Here is the data from the database:</p>
%DataSetHtmlTable%
</body>
</html>

Refinements

This sample uses minimal formatting for the HTML. In practice you will probably want to add some styling to the HTML generated in the helper library, and to the HTML content in the notification message.

If you want to send a message regardless of whether any data was returned, you can set the initial value of the DataSetHtmlTable variable (in the job properties) to the text you want to use in the message if there was no data. For example, set the variable to

<p>No data was available today.</p>

Now the notification message will always be sent, and this placeholder message will be included if it's not overwritten with data by the script.