What are the changes we observe when we promote a non resuable Sequence Generator to a resuable one? And what happens if we set the Number of Cached Values to 0 for a reusable transformation?

Ans. When we convert a non reusable sequence generator to resuable one we observe that the Number of Cached Values is set to 1000 by default; And the Reset property is disabled.
When we try to set the Number of Cached Values property of a Reusable Sequence Generator to 0 in the Transformation Developer we encounter the following error message:
The number of cached values must be greater than zero for reusable sequence transformation.

Suppose we have 100 records coming from the source. Now for a target column population we used a Sequence generator. Suppose the Current Value is 0 and End Value of Sequence generator is set to 80. What will happen?

Ans. End Value is the maximum value the Sequence Generator will generate. After it reaches the End value the session fails with the following error message:
TT_11009 Sequence Generator Transformation: Overflow error.
Failing of session can be handled if the Sequence Generator is configured to Cycle through the sequence, i.e. whenever 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.

Suppose we have a source table populating two target tables. We connect the NEXTVAL port of the Sequence Generator to the surrogate keys of both the target tables. Will the Surrogate keys in both the target tables be same? If not how can we flow the same sequence values in both of them.

Ans. When we connect the NEXTVAL output port of the Sequence Generator directly to the surrogate key columns of the target tables, the Sequence number will not be the same.
A block of sequence numbers is sent to one target tables surrogate key column. The second targets receives a block of sequence numbers from the Sequence Generator transformation only after the first target table receives the block of sequence numbers.
Suppose we have 5 rows coming from the source, so the targets will have the sequence values as TGT1 (1,2,3,4,5) and TGT2 (6,7,8,9,10). [Taken into consideration Start Value 0, Current value 1 and Increment by 1.
Now suppose the requirement is like that we need to have the same surrogate keys in both the targets.
Then the easiest way to handle the situation is to put an Expression Transformation in between the Sequence Generator and the Target tables. The SeqGen will pass unique values to the expression transformation, and then the rows are routed from the expression transformation to the targets.

Define the Properties available in Sequence Generator transformation in brief.

Ans.

Sequence Generator Properties	Description
Start Value	Start value of the generated sequence that we want the Integration Service to use if we use the Cycle option. If we select Cycle, the Integration Service cycles back to this value when it reaches the end value. Default is 0.
Increment By	Difference between two consecutive values from the NEXTVAL port.Default is 1.
End Value	Maximum value generated by SeqGen. After reaching this value the session will fail if the sequence generator is not configured to cycle.Default is 2147483647.
Current Value	Current value of the sequence. Enter the value we want the Integration Service to use as the first value in the sequence. Default is 1.
Cycle	If selected, when 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.
Number of Cached Values	Number of sequential values the Integration Service caches at a time. Default value for a standard Sequence Generator is 0. Default value for a reusable Sequence Generator is 1,000.
Reset	Restarts the sequence at the current value each time a session runs.This option is disabled for reusable Sequence Generator transformations.

What is a Sequence Generator Transformation?

Ans. A Sequence Generator transformation is a Passive and Connected transformation that generates numeric values. It is used to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers. This transformation by default contains ONLY Two OUTPUT ports namely CURRVAL and NEXTVAL. We cannot edit or delete these ports neither we cannot add ports to this unique transformation. We can create approximately two billion unique numeric values with the widest range from 1 to 2147483647.