Sequence Generator Transformation in Informatica

Sequence generator transformation is used to generate numeric sequence values like 1, 2, 3, 4, 5 etc.

For example, you want to assign sequence values to the source records, then you can use sequence generator. The generated sequence values can be like 5, 10, 15, 20, 25 etc. or 10, 20, 30, 40, 50 etc. depending upon the configured properties of the transformation.

The sequence generator is also used to generate primary key values. It is a passive transformation & it does not affect the number of input rows.

It has two output ports

• CURRVAL
• NEXTVAL

CURRVAL port value is always NEXTVAL+1.

To generate the sequence numbers, we always use the NEXTVAL column.

Properties of Sequence Generator Transformation

• Start Value – It is the first value that will be generated by the transformation, the default value is 0.
• Increment by – This is the number by which you want to increment the values. The default value is 1.
• End value – It is the maximum value that the transformation should generate.
• Cycle – if this option is set then after reaching the end of the value, the transformation restarts from the start value.

In this example, we will generate sequence numbers and store in the target.

Step 1 – Create a target table with the following script.

DROP TABLE EMP_SEQUENCE;

CREATE TABLE EMP_SEQUENCE(

 EMPNO NUMBER(4) NOT NULL,

 ENAME VARCHAR2(10),

 JOB VARCHAR2(9),

 MGR NUMBER(4),

 HIREDATE DATE,

 SAL NUMBER(7, 2),

 COMM NUMBER(7, 2),

 DEPTNO NUMBER(2),

 SNO NUMBER(3)

) ;

PURGE RECYCLEBIN

/

Step 2- Import the table in Informatica as target table

Step 3 – Create a new mapping and import EMP source and EMP_SEQUENCE target table

Step 4 – Create a new transformation in the mapping

1. Select sequence transformation as the type
2. Enter transformation name "seq_emp"
3. Select Create option

Step 5 - Sequence generator transformation will be created, select the done option

Step 6 - Link the NEXTVAL column of sequence generator to SNO column in target

Step 7 – link the other columns from source qualifier transformation to the target table

Step 8 – Double click on the sequence generator to open property window, and then

1. Select the properties tab
2. Enter the properties with Start value =1, leave the rest properties as default
3. Select OK button

Now save the mapping and execute it after creating the session and workflow.

The sno column in the target would contain the sequence numbers generated by the sequence generator transformation.

In our example, the sequences will be like 1 – Scott, 2 – King, 3 – Adam, 4 – Miller, etc.