Sequence Generator Transformation in Informatica

The Sequence Generator Transformation in Informatica is a connected transformation that generates numeric values. We can mainly use this transformation to generate primary keys and foreign keys or to fill or replace the missing primary keys with unique ones.

The Sequence Generator Transformation in Informatica has two output ports, only NEXTVAL and CURRVAL, and it will not allow the users to create new ports or alter or delete existing ports.

In this article, we are going to explain the steps involved in configuring the Informatica Sequence Generator Transformation with an example. For this example, we are going to use the below show data.

Source Table 1

Configure Sequence Generator Transformation in Informatica

Before we start configuring the sequence generator transformation, First connect to the repository service. In order to connect with the Repository service, we have to provide the Admin Console credentials. So, Please provide the appropriate Username and Password and click on Connect button.

TIP: Here, you have to provide the Informatica Admin Username and password that you specified while installing the Server.

Connect to Repository Admin Console

Step 1: Creating Source Definition for Sequence Generator Transformation in Informatica

Once you have connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Sequence Generator Transformation example, we are using the [Employ] table from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition.

Source Analyzer 2

Step 2: Create Target Definition

Please navigate to Target Designer and define the Target. In this Informatica Sequence Generator Transformation example, we are using the already created SQL table (Sequence Generator) as our target definition. Please refer Create Target table using Source Definition to understand the steps involved in creating a target definition

Target Table 1

Creating Mapping for Sequence Generator Transformation in Informatica

In order to create a new mapping, Please navigate to the Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window. Here, you have to write a unique name for this mapping (m_Sequence_Generator) and click the OK button.

TIP: Please refer Mapping article to understand the steps involved in creating Mapping.

Sequence Generator Transformation in Informatica 2

Next, Drag and drop the [Employ] source definition from the Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer will automatically create the default transformation called source qualifier.

Creating a Sequence Generator Transformation in Informatica

In order to create a Sequence Generator transformation in Informatica, Please navigate to the Transformation menu in Menu Bar and select the Create.. option as shown below.

Sequence Generator Transformation in Informatica 3

Once you click on the Create.. option, Create Transformation window will be opened. Please select the Informatica Sequence Generator from the drop-down list and specify the unique name (seq_Employee) for this transformation and click on Create button

Sequence Generator Transformation in Informatica 4

Once you click on the Create button, the Informatica Sequence Generator transformation will be added to the mapping designer. Next, Drag and drop the target definition (sequence generator) from the Targets folder to the mapping designer and connect the source qualifier, Transformation, with the target definition. Please use the Autolink.. option to connect them.

Sequence Generator Transformation in Informatica 5

From the above screenshot, you can observe that the Informatica Sequence Generator transformation generates two default items NEXTVAL and CURRVAL. NEXTVAL port is the one we have to assign for the Employee_ID and we have to assign the CURRVAL inside the transformation editor.

Double click on the Informatica Sequence Generator transformation to generate the sequential numbers. From below screenshot, you can see the list of available properties in the Transformation tab:

  • Select Transformation: By default, it will select the transformation you clicked on.
  • Rename: This button will help you to rename the Sequence Generator transformation to a more meaningful name.
  • Make Reusable: If you check mark this option then, this transformation will become reusable transformation.
  • Description: Please provide a valid description of this transformation.
Edit and Rename 6

Below screenshot will show you the list of available options in the Informatica Sequence generator transformation Ports tab:

  • Port Name: List of available column names. By clicking the New column button you can add new columns and by clicking the scissors button you can delete the unwanted columns.
  • I: Sequence generator transformation will not allow Input columns.
  • O: Columns that are check-marked under this section are the sequence generator transformation Output columns. If you are unchecked any column then, that column will not be available to load in the target table.
Available Ports 7

