---
description: Sequence generator transformation is used to generate numeric sequence values like 1, 2, 3, 4, 5 etc. For example, you want to assign sequence values to the source records, then you can use sequence g
title: Sequence Generator Transformation in Informatica
image: https://www.guru99.com/images/sequence-generator-transformation.png
---

[Skip to content](#main)

**⚡ 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.

- 🔢 **Two fixed ports:** NEXTVAL carries the generated numbers, and CURRVAL returns NEXTVAL plus the Increment By value.
- ⚙️ **Four core properties:** Start Value, Increment By, End Value and Cycle decide the shape of every sequence produced.
- 🧪 **Worked example:** Eight Mapping Designer steps load generated numbers into the SNO column of an EMP\_SEQUENCE target.
- ♻️ **Reusable option:** A reusable Sequence Generator is shared by several mappings and caches 1,000 values by default.
- 🔒 **Ports are fixed:** Ports cannot be added, edited or deleted, so all behaviour is controlled from the Properties tab.
- 🗝️ **Surrogate keys:** Warehouse dimension tables use generated keys instead of natural keys from the source system.

Read More

![Sequence Generator Transformation in Informatica]()

## 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](https://www.guru99.com/mappings-informatica.html).

## 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](https://drive.google.com/uc?export=download&id=0ByI5-ZLwpo25VDZKTWR5dWNMMGM)

**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.

[![Mapping Designer with the EMP source and the EMP\_SEQUENCE target definition imported]()](https://www.guru99.com/images/informatica/083115_1206_SequenceGen2.png)

**Step 4)** Create a new transformation in the mapping

1. Select sequence transformation as the type
2. Enter transformation name “seq\_emp”
3. Select Create option

[![Create Transformation window with Sequence Generator selected and the name seq\_emp entered]()](https://www.guru99.com/images/informatica/083115_1206_SequenceGen3.png)

**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.

[![Sequence Generator transformation seq\_emp created on the mapping canvas]()](https://www.guru99.com/images/informatica/083115_1206_SequenceGen4.png)

**Step 6)** Link the NEXTVAL column of Sequence Generator to SNO column in target.

[![NEXTVAL port of seq\_emp linked to the SNO column of the EMP\_SEQUENCE target]()](https://www.guru99.com/images/informatica/083115_1206_SequenceGen5.png)

**Step 7)** Link the other columns from [source qualifier](https://www.guru99.com/source-qualifier-transformation-informatica.html) transformation to the target table.

[![Remaining Source Qualifier columns linked to the EMP\_SEQUENCE target table]()](https://www.guru99.com/images/informatica/083115_1206_SequenceGen6.png)

**Step 8)** Double click on the Sequence Generator to open property window, and then

1. Select the properties tab
2. Enter the properties with Start value =1, leave the rest properties as default
3. Select OK button

[![Properties tab of the Sequence Generator transformation with the start value set to 1]()](https://www.guru99.com/images/informatica/083115_1206_SequenceGen7.png)

Now save the mapping and execute it after creating the [session](https://www.guru99.com/session-objects-informatica.html) and [workflow](https://www.guru99.com/workflows-informatica.html).

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.

**Don't Miss:**

- [Source Analyzer & Target Designer in Informatica](https://www.guru99.com/source-analyzer-and-target-designer-informatica.html)
- [Mapping in Informatica: Create, Components & Parameters](https://www.guru99.com/mappings-informatica.html)
- [Rank Transformation in Informatica with EXAMPLE](https://www.guru99.com/rank-transformation-informatica.html)
- [Top 50 Informatica Interview Questions and Answers (2026)](https://www.guru99.com/informatica-interview-questions.html)

## 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](https://www.guru99.com/performance-tuning-transformation-informatica.html). Where a key has to depend on the data itself rather than on a counter, an [Aggregator](https://www.guru99.com/aggregator-transformation-informatica.html) or [Joiner transformation](https://www.guru99.com/joiner-transformation-informatica.html) is the object doing that work, not this one.

## FAQs

🗝️ What is a surrogate key and why does it need a sequence?

A surrogate key is a meaningless number that identifies a warehouse row instead of the source system identifier. It stays stable when the source changes its codes, and a generated sequence is the simplest way to produce one.

🏦 Why use this instead of a database sequence or identity column?

Because the numbering then lives in the mapping rather than in one database. Flat file and other non-relational targets get keys the same way, and the same reusable object can number several targets without a database object per table.

🔝 What is the highest number the transformation can generate?

The End Value accepts up to 9,223,372,036,854,775,807, the largest signed 64-bit integer. Reaching it stops the session with an overflow error unless Cycle is enabled, in which case numbering restarts from the start value.

🧩 Can NEXTVAL feed another transformation instead of a target?

Yes. NEXTVAL connects to any downstream transformation, most often an Expression that builds a composite key or a Lookup that checks whether a key already exists. The generated number then travels on with the row.

🔄 Can the start value be changed after the mapping has already run?

Yes, but check the target first. Editing Start Value or Current Value on a live key column can hand out numbers the target already holds, so raise the value above the highest key rather than lowering it.

🧾 Does the transformation slow a session down?

Barely. It reads no rows and builds no cache files, so its cost is a counter update per row. Large loads are limited by the reader and the writer long before the numbering becomes an issue.

🤖 How is AI used when designing keys in ETL data pipelines?

AI profilers scan source columns to suggest which combination is genuinely unique, flag candidate keys with nulls or duplicates, and detect drift between loads. Machine learning based matching also links records that describe the same entity under different identifiers.

💻 Can GitHub Copilot help with sequence and key generation logic?

Copilot writes the equivalent [SQL](https://www.guru99.com/sql.html) sequence or identity definition and the reconciliation queries that prove no key was duplicated. It cannot see your repository values, so verify the current value in the transformation itself before a load.

#### Summarize this post with:

ChatGPTPerplexityGrokGoogle AI

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.**

##### Sign up for the newsletter

Subscribe for Free

You have successfully subscribed.  
Please check your inbox.

![AI-Newsletter]() Chosen by over **350,000+** professionals

[Scroll to top](#wrapper)

×

Search for:

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/sequence-generator-transformation.png","url":"https://www.guru99.com/images/sequence-generator-transformation.png","width":"700","height":"250","caption":"Sequence Generator Transformation","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/sequence-generator-transformation-informatica.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/informatica","name":"Informatica"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/sequence-generator-transformation-informatica.html","name":"Sequence Generator Transformation in Informatica"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/sequence-generator-transformation-informatica.html#webpage","url":"https://www.guru99.com/sequence-generator-transformation-informatica.html","name":"Sequence Generator Transformation in Informatica","dateModified":"2026-10-10T16:39:51+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/sequence-generator-transformation.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/sequence-generator-transformation-informatica.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/editorial","name":"Editorial Team","url":"https://www.guru99.com/author/editorial","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/favicon-new-logo.png","url":"https://www.guru99.com/images/favicon-new-logo.png","caption":"Editorial Team","inLanguage":"en-US"},"sameAs":["https://www.guru99.com/editorial-team"],"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"Informatica","headline":"Sequence Generator Transformation in Informatica","description":"Sequence generator transformation is used to generate numeric sequence values like 1, 2, 3, 4, 5 etc. For example, you want to assign sequence values to the source records, then you can use sequence g","keywords":"informatica","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/editorial","name":"Editorial Team"},"dateModified":"2026-10-10T16:39:51+05:30","image":{"@id":"https://www.guru99.com/images/sequence-generator-transformation.png"},"copyrightYear":"2026","name":"Sequence Generator Transformation in Informatica","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is a surrogate key and why does it need a sequence?","acceptedAnswer":{"@type":"Answer","text":"A surrogate key is a meaningless number that identifies a warehouse row instead of the source system identifier. It stays stable when the source changes its codes, and a generated sequence is the simplest way to produce one."}},{"@type":"Question","name":"Why use this instead of a database sequence or identity column?","acceptedAnswer":{"@type":"Answer","text":"Because the numbering then lives in the mapping rather than in one database. Flat file and other non-relational targets get keys the same way, and the same reusable object can number several targets without a database object per table."}},{"@type":"Question","name":"What is the highest number the transformation can generate?","acceptedAnswer":{"@type":"Answer","text":"The End Value accepts up to 9,223,372,036,854,775,807, the largest signed 64-bit integer. Reaching it stops the session with an overflow error unless Cycle is enabled, in which case numbering restarts from the start value."}},{"@type":"Question","name":"Can NEXTVAL feed another transformation instead of a target?","acceptedAnswer":{"@type":"Answer","text":"Yes. NEXTVAL connects to any downstream transformation, most often an Expression that builds a composite key or a Lookup that checks whether a key already exists. The generated number then travels on with the row."}},{"@type":"Question","name":"Can the start value be changed after the mapping has already run?","acceptedAnswer":{"@type":"Answer","text":"Yes, but check the target first. Editing Start Value or Current Value on a live key column can hand out numbers the target already holds, so raise the value above the highest key rather than lowering it."}},{"@type":"Question","name":"Does the transformation slow a session down?","acceptedAnswer":{"@type":"Answer","text":"Barely. It reads no rows and builds no cache files, so its cost is a counter update per row. Large loads are limited by the reader and the writer long before the numbering becomes an issue."}},{"@type":"Question","name":"How is AI used when designing keys in ETL data pipelines?","acceptedAnswer":{"@type":"Answer","text":"AI profilers scan source columns to suggest which combination is genuinely unique, flag candidate keys with nulls or duplicates, and detect drift between loads. Machine learning based matching also links records that describe the same entity under different identifiers."}},{"@type":"Question","name":"Can GitHub Copilot help with sequence and key generation logic?","acceptedAnswer":{"@type":"Answer","text":"Copilot writes the equivalent SQL sequence or identity definition and the reconciliation queries that prove no key was duplicated. It cannot see your repository values, so verify the current value in the transformation itself before a load."}}]}],"@id":"https://www.guru99.com/sequence-generator-transformation-informatica.html#schema-1151790","isPartOf":{"@id":"https://www.guru99.com/sequence-generator-transformation-informatica.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/sequence-generator-transformation-informatica.html#webpage"}}]}
```