Sequence Generator transformation is used to generate a sequence of unique numbers. Unique values are generated based on the property defined in the Sequence Generator transformation. A sample mapping showing the Sequence Generator transformation is shown in the following screenshot:
As you can see in the mapping, the Sequence Generator transformation does not have any input port. You need to define the start value, increment by value, and end value in the properties. Based on properties, the sequence generator generates the value. In the preceding mapping, as soon as the first record enters the target from the Source Qualifier transformation, NEXTVAL
generates its first value, and so on for other records. The sequence generator is built to generate numbers.
The Sequence Generator transformation has only two ports, NEXTVAL
and CURRVAL
. Both the ports are output ports. You cannot add or delete any port in a sequence generator. It is recommended that you always use the NEXTVAL
port first. If the NEXTVAL
port is utilized, then use the CURRVAL
port. You can define the value of CURRVAL
in the properties of the Sequence Generator transformation.
Consider a scenario where we are passing two records to the transformation. The following events occur inside the Sequence Generator transformation. Also note that in our case, we have defined the start value as 0
, the increment by value as 1
, and the end value is the default in the property. Also, the current value defined in Properties is 1
. The following is the sequence of events:
1
in the Properties of the sequence generator, is assigned to the NEXTVAL
port. This gets loaded into the target by the connected link. So for the first record, SEQUENCE_NO
in the target is given the value of 1
.CURRVAL
internally and assigns that value to the current value, 2 in this case.2
now gets assigned to NEXTVAL
. The sequence generator gets incremented internally to give CURRVAL
a value of 3
.So at the end of the processing of record 2
, the NEXTVAL
port will have a value of 2
and the CURRVAL
port will have its value set as 3
. This is how the cycle keeps on running till you reach the end of the records from the source.
It is slightly confusing to understand how the NEXTVAL
and CURRVAL
ports behave, but after reading the given example, you will have a proper understanding of the process.
There are multiple values that you need to define inside the Sequence Generator transformation. Double-click on the sequence generator and click on the Properties tab, as shown in the following screenshot:
Let's discuss the properties in detail:
The default value is 0 and the maximum value is 9223372036854775806.
NEXTVAL
port.The default value is 1 and the maximum value is 2147483647.
9223372036854775807
.CURRVAL
port. Specify the current value that you wish to have as the value for the first record. As mentioned earlier, the CURRVAL
port gets assigned to NEXTVAL
, and the CURRVAL
port is incremented.The CURRVAL
port stores the value after the session is over, and when you run the session the next time, it starts incrementing the value from the stored value if you have not checked the reset option. If you check the reset option, Integration Services resets the value to 1
. Suppose you have not checked the Reset option and you have passed 17 records at the end of the session; then, the current value will be set to 18
, which will be stored internally. When you run the session the next time, it starts generating the value from 18
.
The maximum value is 9223372036854775807
.
If your source records are more than the end value defined, the session will fail with an overflow error.
The default value for nonreusable transformations is 0
. The default value for reusable transformations is 1000
. The maximum value is 9223372036854775807
.
With this, we have seen all the properties of the Sequence Generator transformation.
Let's talk about the usage of the Sequence Generator transformation:
NEXTVAL
port to the targets for which you wish to generate the primary and foreign key, as shown in the following screenshot:JOB_ID
of an employee. Some records do not have JOB_ID
in the table. Use the following function to replace these missing values. Make sure you are not generating NEXTVAL
in a manner similar to existing JOB_ID
in the data:IIF( ISNULL (JOB_ID), NEXTVAL, JOB_ID)
The preceding function interprets whether JOB_ID
is null and then assigns NEXTVAL
, otherwise it keeps JOB_ID
as it is. The following screenshot indicates these requirements:
With this, we have learned all the options available in the Sequence Generator transformation. Next, we will talk about Joiner transformations.