The below screenshot will show you the list of available options in the Informatica Sequence generator transformation Properties tab.

  • Start Value: Please specify the value you want to start with. For example 1 for ID’s or Primary key value of starting or Maximum value plus one.
  • Incremented By: Please specify the value you want to use as sequence incremented. For example, 1 means the value will be incremented by 1 (1, 2, 3, …..) or if you specify 2 then values will be 2, 4, 6, 8…
  • End Value: Please specify the end value here. For example, if you specify 10 as End Value then, a sequence will stop when it reaches 10.
  • Current Value: Please specify the Current value here. For example, if you specify 3 as the current Value, then the sequence will start from 3 and increment the value using Incremented By option until it reaches the End Value. Once it reaches the End value, it will repeat the cycle starting from Start Value
  • Cycle: Please specify whether you want to repeat the cycle or Not. For example, if your End Value is less than the existing rows then it is good to select the Cycle option OR if you want to repeat the same series of numbers then select this option.
  • Reset: Please specify whether you want to reset the sequence for every single session or Not.
Sequence Generator Transformation in Informatica 8

From the below screenshot, you can observe the properties we specified. Here, Informatica Sequence generator transformation will start the sequence with value 1 and incremented by 2 until it reaches 6. After reaching the end value, the Sequence generator will start the sequence with value 0 (Start Value)

Sequence Generator Transformation in Informatica 9

Once you finish configuring the properties, Click OK to close the transformation window.

Sequence Generator Transformation in Informatica 10

Before we close the Mapping, Let us validate the mapping by going to the Mapping Menu bar and selecting the Validate option.

Step 4: Creating a Workflow

Once we finish creating the Mapping we have to create the workflow for it. PowerCenter Workflow Manager provides two approaches to creating a workflow.

In this Informatica Sequence generator transformation example, we will create the Workflow manually. In order to create a new Workflow, Please navigate to Workflows Menu and select the Create option. This will open Create Workflow window as shown below. Please provide the unique name (wf_Rank_Transformation) and leave the default settings.

Sequence Generator Transformation in Informatica 11

Once we have created the workflow, our next step is to create a session task for our mapping.

NOTE: We strictly recommend referring to the Workflow article to understand the steps involved in creating Workflow manually.

Step 4(a): Creating Session for Informatica Sequence generator transformation

There are two types of sessions:

  • Non-reusable Session Task: Please refer Session article to understand the steps involved in it.
  • Reusable Session Task: Please refer Reusable Session article to understand the steps involved in it.

For this Informatica Sequence generator transformation example, we are going to create Non-reusable Session. Please navigate to the Tasks Menu and select the Create option to open the Create Task window. Here you have to select the Session as Task type (default) and enter a unique name (S_Rank_Transformation) for the session.

Once you click on the Create button, a new window called Mappings will be opened. Here you have to select the mapping you want to associate with this session. From the below screenshot, you can observe that we are selecting the mapping (m_Rank_Transformation) that we created earlier (in Step 3).

Choose the mapping 12

Double click on the Session Task to configure it. Although we have to configure Sources, targets, and some common properties, we are explaining only a few properties. We strictly recommend visiting the Session article to understand the remaining properties.

From the below screenshot, you can observe that the Sequence Generator Transformation in Informatica workflow is a valid one. Now, let us start the Workflow by navigating to the Workflows menu and selecting the Start Workflow option.

Sequence Generator Transformation in Informatica 13

Once you select the Informatica Sequence Generator Transformation Start Workflow option, the PowerCenter Workflow monitor will open to monitor it. From the below screenshot, you can observe that our workflow is executed without any errors.

Sequence Generator Transformation in Informatica 14

Let us open the SQL Server Management Studio to check whether we successfully generated the Employee_Id using the Sequence Generator Transformation in Informatica

Target Table 15

Let us change the Properties of the Start Value, End Value, and Incremented Value as shown below. Next, refresh the mapping and start the Workflow.

Alter Sequence Start, End and Increment Value 16

Let us open the SQL Server Management Studio to check whether we successfully generated the sequential Employee_Id using the Sequence Generator Transformation in Informatica

Target Table output 17