Using Information Distribution Task

 

1 Waht is Information Distribution Task?

In SQLMessenger, you can set up a recipient list in a task. SQLMessenger will execute SQL queries separately for each recipient in the list and send the query results to that recipient. This kind of task is called "Information Distribution Task".

When the system executes an "Information Distribution Task", it first initiates a task instance of the task (This instance is referred to as the "parent task instance"). The parent task instance reads the "Recipient List(Loop Data)" from the specified data source, and generates sub-task instance for each record in the list. It also passes the corresponding record data in the list as parameters to the sub-task instances. The sub-task instances process the email body template and attachment templates, and generate the emails to be sent. In the sub-task instances, you can use "@@LoopData(Column Name)" to retrieve the data of the corresponding record in the "Recipient List (Loop Data)". The parent task instance only completes and exits after all sub-task instances have finished running.

The following is a typical execution flow of an Information Distribution Task:

The execution flow of the parent task instance

The execution flow of a sub-task instance

2 Enable the "Information Distribute" Option

To make a task an "Information Distribution Task," you first need to enable the "Generate a subtask instance for each recipient in the Recipient List to individually query and send information (Loop Task)" option in the "Information Distribute" tab of the task editor.

Enable the "Generate a subtask instance for each recipient in the Recipient List to individually query and send information (Loop Task)" option for the task

3 Configuring the Recipient List (Loop Data)

The Recipient List contains the information of the recipients to whom you want to send information. SQLMessenger will generate a subtask for each record in the list and pass the data of that record as a parameter to the subtask.

You can save the Recipient List in an Excel or CSV file and import this file when configuring the task. Alternatively, you can save the Recipient List in a table in a database, and the system will read the recipient list from this table during task execution.

Click the "Recipient List(Loop Data) Config" button to open the "Recipient List(Loop Data) Config" dialog box

"Recipient List(Loop Data) Config" dialog box

If the Recipient List is saved in a file, you can select "Excel or CSV file" as the data source and then choose the Excel or CSV file. The system will import the content of the file into the task configuration.

Select "Excel or CSV file" as the data source

If the Recipient List is saved in an Excel file, the list data should be saved in the first sheet of the file.

The first row of the table is the header row, and the data rows start from the second row. Column headers must not be empty, and column names must not be duplicated.

Recipient List in an Excel file

Note: If the Recipient List is stored in a file, after you modify the data in the file, you need to re-import the updated file in the task configuration.

If the Recipient List is stored in a database table, you need to select the data source where this table is located and enter the SQL query used to retrieve the Recipient List. After entering the SQL query, click the "Test SQL" button. The system will execute the SQL query and retrieve the field information returned by the query.

Configure the task to read the Recipient List from the database during runtime

4 Modifying Query Statements of the Task

After setting up the Recipient List, you need to modify the query statements to query data individually for each recipient. For example, consider the following SQL statement:

SELECT *
FROM   sales_stat
WHERE  city = 'Birmingham' --Query sales reports for a specified region
ORDER  BY stat_month; 

In an Information Distribution Task, you need to replace the query conditions in the statement with the variable @@LoopData(Column Name), which uses the field data from the corresponding record in the Recipient List as the query condition. The modified statement is as follows:

SELECT *
FROM   sales_stat
WHERE  city = #[@@LoopData(City)]#  --Query sales reports for a specified region,use the column "City" from the Recipient List as query criteria.
ORDER  BY stat_month; 

In the modified statement, use @@LoopData(City) to get the data from the City column of the corresponding record in the Recipient List as the query parameter in the SQL statement, thus querying data only for a specific recipient.

Using Variables in Tasks, Using Variables in SQL Statements, Using JDBC Variables

5 Modifying the Execution Context of SQL Commands

If SQL commands need to be executed in the sub-task instances, you must modify the "Execution Context" of the SQL commands to "Execute the command in the sub-tasks generated by the task" or "Execute the command in the task itself and its generated sub-tasks." This way, the SQL commands will be executed in the sub-task instances.

For example, if we need to verify in a sub-task instance whether there are sales report data available for a specific region, and if not, abort the current sub-task instance without affecting the execution of other region's sub-task instances, we can use the following SQL command:

Execute SQL Command in sub-task instance

6 Using @@LoopData as the Recipient of the Task

To achieve independent sending of emails, you need to use @@LoopData as the recipient in the task. For example, if the "Manager Email" column in the Recipient List contains the recipient's email address, then the task's recipient should be changed to "@@LoopData(Manager Email)", like this:

Set "@@LoopData(Manager Email)" as the recipients for the task

Set Recipients for Information Distribution Task

7 Considerations for Using Information Distribution Task

(1) After a sub-task instance completes its execution, the email it generates will be sent immediately by the system, without waiting for all sub-task instances to finish.

(2) You can enable the option "Emails generated by this task must be reviewed by me before sending," so that the emails generated will need your approval before being sent.

(3) If the Recipient List's data source is "Excel or CSV file", and you imported an empty Excel or CSV file (containing only header rows without any data rows) when configuring the task, you will need to manually start this task. During task startup, you must import a file containing the Recipient List data. The format of the imported file should match the template file of the Recipient List used during task configuration. Start Tasks Manually