Scheduled Runs

Batches are used to execute multiple sheets, directories, and transformations

  • Batch folder
  • Batches are not deployed

Elements

List of elements to execute (directory, worksheet, transformation) Priority Start Mode Normal / Skipped / Halted

Scheduling

When, frequency

Notifications

When a time-triggered batch is executed, the user can be informed by email about the execution status.

The following events regarding the run status can be set:

Name Meaning
On Start Message is sent when the run starts
On Retry Message is sent when the scheduler automatically restarts
On Error Message is sent when the run enters an error state
On Skip Message is sent when an action is skipped
On Hold Message is sent when the run is halted
On Success Message is sent when the run has been completed successfully
On Action Error Currently not implemented

The format of the sent messages is specified via a Freemarker template and can be customized by the customer.

There is one template for the subject and one for the message content. The templates are stored in the table vw_sqts_conig_global:

Type config_key1 config_key2 config_key3 config_key4
Subject SCHEDULER EMAIL TEMPLATE Mail_Header
Content SCHEDULER EMAIL TEMPLATE Mail_Content

The following variables are made available in Freemarker:

Variable Data Type Meaning
batchName String The batch name
eventType String See events for run status
instance String The environment name. Determined from vv_sqts_config_global ("SCHEDULER", "TRANSFORMATION", "INSTANCE_NAME")
planned Timestamp Planned time of the batch
when String Effective start time of the batch
resultsFromData List List of objects from the batch

The list of objects from the batch contains all entries from Actions that were not successful on the first attempt. This list is sorted ascending by action ID and descending by start time.

Each object has a list of names and their values. The following names are available:

Name Data Type Meaning
sqtsActionId Number The action ID
sqtsQueueId Number The associated queue entry ID
sqtsDiagramGroupName String The sheet name
actionName String The action name
status Char The status code of the entry
executionMode Char N=Normal, K = Skipped
insertUser String The name of the user who created the queue entry (initial datasqill)
startDt Timestamp Action start time
endDt Timestamp Action end time
errorCode String Error number or 0 if no error occurred
statusText String Error text

A subject could look like this:

[#if eventType == 'ON START']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: started
[#elseif eventType == 'ON ACTION ERROR']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: ignored
[#elseif eventType == 'ON SKIP']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: skipped
[#elseif eventType == 'ON SUCCESS']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: finished
[#elseif eventType == 'ON ERROR']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: ERROR
[#elseif eventType == 'ON HOLD']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: Halted
[#elseif eventType == 'ON RETRY']
Batch ${batchName}${instance?has_content?then("@" + instance, "")}: Restarted

A simple email content could look like this:

[#assign statusCodes = { "E": "Error", "F": "Finished", "I": "Interupted", "H": "Halted" }]
[#if eventType == 'ON START']
Batch started, Planned start date: ${planned?string("dd.MM.yyyy HH:mm:ss")}. Effective start date: ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON SKIP']
Batch skipped at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON SUCCESS']
Batch successfully completed at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON ERROR']
Batch terminated with ERROR at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON HOLD']
Batch went in halted state at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[#elseif eventType == 'ON RETRY']
Batch was automatically restarted at ${when?string("dd.MM.yyyy HH:mm:ss")}.
[/#if]
[#assign lastId = 0]
[#-- loop over entries--]
[#list resultsFromData as item]
  [#-- Check if new group starts --]
  [#if item.sqtsActionId != lastId]
    [#-- New group: output blank line, ID and name --]
    [#if item?index != 0]

    [/#if]
${item.sqtsDiagramGroupName}/${item.actionName}(${item.sqtsActionId?c})
    [#assign lastId = item.sqtsActionId]
  [/#if]
  ${item.startDt?string("dd.MM.yyyy HH:mm:ss")} - ${item.endDt?string("dd.MM.yyyy HH:mm:ss")} State: "${statusCodes[item.status]}" [#if item.errorCode = "0"][#if item.executionMode = "K"]Skipped[#else]Solved[/#if] by ${item.insertUser}[#else]Inserted by ${item.insertUser}
    Error: ${item.errorCode}[/#if] ${item["statusText"]!" "?replace("\n", "    \n")}
[/#list]

Batch Dependencies

When editing batches, there is a Batch Dependencies window at the bottom right. Here you define which batches (Dependent From) this batch depends on, or in other words, which batches this batch should react to, or not start or abort.

There are four different reasons when a batch can influence the start of this batch:

  • If Scheduled: When the Dependent From batch is supposed to start at the same time (Next Run date) or earlier but has not yet started
  • If Running: When there is currently a running instance of the Dependent From batch
  • If Error: When there is currently an instance of the Dependent From batch in an error state
  • If Missing: When the last execution of the Dependent From batch is smaller than the planned execution (Next run date) of this batch

Activities in case of one of the conditions

  • Wait: This batch waits until this dependency no longer exists
  • Start: This batch can start from the perspective of this dependency
  • Skip: This batch should no longer be executed

A batch waits when a condition of the dependent batches results in Wait A batch is skipped when a condition of the dependent batches results in Skip A batch is started when all conditions in all dependent batches result in Start

Self-dependency A batch can also be made dependent on itself. In this case, the If Scheduled and If Missing conditions are ignored.