Sequence Generator Transformation in Informatica
David Carter
⚡ Smart Summary
Sequence Generator transformation in Informatica is the passive, connected object that produces numeric sequences for surrogate and primary key columns, handing every row a value through two fixed output ports named NEXTVAL and CURRVAL.
What is Sequence Generator Transformation?
Sequence Generator transformation is passive, so it does not affect the number of input rows. The Sequence Generator is used to generate primary key values, and it is used to generate numeric sequence values like 1, 2, 3, 4, 5 etc.
For example, if 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.
Unlike most transformations, this one has no input ports at all. It does not read the incoming rows, it simply hands the next number to whichever port it is linked to, which is why it is safe to place beside any pipeline in a mapping.
Ports in a Sequence Generator Transformation
Sequence Generator has two output ports
- CURRVAL
- NEXTVAL
CURRVAL port value is always NEXTVAL plus the Increment By value, so it reads as NEXTVAL+1 only while Increment By is left at its default of 1.
To generate the sequence numbers, we always use the NEXTVAL column. The table below sums up how the two ports behave.
| Port | What it returns | When to connect it |
|---|---|---|
| NEXTVAL | The next number in the sequence for every row that passes through the pipeline. | Almost always. This is the port that feeds a key column in the target. |
| CURRVAL | NEXTVAL plus the Increment By value. | Only alongside NEXTVAL. Connected on its own, it passes the same constant value for every row. |
Neither port can be renamed or removed, and no third port can be added, so every change to the generated numbers is made on the Properties tab rather than on the ports.
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.
Three further settings appear on the same tab and matter as soon as the transformation is shared or a session is rerun.
| Property | What it controls | Default |
|---|---|---|
| Current Value | The value the next session run starts from. The Integration Service updates it in the repository as rows are generated. | 1 |
| Number of Cached Values | How many values are reserved in memory at a time so that concurrent sessions never hand out the same number twice. | 0 for non-reusable, 1000 for reusable |
| Reset | Starts every session from the original current value instead of continuing. It is unavailable on reusable Sequence Generators. | Disabled |
| Tracing Level | Amount of detail written to the session log: Terse, Normal, Verbose Initialization or Verbose Data. | Normal |
How to Use Sequence Generator Transformation in Informatica
In this example, we will generate sequence numbers and store them in the target. Work through the eight steps below in the Mapping Designer.
Step 1) Create a target table with the following script.
Download the above emp_sequence.sql File
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. Both definitions now sit on the canvas, as shown below.
Step 4) Create a new transformation in the mapping
- Select sequence transformation as the type
- Enter transformation name “seq_emp”
- Select Create option
Step 5) Sequence Generator transformation will be created, select the done option. The seq_emp object now appears on the canvas with its two ports.
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
- Select the properties tab
- Enter the properties with Start value =1, leave the rest properties as default
- 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.
Reusable and Non-Reusable Sequence Generators
The transformation built above lives inside one mapping. Creating it in the Transformation Developer instead makes it reusable, and the two behave differently once more than one session is involved.
| Point of comparison | Non-reusable | Reusable |
|---|---|---|
| Where it is created | Inside a single mapping, in the Mapping Designer | In the Transformation Developer, then added to any number of mappings |
| Number of Cached Values | Defaults to 0, so values are not reserved in advance | Defaults to 1000, so each session reserves its own block of numbers |
| Reset property | Available, and restarts the sequence from the original current value | Not available, because restarting would hand out numbers another session already used |
| Typical use | One target key column in one mapping | A shared surrogate key across several mappings loading the same dimension |
Caching is what keeps concurrent sessions apart. Each session takes a block of numbers up front, which means the numbers stay unique but do not stay contiguous: any values left unused when a session ends are discarded, and the next block starts after them.
Rules and Limitations of Sequence Generator Transformation
Some of these only surface after the mapping has run, so they are cheaper to know before the ports are linked.
- The ports are fixed. NEXTVAL and CURRVAL cannot be edited or deleted, and no further port can be added to the transformation.
- CURRVAL on its own returns a constant. Connect CURRVAL only when NEXTVAL is also connected downstream, otherwise every row receives the same value.
- Two targets are loaded one after another. When NEXTVAL feeds two target tables, the first target is loaded with its numbers before the second target starts, so the two sets do not interleave.
- Gaps are normal. Cached values that a session does not use are never handed back, so a failed or short session leaves holes in the sequence. Design the key column to tolerate gaps.
- Cycle can create duplicates. Once the end value is reached with Cycle enabled the numbering restarts at the start value, which will collide with existing keys unless the target allows it.
Because it never reads a row, the transformation adds almost nothing to session runtime, which is why it rarely appears as a bottleneck during performance tuning. Where a key has to depend on the data itself rather than on a counter, an Aggregator or Joiner transformation is the object doing that work, not this one.
