Sequence generator Transformation

Passive and Connected Transformation.
The Sequence Generator transformation generates numeric values.
Use the Sequence Generator to create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
We use it to generate Surrogate Key in DWH environment mostly. When we want to Maintain history, then we need a key other than Primary Key to uniquely identify the record. So we create a Sequence 1,2,3,4 and so on. We use this sequence as the key. Example: If EMPNO is the key, we can keep only one record in target and can’t maintain history. So we use Surrogate key as Primary key and not EMPNO.

Sequence Generator Ports :

The Sequence Generator transformation provides two output ports: NEXTVAL and CURRVAL.

We cannot edit or delete these ports.
Likewise, we cannot add ports to the transformation.
NEXTVAL:

Use the NEXTVAL port to generate sequence numbers by connecting it to a Transformation or target.

For example, we might connect NEXTVAL to two target tables in a mapping to generate unique primary key values.

Sequence in Table 1 will be generated first. When table 1 has been loaded, only then Sequence for table 2 will be generated.

CURRVAL:

CURRVAL is NEXTVAL plus the Increment By value.

We typically only connect the CURRVAL port when the NEXTVAL port is Already connected to a downstream transformation.
If we connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
when we connect the CURRVAL port in a Sequence Generator Transformation, the Integration Service processes one row in each block.
We can optimize performance by connecting only the NEXTVAL port in a Mapping.

Example: To use Sequence Generator transformation

EMP will be source.
Create a target EMP_SEQ_GEN_EXAMPLE in shared folder. Structure same as EMP. Add two more ports NEXT_VALUE and CURR_VALUE to the target table.
Create shortcuts as needed.
Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_seq_gen_example

4. Drag EMP and Target table.

5. Connect all ports from SQ_EMP to target table.

6. Transformation -> Create -> Select Sequence Generator for list -> Create -> Done

7. Connect NEXT_VAL and CURR_VAL from Sequence Generator to target.

8. Validate Mapping

9. Repository -> Save

Create Session and then workflow.
Give connection information for all tables.
Run workflow and see the result in table.
Sequence Generator Properties:

Setting Required/Optional
Description

Start Value
Required

Start value of the generated sequence that we want IS to use if we use Cycle option. Default is 0.

Increment By
Required

Difference between two consecutive values from the NEXTVAL port.

End Value

Optional
Maximum value the Integration Service generates.

Current Value Optional
First value in the sequence.If cycle option used, the value must be greater than or equal to the start value and less the end value.

Cycle Optional
If selected, the Integration Service cycles through the sequence range. Ex: Start Value:1 End Value 10 Sequence will be from 1-10 and again start from 1.

Reset Optional
By default, last value of sequence during session is saved to repository. Next time the sequence is started from the valued saved.

If selected, the Integration Service generates values based on the original current value for each session.


Points to Ponder:

If Current value is 1 and end value 10, no cycle option. There are 17 records in source. In this case session will fail.
If we connect just CURR_VAL only, the value will be same for all records.
If Current value is 1 and end value 10, cycle option there. Start value is 0.
There are 17 records in source. Sequence: 1 2 – 10. 0 1 2 3 –
To make above sequence as 1-10 1-20, give Start Value as 1. Start value is used along with Cycle option only.
If Current value is 1 and end value 10, cycle option there. Start value is 1.
There are 17 records in source. Session runs. 1-10 1-7. 7 will be saved in repository. If we run session again, sequence will start from 8.
Use reset option if you want to start sequence from CURR_VAL every time.