Thursday, February 25, 2016

Sequence Generator Transformation In Informatica

Sequence Generator Transformation 

Ø  A Sequence genrator transformation generates numeric values.
Ø  Use the Sequence Generator transformation to create unique Primary / Surrogate key values, replace missing primary keys, or cycle through a sequential range of numbers.
Ø  A Sequence generator transformation contains two output ports: CURRVAL, NEXTVAL and you can edit or delete these ports from the transformation.
Ø  NEXTVAL port:
·         You can connect NEXTVAL to a transformation to generate unique values for each row in the transformation.
·         Connect the NEXTVAL port to a downstream transformation or target to generate a sequence of numbers.
·         If you connect NEXTVAL to multiple transformations, the Integration Service generates the same sequence of numbers for each transformation.
·         You connect the NEXTVAL port to generate the sequence based on the Start Value and Increment Value properties.
·         If the Sequence Generator is not configured to cycle through the sequence, the NEXTVAL port generates sequence numbers up to the configured end value.
·         CURRVAL is NEXTVAL plus the Increment By value.
·         You typically only connect the CURRVAL port when the NEXTVAL port is already connected to a downstream transformation.
·         When a row enters a transformation connected to the CURRVAL port, the Integration Service passes the last created NEXTVAL value plus one.
·         If you connect the CURRVAL port without connecting the NEXTVAL port, the Integration Service passes a constant value for each row.
·         When you connect the CURRVAL port in a Sequence Generator transformation, the Integration Service processes one row in each block. You can optimize performance by connecting only the NEXTVAL port in a mapping.

Ø  Sequence Generator Properties:

Start Value :Start value of the generated sequence that you want the Integration Service to use if you use the Cycle option. If you select Cycle, the Integration Service cycles back to this value when it reaches the end value.
·         Default is 0.
·         Maximum value is 9,223,372,036,854,775,806.
Increment By :Difference between two consecutive values from the NEXTVAL port.
·         Default is 1.
·         Must be a positive integer.
·         Maximum value is 2,147,483,647.
End Value : Maximum value that 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.
·         If you connect the NEXTVAL port to a downstream integer port, set the end value to a value no larger than the integer maximum value.
·         If the NEXTVAL exceeds the datatype maximum value for the downstream port, the session fails.
Current Value  :Current value of the sequence. Enter the value you want the Integration Service to use as the first value in the sequence. To cycle through a series of values, the value must be greater than or equal to the start value and less than the end value.
·         If the Number of Cached Values is set to 0, the Integration Service updates the current value to reflect the last-generated value for the session plus one, and then uses the updated current value as the basis for the next time you run this session. However, if you use the Reset option, the Integration Service resets this value to its original value after each session.
·         If you edit this setting, you reset the sequence to the new setting. If you reset Current Value to 10, and the increment is 1, the next time you use the session, the Integration Service generates a first value of 10.
·         Maximum value is 9,223,372,036,854,775,806. The Integration Service sets the value to NULL if the current value exceeds the maximum value.
Cycle :
·         If enabled, the Integration Service cycles through the sequence range and starts over with the start value.
·         If disabled, the Integration Service stops the sequence at the configured end value. The Integration Service fails the session with overflow errors if it reaches the end value and still has rows to process.

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 Sequence Generator at the same time to ensure each session receives unique values.
·         The Integration Service updates the repository as it caches each value. When set to 0, the Integration Service does not cache values.
·         Default value is 0.
·         Default value for a reusable Sequence Generator is 1,000.
·         Maximum value is 9,223,372,036,854,775,807.
Reset :
·         If enabled, the Integration Service generates values based on the original current value for each session.
·         If disabled, the Integration Service updates the current value to reflect the last-generated value for the session plus one, and then uses the updated current value as the basis for the next session run.
·         This property is disabled for reusable Sequence Generator transformations.

Tracing Level :Level of detail about the transformation that the Integration Service writes into the session log. 


  1. Informatica is a software development company. It delivers management software and data integration products for the cloud and big data.Start to learn Informatica course in Chennai at FITA.They are offering a wonderful training with experienced professionals.
    Informatica Training in Chennai | Informatica Training Center in Chennai

  2. I can only express a word of thanks! Nothing else. Because your topic is nice, you can add knowledge. Thank you very much for sharing this information.

    Avriq India
    pest control
    cctv camera