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:
- When the first record enters the target from the filter transformation, the current value, which is set to
1in the Properties of the sequence generator, is assigned to theNEXTVALport. This gets loaded into the target by the connected link. So for the first record,SEQUENCE_NOin the target is given the value of1. - The sequence generator increments
CURRVALinternally and assigns that value to the current value, 2 in this case. - When the second record enters the target, the current value that is set as
2now gets assigned toNEXTVAL. The sequence generator gets incremented internally to giveCURRVALa value of3.
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:
- Start Value: This comes into the picture only if you select the Cycle option in the properties. Start Value indicates the Integration Service that starts over from this value when the end value is reached after you have checked the cycle option.
The default value is 0 and the maximum value is 9223372036854775806.
- Increment By: This is the value by which you wish to increment the consecutive numbers from the
NEXTVALport.The default value is 1 and the maximum value is 2147483647.
- End Value: This is the maximum value that the Integration Service can generate. If the Sequence Generator reaches the end value and is not configured for the cycle, the session will fail, giving the data overflow error. The maximum value is
9223372036854775807. - Current Value: This indicates the value assigned to the
CURRVALport. Specify the current value that you wish to have as the value for the first record. As mentioned earlier, theCURRVALport gets assigned toNEXTVAL, and theCURRVALport is incremented.The
CURRVALport 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 to1. 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 to18, which will be stored internally. When you run the session the next time, it starts generating the value from18.The maximum value is
9223372036854775807. - Cycle: If you check this option, Integration Service cycles through the sequence defined. If you do not check this option, the process stops at the defined End Value.
If your source records are more than the end value defined, the session will fail with an overflow error.
- Number of Cached Values: This option indicates how many sequential values Integration Services can cache at a time. This option is useful only when you are using reusable Sequence Generator transformations.
The default value for nonreusable transformations is
0. The default value for reusable transformations is1000. The maximum value is9223372036854775807. - Reset: If you do not check this option, Integration Service stores the value of the previous run and generates the value from the previously stored value. Otherwise, the integration will get reset to the defined current value and will generate values from the initial value that was defined. This property is disabled for reusable Sequence Generator transformations.
- Tracing Level: This indicates the level of detail you wish to write into the session log. We will discuss this option in detail later in the chapter.
With this, we have seen all the properties of the Sequence Generator transformation.
Let's talk about the usage of the Sequence Generator transformation:
- Generating a primary/foreign key: The sequence generator can be used to generate a primary key and foreign key. The primary key and foreign key should be unique and not null. The Sequence Generator transformation can easily do this, as seen here. Connect the
NEXTVALport to the targets for which you wish to generate the primary and foreign key, as shown in the following screenshot:
- Replace the missing values: You can use the Sequence Generator transformation to replace missing values by using IIF and ISNULL functions. Consider that you have some data with
JOB_IDof an employee. Some records do not haveJOB_IDin the table. Use the following function to replace these missing values. Make sure you are not generatingNEXTVALin a manner similar to existingJOB_IDin 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.