A new dataset will be required to check the condition or conditions. A script will need to be added to that Dataset to trigger the Schedule if a certain value is returned.
For this topic, we’ll refer the this dataset as the “Exception Dataset”.
The Exception Dataset can be as simple as a query to check a condition and return the value “Error” if the condition isn’t as expected. A script is then added to the dataset to check for that error and execute a specific Schedule if found.
Dataset Caching – Checking the Condition Regularly
The dataset can be scheduled to check for the condition every “N” minutes using the Cache tab. More details on caching here.
If the dataset is set to have time-out period of 10 minutes, then the query to check whether the condition exists will run every 10 minutes. The “Cache Start” and “Cache End Time” values can be used to set the window that the checks will occur.
Avoid Multiple Emails on same condition
If multiple executions of the exception schedule is not desired, keeping a log-table can help ensure that, for example, only 1 alert is sent per day.
This example shows a dataset that is running against a MS SQL DB and:
- Checks a table storing the latest Stored Procedure execution logs
- If an error is found, a result is returned that will trigger an email. The current date is stored in a tracking table to show that an email alert was sent today
- If an error is found on next execution, but an email as already gone out on that day, then the error is ignored.
- Only 1 row and 1 field is returned. The field is called “RESULT” and will contain a message about whether an error was encountered or not. You could return multiple rows if you want multiple schedules to be run. To do this you’d include a SCHEDULE_ID field and return a row for each schedule you need executed. The SCHEDULE_ID could then be used to call separate schedules in the script described here.
DECLARE @ERRORS INT, @EMAIL_TODAY INT --Simple check of a table containing logs and errors of nightly tasks --This could be any query checking for any result such as sales < expected or similar. SET @ERRORS = (SELECT COUNT(*) FROM LGX_LATEST_JOB_LOGS WHERE ERROR_MSG IS NOT NULL) --Has email been sent today? --Check a LGX_ERROR_EMAIL_DATES table containing a SENT_DATE field to log whether email sent today already or not. Prevents multiple emails if desired SET @EMAIL_TODAY = (SELECT COUNT(*) FROM LGX_ERROR_EMAIL_DATES WHERE DATEADD(dd, DATEDIFF(dd, 0, SENT_DATE), 0) = DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)) --If there are errors, but no email has yet been sent, return the Error string which is checked in the script and will execute the schedule immediately IF @ERRORS > 0 AND @EMAIL_TODAY = 0 BEGIN --If errors, log the date in the tracking table. INSERT INTO LGX_ERROR_EMAIL_DATES VALUES (DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) --This is the value the script will execute the schedule on SELECT 'Error Found in Query' AS RESULT END --If errors, but email already sent, just return error and take no action ELSE IF @ERRORS > 0 AND @EMAIL_TODAY > 0 SELECT 'Errors, Email Sent Today Already' AS RESULT ELSE --No Errors, no action SELECT 'No Errors' AS RESULT --Optional to remove old dates from email tracking table DELETE FROM LGX_ERROR_EMAIL_DATES WHERE DATEADD(dd, DATEDIFF(dd, 0, SENT_DATE), 0) <> DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)