You are here:

Flow Control Tab

Flow control is the ability to take one source row and conditionally insert, update, or delete records into one or more target data objects. In conjunction with the seek operation, the Flow Control tab allows you to specify step processing based on the results of previous steps.

Using the Flow Control tab, you can design data transaction jobs that only execute some of the steps, jump over steps, or stop in the middle of a set of steps.

Note:

This feature requires special handling when working with bulk operations. See Using Bulk Operations for more information.

To Change The Flow For A Step

Select a step from the Step Order pane.
Select the outcome you want to change from the flow control table. The current flow for that outcome appears in the outcome processing section.
Make changes to the flow as needed.
If desired, select another outcome for this step or another step.

The Flow Control tab contains the Flow Control table, and, for each outcome, an Outcome processing section.

Flow Control Table

The flow control table displays the current configuration for each step. Click a step and its flow control settings display in the table.

Depending on the operation, each step has three or four possible outcomes:

Outcome	Definition
Failure	Failure: the step has failed. Either a database failure occurred (invalid foreign key, required field missing, duplicate value in a unique column, etc) or the row failed during adapter pre- or post-processing.
Success (0)	Success: no rows affected. There were no failures, but the operation affected or found no rows. This can occur when updates, deletes and seeks have lookup links that match nothing in the target.
Success (1)	Success: one row affected. Operation succeeded and affected or found one row.
Success (>1)	Success: more than one row affected. This outcome only appears when multirecord updates are valid. Operation succeeded and affected or found more than one row. Occurs most frequently with updates, deletes or seeks. If multirecord operations are blocked at a database level, then you should never see this outcome for updates or deletes. Instead, these multirecord operations are get trapped and returned as failures.

Outcome Processing

This section allows you to determine how to process each outcome (as shown in the flow control table) for this step.For each outcome, you can configure the following:

Action Drop-Down List

For each outcome, you can select one of the following actions:

Continue — Execute the next step (or stop processing for this source row if this is the last step)
Goto Next Row — Stop processing steps for the current source row, go to the next source row and continue processing
Goto Step — Jump to the specified step number (you can only jump ahead, no looping allowed)
End Job — Stop processing steps for this source row and all subsequent or remaining source rows

Error Logging

To log an error for an action, select the Log an Error checkbox to enable error text and then enter the error text in the text box. If you choose to log an error:

An error is logged in the Transaction Error log
If rejected rows are being recorded, the current source row is written to the rejected row table
If source rows are being updated or deleted, the current source row is left unchanged

If you choose not to log an error when a failure occurs, you are turning a failed step into a success and:

Nothing is written to the transaction error log
The row is not written to the rejected row table (if they’re being recorded)
The source row is updated (if the feature is enabled)

Transaction Processing

For each step outcome, choose one of three options:

Commit the transaction
Rollback the transaction
None (leave the current transaction as is)

Note: The appropriate choice varies depending on the commit level for the DTS file.

Exit Status

If you selected End Job as an outcome, determine how to process the job if execution is stopped by the End Job call:

Success — End the job and record a fatal error indicating that execution was terminated due to End Job Success step control configuration
Retry — For messages, end the job and send the message back into the queue
Failed — End the job and record a fatal error indicating that execution was terminated due to End Job Failed step control configuration
Disable DTS — For messages, end the job and disable processing of this message type

Pre-Operation Step Flow Control Formula

Step control formulas provide a way to conditionally control step execution before each step is executed. The formula created in this section is on the step itself, rather than on a data link or lookup link.

The formula is evaluated before the step is executed. The formula usually has a source reference but is not linked to any target field. The result of the formula should always be either one of the step control functions or TRUE.

For example, the following Pre-Operation Step Control Formula checks if the source field S32 is null, then skips the step if it is. If the source field S32 is not null, the step is executed.

IF( ISERROR( S32 ), SKIPSTEP( ), TRUE ( ))

Note: When you copy a step, its associated links, data formulas, and lookup criteria are also copied. They are accessible on the corresponding tabs.