Sequence Generator Transformation Properties:
You have to configure the following properties of a sequence generator transformation:
Specify the Start Value when you configure the sequence generator transformation for Cycle option. If you configure the cycle, the integration service cycles back to this value when it reaches the End Value. Use Cycle to generate a repeating sequence numbers, such as numbers 1 through 12 to correspond to the months in a year. To cycle the integration service through a sequence:
The Integration service generates sequence numbers based on the Current Value and the Increment By properties in the sequence generator transformation. Increment By is the integer the integration service adds to the existing value to create the new value in the sequence. The default value of Increment By is 1.
End value is the maximum value that the integration service generates. If the integration service reaches the end value and the sequence generator is not configured for cycle option, then the session fails with the following error message:
TT_11009 Sequence Generator Transformation: Overflow error.
If the sequence generator is configured for cycle option, then the integration service cycles back to the start value and starts generating numbers from there.
The integration service uses the Current Value as the basis for generated values for each session. Specify the value in "Current Value" you want the integration service as a starting value to generate sequence numbers. If you want to cycle through a sequence of numbers, then the current value must be greater than or equal to the Start Value and less than the End Value.
At the end of the session, the integration service updates the current value to the last generated sequence number plus the Increment By value in the repository if the sequence generator Number of Cached Values is 0. When you open the mapping after a session run, the current value displays the last sequence value generated plus the Increment By value.
The reset option is applicable only for non reusable sequence generator transformation and it is disabled for reusable sequence generator. If you select the Reset option, the integration service based on the original current value each time it starts the session. Otherwise the integration service updates the current value in the repository with last value generated plus the increment By value.
Number of Cached Values:
The Number of Cached Values indicates the number of values that the integration service caches at one time. When this value is configured greater than zero, then the integration service caches the specified number of values and updates the current value in the repository.
Non Reusable Sequence Generator:
The default value of Number of Cached Values is zero for non reusable sequence generators. It means the integration service does not cache the values. The integration service, accesses the Current Value from the repository at the start of the session, generates the sequence numbers, and then updates the current value at the end of the session.
When you set the number of cached values greater than zero, the integration service caches the specified number of cached values and updates the current value in the repository. Once the cached values are used, then the integration service again accesses the current value from repository, caches the values and updates the repository. At the end of the session, the integration service discards any unused cached values.
For non-reusable sequence generator setting the Number of Cached Values greater than zero can increase the number of times the Integration Service accesses the repository during the session. And also discards unused cache values at the end of the session.
As an example when you set the Number of Cached Values to 100 and you want to process only 70 records in a session. The integration service first caches 100 values and updates the current value with 101. As there are only 70 rows to be processed, only the first 70 sequence number will be used and the remaining 30 sequence numbers will be discarded. In the next run the sequence numbers starts from 101.
The disadvantage of having Number of Cached Values greater than zero are: 1) Accessing the repository multiple times during the session. 2) Discarding of unused cached values, causing discontinuous sequence numbers
Reusable Sequence Generators:
The default value of Number of Cached Values is 100 for reusable sequence generators. When you are using the reusable sequence generator in multiple sessions which run in parallel, then specify the Number of Cache Values greater than zero. This will avoid generating the same sequence numbers in multiple sessions.
If you increase the Number of Cached Values for reusable sequence generator transformation, the number of calls to the repository decreases. However there is chance of having highly discarded values. So, choose the Number of Cached values wisely.