Powered By Blogger

Wednesday, June 29, 2011

Workflow Variables

Workflow Variables Overview:

Bookmark Facebook Twitter Google Delicious Digg StumbleUpon More Bookmarks

You can create and use variables in a workflow to reference values and record information. For example, use a Variable in a Decision task to determine whether the previous task ran properly. If it did, you can run the next task.

If not, you can stop the workflow. Use the following types of workflow variables:

  • Predefined workflow variables. The Workflow Manager provides predefined workflow variables for tasks within a workflow.
  • User-defined workflow variables. You create user-defined workflow variables when you create a workflow. Use workflow variables when you configure the following types of tasks:
  • Assignment tasks. Use an Assignment task to assign a value to a user-defined workflow variable. For Example, you can increment a user-defined counter variable by setting the variable to its current value plus 1.
  • Decision tasks. Decision tasks determine how the Integration Service runs a workflow. For example, use the Status variable to run a second session only if the first session completes successfully.
  • Links. Links connect each workflow task. Use workflow variables in links to create branches in the workflow. For example, after a Decision task, you can create one link to follow when the decision condition evaluates to true, and another link to follow when the decision condition evaluates to false.
  • Timer tasks. Timer tasks specify when the Integration Service begins to run the next task in the workflow. Use a user-defined date/time variable to specify the time the Integration Service starts to run the next task.

Use the following keywords to write expressions for user-defined and predefined workflow variables:

  • AND
  • OR
  • NOT
  • TRUE
  • FALSE
  • NULL
  • SYSDATE

Predefined Workflow Variables:

Each workflow contains a set of predefined variables that you use to evaluate workflow and task conditions. Use the following types of predefined variables:

  • Task-specific variables. The Workflow Manager provides a set of task-specific variables for each task in the workflow. Use task-specific variables in a link condition to control the path the Integration Service takes when running the workflow. The Workflow Manager lists task-specific variables under the task name in the Expression Editor.
  • Built-in variables. Use built-in variables in a workflow to return run-time or system information such as folder name, Integration Service Name, system date, or workflow start time. The Workflow Manager lists built-in variables under the Built-in node in the Expression Editor.

Task-Specific
Variables

DescriptionTask TypesData type
Condition

Evaluation result of decision condition expression.
If the task fails, the Workflow Manager keeps the condition set to null.


Sample syntax:
$Dec_TaskStatus.Condition =

DecisionInteger
End Time

