You can configure jobs to use alternative work flows that handle exceptions and include built-in recovery mechanisms. This technique allows you to automate the recovery process.
In this example, the main data flow uses default settings to load a target table.
If an error occurs, the job records the failure. After you resolve the issue, you can execute the job again.
The next execution then uses a different data flow with the auto-correct load option enabled. This allows the data flow to complete successfully, even if some rows were already committed during the first attempt.
The successful execution is recorded so that the next job run uses the default data flow.
Alternative Work Flow Details
Alternative work flows consist of several components, as shown in the previous figure.
Alternative Work Flow Components
| 1 | "Get Last Execution Status" Script | A script to determine if recovery is required.
This script reads a value from a status table and populates a global variable. The initial value is set to indicate that recovery is not required. |
| 2 | Conditional | A conditional that triggers the appropriate work flow based on the recovery status.
The conditional uses an If/Then/Else statement to route the process: work flows that do not require recovery are processed one way, while those that do require recovery are processed another way. |
| 3 | Standard Execution | A work flow with a try/catch block for standard execution.
The data flow for standard execution is configured without the auto-correct load option. This ensures that the data flow runs in a less resource-intensive mode whenever possible. |
| 4 | Exception Script | A script within the catch object to update the status table.
This script flags that recovery is required if any exceptions occur during execution. |
| 5 | Recovery Execution | A work flow for recovery execution and a script to update the status table.
The data flow is configured for more resource-intensive processing to resolve exceptions. The script then updates the status table to indicate that recovery is no longer required. |
Conditionals
Conditionals are single-use objects that implement logic within a work flow.
When a conditional is defined, you specify a condition and two logical branches:
| Statement | Description |
|---|---|
| If | A Boolean expression that evaluates to TRUE or FALSE. You can use functions, variables, and standard operators to construct the expression. |
| Then | The work flow element to execute if the IF expression evaluates to TRUE. |
| Else | The work flow element to execute if the IF expression evaluates to FALSE. |
Both the Then and Else branches can contain any object valid in a work flow, including other work flows, data flows, nested conditionals, try/catch blocks, and scripts.
Try/Catch Blocks
A try/catch block allows you to define alternative work flows if errors occur during job execution.
These blocks catch specific error classes, apply provided solutions, and allow execution to continue.
You can include multiple catch operations in the same block to handle different error types.
For each catch, you must:
- Specify the exception or group of exceptions handled by the catch.
To handle multiple exceptions, add more catches to the try/catch block.
- Specify the work flow to execute if the exception occurs.
You can use an existing work flow or define a new one in the catch editor.
If an exception occurs during the execution of a try/catch block and no catch is configured for that exception, the error is handled by standard error logic.
Summary
In this lesson, you've learned how to design alternative work flows.
- Configure alternative workflows with try/catch blocks to automate error recovery and handle exceptions efficiently.
- Use conditionals with If/Then/Else logic to route processes based on recovery status and execution requirements.
- Implement try/catch blocks to catch specific error classes and execute alternative workflows when exceptions occur.
- Prevent duplicate rows during job restarts using Table Comparison, auto-correct load, or Preload SQL options.