Sequence Generator Transformation Properties
Configure transformation properties that the Integration Service uses to generate sequential values.
The following table lists the properties that you configure for a sequence data object and a new sequence:
Property | Description |
|---|
Start Value | Start value of the generated sequence that you want the Integration Service to use if you use the Cycle option. If you select Cycle, the Integration Service cycles back to this value when it reaches the end value. Default is 0. Maximum value is 9,223,372,036,854,775,806. |
End Value | Maximum value that the Integration Service generates. If the Integration Service reaches this value during the session and the sequence is not configured to cycle, the session fails. Maximum value is 9,223,372,036,854,775,807. |
Increment Value | Difference between two consecutive values from the NEXTVAL port. Default is 1. Must be a positive integer. Maximum value is 2,147,483,647. |
Cycle | If enabled, the Integration Service cycles through the sequence range and starts over with the start value. If disabled, the Integration Service stops the sequence at the configured end value. The Integration Service fails the session with overflow errors if it reaches the end value and still has rows to process. |
Reset | If enabled, the Integration service resets the sequence data object to the start value when the mapping run completes. If disabled, the Integration Service increments the current value after the mapping run completes, and uses that value in the next mapping run. This property is disabled for reusable Sequence Generator transformations and for non-reusable Sequence Generator transformations that use a reusable sequence data object. |
Tracing Level | Level of detail about the transformation that the Integration Service writes into the mapping log. You can choose terse, normal, verbose initialization, or verbose data. Default is normal. |
Maintain Row Order | Maintain the row order of the input data to the transformation. Select this option if the Integration Service should not perform any optimization that can change the row order. Default is false. |
Start Value
Use Cycle to generate a repeating sequence, such as numbers 1 through 12 to correspond to the months in a year.
1. Enter the lowest value in the sequence that you want the Integration Service to use for the start value.
2. Enter the highest value to be used for End Value.
3. Select Cycle.
As it cycles, the Integration Service reaches the configured end value for the sequence, it wraps around and starts the cycle again, beginning with the configured start value.
End Value
The end value is the maximum value that you want the Integration Service to generate. If the Integration Service reaches the end value and the sequence senerator is not configured to cycle through the sequence, the mapping run fails with an overflow error.
Set the end value to any integer between 1 and 9,233,372,036,854,775,807. If you connect the NEXTVAL port to a downstream integer port, set the end value to a value no larger than the integer maximum value. For example, if you connect the NEXTVAL port to a Small Integer port, set the end value to a maximum of 32,767. If the NEXTVAL exceeds the datatype maximum value for the downstream port, the mapping fails.
Increment Value
The Integration Service generates a sequence in the NEXTVAL port based on the Current Value and Increment By properties in the Sequence Generator transformation.
The Current Value property is the value at which the Integration Service starts creating the sequence for each session. Increment By is the integer the Integration Service adds to the existing value to create the new value in the sequence. By default, the Current Value is set to 1, and Increment By is set to 1.
For example, you might create a Sequence Generator transformation with a current value of 1,000 and an increment of 10. If you pass three rows through the mapping, the Integration Service generates the following set of values:
1000
1010
1020
Cycle Through a Range of Values
You can establish a range of values for the Sequence Generator transformation. If you use the cycle option, the Sequence Generator transformation repeats the range when it reaches the end value.
For example, if you set the sequence range to start at 10 and end at 50, and you set an increment value of 10, the Sequence Generator transformation creates the values 10, 20, 30, 40, 50. The sequence starts over again at 10.
Reset
If you configure a non-reusable Sequence Generator transformation to use the reset property, the Integration Service uses the original start value for each mapping run. Otherwise, the Integration Service increments the current value and uses that value in the next mapping run.
For example, you configure a Sequence Generator transformation to create values from 1 to 1,000 with an increment of 1, a start value of 1, and choose reset. During the first mapping run, the Integration Service generates numbers 1 through 234. In each subsequent mapping run, the Integration Service again generates numbers beginning with the start value of 1.
If you do not reset, the Integration Service updates the current value to 235 at the end of the first run. The next time it uses the Sequence Generator transformation, the first value generated is 235.
Maintain Row Order
Maintain the row order of the input data to the transformation. Select this option if the Integration Service should not perform any optimization that can change the row order.
When the Integration Service performs optimizations, it might lose an order established earlier in the mapping. You can establish order in a mapping with a sorted flat file source, a sorted relational source, or a Sorter transformation. When you configure a transformation to maintain row order, the Integration Service considers this configuration when it performs optimizations for the mapping. The Integration Service performs optimizations for the transformation if it can maintain the order. The Integration Service does not perform optimizations for the transformation if the optimization would change the row order.