Date and time the associated task ended. Precision is to the second.
Sample syntax:
$s_item_summary.EndTime > TO_DATE('11/10/2004
08:13:25')

All tasksDate/Time
ErrorCode

Last error code for the associated task. If there is no error, the Integration Service sets ErrorCode to 0 when the task completes.
Sample syntax:
$s_item_summary.ErrorCode = 24013.
Note: You might use this variable when a task consistently fails with this final error message.

All tasksInteger
ErrorMsg

Last error message for the associated task.If there is no error, the Integration Service sets ErrorMsg to an empty string when the task completes.
Sample syntax:
$s_item_summary.ErrorMsg = 'PETL_24013 Session run
completed with failure
Variables of type Nstring can have a maximum length of 600 characters.
Note: You might use this variable when a task consistently fails
with this final error message.

All tasksNstring
First Error Code

Error code for the first error message in the session.
If there is no error, the Integration Service sets FirstErrorCode to 0
when the session completes.
Sample syntax:
$s_item_summary.FirstErrorCode = 7086

SessionInteger
FirstErrorMsg

First error message in the session.If there is no error, the Integration Service sets FirstErrorMsg to an empty string when the task completes.
Sample syntax:
$s_item_summary.FirstErrorMsg = 'TE_7086 Tscrubber:
Debug info… Failed to evalWrapUp'Variables of type Nstring can have a maximum length of 600 characters.

SessionNstring
PrevTaskStatus

Status of the previous task in the workflow that the Integration Service ran. Statuses include:
1.ABORTED
2.FAILED
3.STOPPED
4.SUCCEEDED
Use these key words when writing expressions to evaluate the status of the previous task.
Sample syntax:
$Dec_TaskStatus.PrevTaskStatus = FAILED

All TasksInteger
SrcFailedRows

Total number of rows the Integration Service failed to read from the source.
Sample syntax:
$s_dist_loc.SrcFailedRows = 0

SessionInteger
SrcSuccessRows

Total number of rows successfully read from the sources.
Sample syntax:
$s_dist_loc.SrcSuccessRows > 2500

SessionInteger
StartTime

Date and time the associated task started. Precision is to the second.
Sample syntax:
$s_item_summary.StartTime > TO_DATE('11/10/2004
08:13:25')

All TaskDate/Time
Status

Status of the previous task in the workflow. Statuses include:
- ABORTED
- DISABLED
- FAILED
- NOTSTARTED
- STARTED
- STOPPED
- SUCCEEDED
Use these key words when writing expressions to evaluate the status of the current task.
Sample syntax:
$s_dist_loc.Status = SUCCEEDED

All TaskInteger
TgtFailedRows

Total number of rows the Integration Service failed to write to the target.
Sample syntax:
$s_dist_loc.TgtFailedRows = 0

SessionInteger
TgtSuccessRows

Total number of rows successfully written to the target.
Sample syntax:
$s_dist_loc.TgtSuccessRows > 0

SessionInteger
TotalTransErrors

Total number of transformation errors.
Sample syntax:
$s_dist_loc.TotalTransErrors = 5

SessionInteger

User-Defined Workflow Variables:

You can create variables within a workflow. When you create a variable in a workflow, it is valid only in that workflow. Use the variable in tasks within that workflow. You can edit and delete user-defined workflow variables.

Use user-defined variables when you need to make a workflow decision based on criteria you specify. For example, you create a workflow to load data to an orders database nightly. You also need to load a subset of this data to headquarters periodically, every tenth time you update the local orders database. Create separate sessions to update the local database and the one at headquarters.

clip_image002

Use a user-defined variable to determine when to run the session that updates the orders database at headquarters.

To configure user-defined workflow variables, complete the following steps:

1. Create a persistent workflow variable, $$WorkflowCount, to represent the number of times the workflow has run.

2. Add a Start task and both sessions to the workflow.

3. Place a Decision task after the session that updates the local orders database.Set up the decision condition to check to see if the number of workflow runs is evenly divisible by 10. Use the modulus (MOD) function to do this.

4. Create an Assignment task to increment the $$WorkflowCount variable by one.

5. Link the Decision task to the session that updates the database at headquarters when the decision condition evaluates to true. Link it to the Assignment task when the decision condition evaluates to false. When you configure workflow variables using conditions, the session that updates the local database runs every time the workflow runs. The session that updates the database at headquarters runs every 10th time the workflow runs.

Creating User-Defined Workflow Variables :

You can create workflow variables for a workflow in the workflow properties.

To create a workflow variable:

1. In the Workflow Designer, create a new workflow or edit an existing one.

2. Select the Variables tab.

3. Click Add.

4. Enter the information in the following table and click OK:

FieldDescription
Name

Variable name. The correct format is $$VariableName. Workflow variable names are not case sensitive.
Do not use a single dollar sign ($) for a user-defined workflow variable. The single dollar sign
is reserved for predefined workflow variables

Data type

Data type of the variable. You can select from the following data types:
- Date/Time
- Double
- Integer
- Nstring

Persistent

Whether the variable is persistent. Enable this option if you want the value of the variable
retained from one execution of the workflow to the next.

Default Value

Default value of the variable. The Integration Service uses this value for the variable during
sessions if you do not set a value for the variable in the parameter file and there is no value
stored in the repository.
Variables of type Date/Time can have the following formats:
- MM/DD/RR
- MM/DD/YYYY
- MM/DD/RR HH24:MI
- MM/DD/YYYY HH24:MI
- MM/DD/RR HH24:MI:SS
- MM/DD/YYYY HH24:MI:SS
- MM/DD/RR HH24:MI:SS.MS
- MM/DD/YYYY HH24:MI:SS.MS
- MM/DD/RR HH24:MI:SS.US
- MM/DD/YYYY HH24:MI:SS.US
- MM/DD/RR HH24:MI:SS.NS
- MM/DD/YYYY HH24:MI:SS.NS
You can use the following separators: dash (-), slash (/), backslash (\), colon (:), period (.), and
space. The Integration Service ignores extra spaces. You cannot use one- or three-digit values
for year or the “HH12” format for hour.
Variables of type Nstring can have a maximum length of 600 characters.

Is NullWhether the default value of the variable is null. If the default value is null, enable this option.
DescriptionDescription associated with the variable.

5. To validate the default value of the new workflow variable, click the Validate button.

6. Click Apply to save the new workflow variable.

7. Click OK.


No comments: