Sequence Generator Transformation

Sequence generator is a passive and connected transformation, and it generates numeric sequence values such as 1, 2, 3, and so on. It does not affect the number of input rows.

The Sequence Generator transformation is used to create unique primary key values and replace missing primary keys.

For example, if we want to assign sequence values to the source records, then we need to use a sequence generator.

The sequence generator transformation consists of two output ports. We cannot edit or delete these ports, such as:

1. CURRVAL
2. NEXTVAL

NEXTVAL

The NEXTVAL port is used to generate sequence numbers by connecting it to a Transformation or target. The generated sequence numbers are based on the Current Value and Increment By properties.

If the sequence generator is not configuring to Cycle, then the NEXTVAL port makes the sequence numbers up to the set End Value.

We can connect the NEXTVAL port to multiple transformations to generate unique values for each row.

The sequence generator transformation creates a block of numbers at the same time. If the block of numbers is used, then it generates the next block of sequence numbers.

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

The integration service generates a block of numbers 1 to 10 for the first target. When the first block of numbers has been loaded, only then another block of numbers 11 to 20 will be generated for the second target.

CURRVAL

The CURRVAL port is NEXTVAL plus the Increment By value.

We only connect the CURRVAL port when the NEXTVAL port is already linked to a downstream transformation.

If we combine the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.

When we combine the CURRVAL port in a Sequence Generator Transformation, then the Integration Service processes one row in each block.

We can optimize performance by connecting only the NEXTVAL port in a Mapping.

Example: Suppose STUD will be a source table.

Create a target STUD_SEQ_GEN_EXAMPLE in the shared folder. Structure the same as STUD. Add two more ports NEXT_VALUE and CURR_VALUE to the target table.

We can create a Sequence Generator transformation to use in a single mapping, or a reusable Sequence Generator transformation to use in multiple mappings.

A reusable Sequence Generator transformation maintains the integrity of the sequence in each mapping that uses an instance of the Sequence Generator transformation.

Properties of Sequence Generator Transformation

Below are the following properties to configure a sequence data object and a new sequence:

Example

In the below example, we will generate sequence numbers and store in the target in the following steps, such as:

Step 1: Create a target table.

Step 2: Import that created table in Informatica as the target table.

Step 3: Create a new mapping and import STUD source and STUD_SEQUENCE target table.

Step 4: Create a new transformation in the mapping,

1. Select sequence transformation as the type.
2. Enter transformation name such as seq_stud.
3. Click on the Create

Step 5: Sequence generator transformation will be created, then click on the Done button.

Step 6: Link the NEXTVAL column of sequence generator to the SNO column in the target table.

Step 7: Link the other columns from source qualifier transformation to the target table.

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

1. Select the properties tab.
2. Enter the properties with Start value =1 and leave the other properties as default.
3. Click on the OK

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.