Reusable VS Non Reusable & Properties of Sequence Generator Transformation

  • Updated
  • Posted in Programming
  • 5 mins read


We will see the distinction of reusable and non reusable sequence generator transformation together with the properties of the transformation.

Sequence Generator Transformation Properties:

You should configure the next properties of a sequence generator transformation:

Start Value:

Specify the Start Value while you configure the sequence generator transformation for Cycle possibility. If you configure the cycle, the combination service cycles again to this worth when it reaches the End Value. Use Cycle to generate a repeating sequence numbers, comparable to numbers 1 by means of 12 to correspond to the months in a yr. To cycle the combination service by means of a sequence:

  • Enter the bottom worth within the sequence to make use of for the Start Value.
  • Enter the very best worth for use for End Value.
  • Select Cycle possibility.

Increment By:

The Integration service generates sequence numbers based mostly on the Current Value and the Increment By properties within the sequence generator transformation. Increment By is the integer the combination service provides to the prevailing worth to create the brand new worth within the sequence. The default worth of Increment By is 1.

End Value:

End worth is the utmost worth that the combination service generates. If the combination service reaches the tip worth and the sequence generator isn’t configured for cycle possibility, then the session fails with the next error message:

TT_11009 Sequence Generator Transformation: Overflow error.

If the sequence generator is configured for cycle possibility, then the combination service cycles again to the beginning worth and begins producing numbers from there.

Current Value:

The integration service makes use of the Current Value as the premise for generated values for every session. Specify the worth in “Current Value” you need the combination service as a beginning worth to generate sequence numbers. If you need to cycle by means of a sequence of numbers, then the present worth should be larger than or equal to the Start Value and fewer than the End Value.

At the tip of the session, the combination service updates the present worth to the final generated sequence quantity plus the Increment By worth within the repository if the sequence generator Number of Cached Values is 0. When you open the mapping after a session run, the present worth shows the final sequence worth generated plus the Increment By worth.

Reset:

The reset possibility is relevant just for non reusable sequence generator transformation and it’s disabled for reusable sequence generator. If you choose the Reset possibility, the combination service based mostly on the unique present worth every time it begins the session. Otherwise the combination service updates the present worth within the repository with final worth generated plus the increment By worth.

Number of Cached Values:

The Number of Cached Values signifies the quantity of values that the combination service caches at one time. When this worth is configured larger than zero, then the combination service caches the required quantity of values and updates the present worth within the repository.

Non Reusable Sequence Generator:

The default worth of Number of Cached Values is zero for non reusable sequence mills. It means the combination service doesn’t cache the values. The integration service, accesses the Current Value from the repository at the beginning of the session, generates the sequence numbers, after which updates the present worth on the finish of the session.

When you set the quantity of cached values larger than zero, the combination service caches the required quantity of cached values and updates the present worth within the repository. Once the cached values are used, then the combination service once more accesses the present worth from repository, caches the values and updates the repository. At the tip of the session, the combination service discards any unused cached values.

For non-reusable sequence generator setting the Number of Cached Values larger than zero can improve the quantity of instances the Integration Service accesses the repository throughout the session. And additionally discards unused cache values on the finish of the session.
As an instance while you set the Number of Cached Values to 100 and also you need to course of solely 70 information in a session. The integration service first caches 100 values and updates the present worth with 101. As there are solely 70 rows to be processed, solely the primary 70 sequence quantity will probably be used and the remaining 30 sequence numbers will probably be discarded. In the subsequent run the sequence numbers begins from 101.

The drawback of having Number of Cached Values larger than zero are: 1) Accessing the repository a number of instances throughout the session. 2) Discarding of unused cached values, inflicting discontinuous sequence numbers

Reusable Sequence Generators:

The default worth of Number of Cached Values is 100 for reusable sequence mills. When you might be utilizing the reusable sequence generator in a number of classes which run in parallel, then specify the Number of Cache Values larger than zero. This will keep away from producing the identical sequence numbers in a number of classes.

If you improve the Number of Cached Values for reusable sequence generator transformation, the quantity of calls to the repository decreases. However there’s likelihood of having extremely discarded values. So, select the Number of Cached values correctly.

Recommended Reading:

Sequence Generator Transformation

Leave a Reply