SEQUENCE GENERATOR TRANSFORMATION
USAGE:
1. SEQUENCE GEN TR is used to Generate Numeric Values.
2. We will use SEQUENCE GEN TR to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
3. The Sequence Generator transformation contains default pass-through fields and two output fields, NEXTVAL and CURRVAL.
Sequence Generator Properties:
• Initial Value: SEQUENCE will always START WITH Initial VALUE. Default is 1.
• Incremented by: SEQUENCE will add the incremented by value for each number. If the SEQUENCE is starting with 1 and if you increment by 2, it will give the numbers as 1 ,3,5,7,9..etc. Default is 1.
• End Value: Maximum value that sequence number can End with the below number. 9223372036854775807.
• Reset: Restarts the sequence at the Initial Value each time a session runs.
• Cycle: Reset to Cycle start value if End value is reached.
• Cycle start value: Always use Cycle start value with cycle option if you don’t use cycle option, Cycle start value will be always ignored. Default is 0.
Current Value: Once a Mapping task completed, you can see the next number where the sequence number starts from the next run by using Current value.
Current value will be available under Sequences, when you edit a Mapping Task.
• You can edit the current value to start the sequence with a specified value.
• Always Current value Takes priority over Initial Value.
• You can Reset both Current value and Initial value under Mapping task.
CURRVAL:
• When you map both CURRVAL and NEXTVAL to the Downstream (Next transformation), CURRVAL will take as NEXTVAL+INCREMENTED BY.
• When you map only CURRVAL, without mapping NEXTVAL to the Downstream transformation, CURRVAL will give the value as in the Current Value.
Disabling Incoming Fields:
• Unlike in PowerCenter, Sequence Generator Transformation can connect in pipeline with both upstream (Before) and downstream (After) transformation shown as below.
• But You can Disable the incoming fields and only connect the sequence generator to downstream transformation from the Advanced properties.
• It looks like below after checking the Disable incoming fields property.
Leave a comment