The Sequence Generator transformation generates numeric values.
Using this transformation we can create unique primary key values, replace missing primary keys, or cycle through a sequential range of numbers.
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.
Disadvantage:
Using a sequence generator transformation to generate unique primary key values can cause performance issues as an additional transformation is required to process in mapping.
We can use expression transformation to generate surrogate keys in a dimensional table.
No comments:
Post a Comment