Sequence generator transformation is a passive and connected transformation. The sequence generator transformation is used for
- Generating unique primary key values.
- Replace missing primary keys
- Generate surrogate keys for dimension tables in SCDs.
- Cycle through a sequential range of numbers.
Creating Sequence Generator Transformation:
Follow the below steps to create a sequence generator transformation:
- Go to the mapping designer tab in power center designer.
- Click on the transformation in the toolbar and then on create.
- Select the sequence generator transformation. Enter the name and then click on Create. Click Done.
- Edit the sequence generator transformation, go to the properties tab and configure the options.
- To generate sequence numbers, connect the NEXTVAL port to the transformations or target in the mapping.
Configuring Sequence Generator Transformation:
Configure the following properties of sequence generator transformation:
- Start Value: Specify the start value of the generated sequence that you want the integration service to use the cycle option. If you select cycle, the integration service cycles back to this value when it reaches the end value.
- Increment By: Difference between two consecutive values from the NEXTVAL port. Default value is 1. Maximum value you can specify is 2,147,483,647.
- End Value: Maximum sequence value the integration service generates. If the integration service reaches this value during the session and the sequence is not configured to cycle, the session fails. Maximum value is 9,223,372,036,854,775,807.
- Current Value: Current Value of the sequence. This value is used as the first value in the sequence. If cycle option is configured, then this value must be greater than or equal to start value and less than end value.
- Cycle: The integration service cycles through the sequence range.
- Number of Cached Values: Number of sequential values the integration service caches at a time. Use this option when multiple sessions use the same reusable generator. Default value for non-reusable sequence generator is 0 and reusable sequence generator is 1000. Maximum value is ,223,372,036,854,775,807.
- Reset: The integration service generate values based on the original current value for each session. Otherwise, the integration service updates the current value to reflect the last-generated value for the session plus one.
- Tracing level: The level of detail to be logged in the session log file.
Sequence Generator Transformation Ports:
The sequence generator transformation contains only two output ports. They are CURRVAL and NEXTVAL output ports.
NEXTVAL Port:
You can connect the NEXTVAL port to multiple transformations to generate the unique values for each row in the transformation. The NEXTVAL port generates the sequence numbers base on the Current Value and Increment By properties. If the sequence generator is not configure to Cycle, then the NEXTVAL port generates the sequence numbers up to the configured End Value.
The sequence generator transformation generates a block of numbers at a time. Once the block of numbers is used then it generates the next block of sequence numbers. As an example, let say you connected the nextval port to two targets in a mapping, the integration service generates a block of numbers (eg:1 to 10) for the first target and then another block of numbers (eg:11 to 20) for the second target.
If you want the same sequence values to be generated for more than one target, then connect the sequence generator to an expression transformation and connect the expression transformation port to the targets. Another option is create sequence generator transformation for each target.
CURRVAL Port:
The CURRVAL is the NEXTVAL plus the Increment By value. You rarely connect the CURRVAL port to other transformations. When a row enters a transformation connected to the CURRVAL port, the integration service passes the NEXTVAL value plus the Increment By value. For example, when you configure the Current Value=1 and Increment By=1, then the integration service generates the following values for NEXTVAL and CURRVAL ports.
NEXTVAL CURRVAL
---------------
1 2
2 3
3 4
4 5
5 6
If you connect only the CURRVAL port without connecting the NEXTVAL port, then the integration service passes a constant value for each row.
