Sequence Generator transformation:

• Sequence Generator transformation is 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 Data Ware House 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. 
• For 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.
• 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.

Points to remember:

• 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.