Informatica
Informatica Sequence Generator
Use Sequence Generator when your mapping needs a predictable surrogate key inside PowerCenter.
What It Does
- Generates numeric values inside a mapping without relying on the source system.
- Usually feeds surrogate keys for dimensions, staging tables, or row-level IDs.
- Exposes two ports: NEXTVAL for the next generated number and CURRVAL for the current one.
Core Ports
| Element | Meaning | Typical Use |
|---|---|---|
| NEXTVAL | Next generated value in the sequence. | Write a new surrogate key. |
| CURRVAL | Current value after NEXTVAL advances. | Reuse the same generated key downstream. |
| Start value | First emitted value. | Set the first key in a load. |
| Increment by | Step size between values. | Control key spacing or partitioning. |
Basic Flow
- 1Add the Sequence Generator transformation to the mapping.
- 2Configure start value, increment, and port usage.
- 3Connect NEXTVAL to the target key column that needs a new identifier.
- 4Use CURRVAL only when you need the same generated value more than once in the mapping.
Code
Typical surrogate-key pattern
SRC_CUSTOMER -> EXP_CLEAN -> SEQ_CUSTOMER_KEY.NEXTVAL -> TGT_DIM_CUSTOMER.CUSTOMER_KEYFAQ
When should I use CURRVAL?
Use it when one generated key needs to feed multiple downstream fields in the same mapping.
Why use Sequence Generator instead of source keys?
Source keys often collide across systems or are not stable enough for dimensional models.
Does Sequence Generator guarantee no gaps in surrogate keys?
No. Sequence Generator does not roll back values after session failures or row rejections. Gaps are expected and normal in ETL workflows.
Can I share one Sequence Generator across multiple target tables?
Yes, technically, but it is not recommended. Shared generators produce interleaved key ranges, making it harder to reason about each table's key domain. Use a separate generator per target table for clean key segregation.
Related