Informatica Transformations are repository Objects that are used to perform aggregations, sorting, merging, modifying, distributing, etc., while passing data through them.

The Power Center Designer provides a set of transformations in Informatica that perform specific functions. For example, Sorter transformation will sort the input fields based on a specified field.  

Types of Transformations in Informatica

A transformation is a part of Informatica Mapping, which transforms or modifies the data as per the selected transformation. This section covers the list of available ones with practical examples.

Informatica transformations can divide into two types: Active or Passive. We can further divide them based on the connectivity in mapping: Connected and Unconnected Transformations.

Active Transformations in Informatica

An active type of transformation in Informatica can change the number of rows that pass through it. For example, if the input rows do not meet the specified expression, then those rows will not move to the target. 

Passive Transformations in Informatica

The Informatica passive transformation does not change the number of rows passing through it.

Connected Transformations in Informatica

Within an Informatica Mapping, if a transformation is connected to another or connected directly to the target table, it is called Connected.

All the active and passive can call connected transformations in Informatica, and they can receive / or provide multiple input ports. 

Unconnected Transformations in Informatica

Within the Mappings, if the Informatica transformation does not connect to others, we can call it Unconnected. It means it is neither connected to Source nor the target.

Unconnected Lookup is the best example of Informatica Unconnected.

Informatica Transformations List

Before we get into the list of Informatica transformations and their definitions, let me open my Power Center Designer to display the available list in mappings:

and few more

The following are a list of Informatica transformations that are available to transform data. I suggest you click on the URL (red color links) to navigate to the required transformation and find the example with a detailed explanation.

If you want to save the above specified list of Informatica Transformations in your Mobile, then use below image 

The Expression Transformation in Informatica is a passive transformation that is used to perform non-aggregate calculations on the source data. It means you can use this Informatica Expression transformation to perform calculations on a single row. For example, you are trimming the extra spaces, data conversions, string manipulations, etc.

The following are some of the operations achieved by the expression transformation in Informatica :  

• Using this expression transformation in Informatica, we can test the data before passing it to another transformation or target table using conditional statements IIF
• Data Manipulation: We can manipulate the data using built-in functions. For example, String Concatenation, Trimming the extra spaces using LTRIM and RTRIM Rounding the values, etc.
• Data Conversion: Converting the existing data to a required form using TO_DATE, TO_DECIMAL, etc.
• Manipulating Date value: Extracting date part, calculating the date differences, etc.
• Perform Arithmetic and logical operations.

This example explains the steps to configure the Expression Transformation in Informatica to store the Audit information. For this example, we are going to use the below show data

In real-time this audit information will be very helpful to check the session time, Mapping Name, Workflow Name, Session Name, Integration Service Name, Repository Service Name, Repository User Name, Source Table Name, Session Start Time, and System Date.

Configure Expression Transformation in Informatica

In this example, Our task is to load [First Name], [Last Name], and [Yearly Income] from the above source to the target table along with Audit Information. Before we start configuring the Expression transformation in Informatica, First connect to the repository service. 

To connect with the Informatica Repository service, we have to provide the Admin Console Username and Password you specified while installing the Server. Next, click on the Connect button.

Create Informatica Expression Transformation Source Definition

Once you connected, Please navigate to Source Analyzer and define your Sources. In this Informatica expression transformation example, we are using the [Employ] table from the SQL Server database as our source definitions. Please refer Database Source article for the source definition

Create Informatica Expression Transformation Target Definition

Please navigate to Target Designer and define the Informatica Expression Transformation Target. In this example, we are using the already created SQL table (Expression Transformation) as our target definition. Please refer Create Target table using Source Definition article 

Create Expression Transformation in Informatica Mapping

To create new Informatica Expression Transformation mapping, Please navigate to the Mappings menu and select the Create.. option. It opens the Mapping Name window to write a unique name (m_Expression_Transformation) and then click OK button.

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

Creating Expression Transformation in Informatica

To create Expression Transformation in Informatica, Please navigate to the Transformation menu and select the Create.. option.

Once you click on the Create.. option, Create Transformation window will open. Please select the Informatica Expression Transformation and specify the unique name (exp_Audit_ Information) and then click on Create button

Once you click on the Create button, Expression Transformation will add to the mapping designer. Please connect the Source definition with the transformation.

From the below screenshot you can observe that we are adding [First Name], [Last Name] and [Yearly Income] to the expression transformation  

Double click on the Informatica Expression transformation to write the custom expressions. From the 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 Expression transformation to a more meaningful name.
• Make Reusable: If you checkmark this option, this transformation will become a reusable transformation.
• Description: Please provide a valid description.

Below screenshot will show you the list of available options in the Informatica Expression 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: Columns that are check-marked under this section are the expression transformation Input columns.
• O: Columns that are check-marked under this section are the expression transformation Output columns. If you unchecked any column then, that column will not be available to load in a target table.
• V: Please check mark this if it is variable

From the below screenshot, you can observe that We added 9 new columns using the New Port button. 

We are going to use these new columns to store the Audit information such as Mapping Name, Workflow Name, Session Name, Integration Service Name, Repository Service Name, Repository User Name, Source Table Name, Session Start Time and System Date.

TIP: All these new columns are output ports only. So, please checkmark O

Let us write the custom expression to get the information we required. To do this, click on the arrow button beside the Mapping Name

It opens the Expression Editor to write a custom expression. Here, we want the Mapping Name. So, please go to the Variables Tab and select one of the Built-in Variables called $PMMappingName.

For the remaining fields in Informatica expression transformation, Please add the appropriate built-in variables.

• Workflow Name: $PMWorkflowName
• Session Name: $PMSessionName
• Integration Service Name: $PMIntegrationServiceName
• Repository Service Name: $PMRepositoryServiceName
• Repository User Name: $PMRepositoryUserName
• Source Table Name: $PMEmploy@TableName
• Session Start Time: SSSSTARTTIME
• System Date: SYSDATE

Once you finish configuring the expressions of Informatica expression transformation. Click OK to close the transformation window. Next, Drag and drop the target definition (expression transformation) from the Targets folder to the mapping designer. Next, connect the expression transformation with the target definition using the Autolink.. option.

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

From the above screenshot, you can observe that Our Mapping is a valid one.

Create a Workflow for Expression Transformation in Informatica

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

• Creating Workflow manually
• Creating Workflow using Wizard

In this Informatica Expression Transformation example, we will create the Workflow manually. Once we created the workflow, our next step is to create a session task for our mapping.

Step 4(a):

There are two types of sessions:

• Non-Reusable Session
• Reusable Session

For this Informatica Expression Transformation example, we are going to create Non-reusable Session. Please navigate to 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_Expression_Transformation) for the session.

Once you click on the Create button, a new window called Mappings will open. Here you have to choose the mapping you want to associate with this session.

From the below screenshot, you can observe that we are selecting the mapping (m_Expression_Transformation) that we created earlier (in Step 3).

Double click on the Session Task to configure it. Although we have to set Sources, targets, and some common properties, we are not going to explain them here. We strictly recommend visiting the Sessions article to understand the features.

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

Once you select the Start Workflow option, the PowerCenter Workflow monitor opened to monitor the workflow. From the below screenshot, see our Informatica Expression Transformation workflow executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully stored the Audit Information using the Expression Transformation in Informatica or not.

The Filter Transformation in Informatica is used to filter the records based on the specified expression/condition. The Filter condition return Boolean TRUE or FALSE, it means integration service will check each row against the specified condition. And if the condition is TRUE then the Informatica Filter transformation will pass the row otherwise, it will skip the current row and check the next one.

Filter Transformation in Informatica is very helpful in real-time. For example, If you want to load the customer data whose sales value is above the minimum threshold etc. In this article, we are going to perform Filter Transformation on two SQL tables.  

For this Informatica Filter Transformation example, we are using the [Dim Products] and [fact Internet Sales] tables from our [Adventure Works DW 2014] database.

Filter Transformation in Informatica Example

Before we start doing anything, First connect to the Informatica repository service with your Admin Console credentials.

Create an Informatica Filter Transformation Source Definition

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Filter Transformation example, we are using [Dim Products] and [fact Internet Sales] from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition 

Creating a Target Definition for Filter Transformation in Informatica

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

Create a Mapping for Filter Transformation in Informatica

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

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

Next, Drag and drop the [Dim Products] and [fact Internet Sales] source definitions from Sources folder to the mapping designer. Once you drag the source, PowerCenter Designer will automatically create the default transformation called source qualifier as we shown below.

As we all know that the [Dim Products] and [fact Internet Sales] tables have the Primary and Foreign key relationship. So, we removed one source qualifier and added the required fields from both tables to single source qualifier.

Creating a Filter Transformation in Informatica

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

Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please select the Filter Transformation from the drop-down list and specify the unique name (fil_SalesAmount) for it and click on Create button

Once you click on the Create button, filter transformation will be added to the mapping designer. In order to filter the records by an expression, filter Transformation requires some data. So, we have to connect the Source definition with the transformation using Autolink.. option (or select and Drag required fields).

Double click on the Filter transformation to provide the filter condition. From the 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 filter 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.

Below screenshot will show you the list of available options in the Informatica Filter 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: Columns that are check-marked under this section are the filter transformation Input columns.
• O: Columns that are check-marked under this section are the filter transformation Output columns. If you unchecked any column then, that column will not be available to load in a target table.

The following screenshot will show you the list of available options in the Properties tab. By default Filter Condition will be set to TRUE and here only we have to provide the condition. In order to specify the condition, click on the Arrow button as shown below.

TIP: If you specify the Filter Condition to FALSE then filter condition will not allow single record.

Once you click on the arrow button, Expression Editor window will be opened as shown below. Here you can write custom expressions using the available functions, Ports (Column Names) and Variables.

For this example, we are using the simple expression i.e., SalesAmount > 2000. It means Informatica Filter transformation will return the records whose sales amount is greater than 2000

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

Next, Drag and drop the target definition (Filter Transformation in Informatica) from the Targets folder to the mapping designer and connect the Transformation with the target definition. Please use the Autolink.. option to connect them.

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

Create an Informatica Filter Transformation Workflow

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

• Creating Workflow manually
• Creating Workflow using Wizard

In this Informatica Filter 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_Filter_Transformation) and leave the default settings.

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

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

Creating a Session

There are two types of sessions:

• Non-reusable Session Task: Please refer Session
• Reusable Session Task: Please refer Reusable Session

For this Informatica Filter Transformation example, we are going to create Non-reusable Session. Please navigate to 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_Filter_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_Filter_Transformation) that we created earlier (in Step 3).

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 we assigned the $target variable to Connection Value, we changed the Target Load Type option from Bulk to Normal mode and check marked the Truncate target table option to truncate the existing data from the destination table.

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

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our workflow is executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully restricted the records with Sales Amount less than 2000 using the Informatica Filter Transformation or not.

The Java Transformation in Informatica provides an opportunity to write a custom Java code within the transformation. If you are familiar with Java programming, then you can write a complex code inside this transformation.

For this Informatica Java Transformation example, we are going to use SP Source and Destination tables. From the below screenshot, you can see that the Source table has 15 records, and the Destination table is Empty.  

Java Transformation in Informatica Example

In this example, we show how to create and configure Java Transformation in Informatica. Before we start configuring, First, let me connect with the Informatica repository service.

To do so, we have to provide the Admin Console credentials. So, Please provide the appropriate Username and Password you specified while installing the Server.

Create Source Definition for Java Transformation in Informatica

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

Create Informatica Java Transformation Target Definition

Please navigate to Target Designer to define the Target. In this Informatica Java Transformation example, we are using the existing SQL table (SP Destination) as our target definition. You can refer to Create Target table using Source Definition to understand the creation of a target definition

Create Informatica Java Transformation Mapping

To create a new mapping for Informatica Java Transformation, Please navigate to the Mappings menu in Menu Bar. Next, select the Create.. option.

Once you select the Create.. option, a new Mapping Name window opens. Let me provide m_Java as the mapping name and click the OK button. 

Drag and drop the SP Source from Sources folder to the mapping designer. Once you drag the source, Power Center Designer automatically creates the Source Qualifier Transformation (default transformation) for you.

Create Informatica Java Transformation

To create Java transformation in Informatica, Please navigate to the Transformation menu in Menu Bar. Next, select the Create.. option.

Selecting the Create.. option opens the Create Transformation window. Please select the Java Transformation from the drop-down list and provide the unique name (Java_Employees) for this transformation and click on Create button

Clicking the Create button will pop up a new window called Active or Passive. Please select whether you want to use this Java Transformation as an Active transformation or passive Transformation.

By clicking OK will create a Java Transformation for you. Let me drag all the fields that are available in source definition to Java transformation.

Double click on the Informatica Java transformation to see and alter the properties. Below screenshot shows you the list of available properties in the Transformation tab:

• Select Transformation: By default, it selects the transformation you selected (or clicked on).
• Rename: This button helps you to rename the transformation name.
• Description: Use this place to provide a valid description.

The below screenshot shows you the list of available columns in the Ports tab. As you can, all the columns dragged from the source definition are in the OUTPUT section.

TIP: Few people may not drag fields from Source Qualifier at this point. They will create both Input and Output fields on their own, and then they point them to Source Qualifier.

To create new fields under the input section, select the INPUT, and then click the New field button.

In our Informatica Java Transformation source definition, we have 7 columns. So let me create 7 new fields under the INPUT section. We also need an extra output column for the New Income filed in our target table

Let me rename the Field names as per the Source Definition

Java Code Tab: This is used to write custom Java code. Here, the Left side window is a Navigator window – Which holds all Input, Output ports that are available in Java transformation, and few regularly used functions.

Let me write a simple code for this Informatica Java Transformation

Within the above code, we used the If Else Statement along with Equals method to check whether the Occupation of an employee is Management or not. If it is TRUE then we are adding 45750 to yearly income otherwise we added 25009. Code that we used is:

EmpID = in_EmpID;
FirstName = in_FirstName;
LastName = in_LastName;
Education = in_Education;
Occupation = in_Occupation;
YearlyIncome = in_YearlyIncome;
Sales = in_Sales;
if(in_Occupation.equals("Management"))
{
	NewIncome = in_YearlyIncome + 45750;
}
else
{
	NewIncome = in_YearlyIncome + 25009;
}
generateRow();

Once you finish configuring the Java Transformation, you have to map the Source qualifier with the Java Transformation Input fields.

Next, Drag and drop the target definition (SP Destination) from the Targets folder to the mapping designer. Next, connect the Output Fileds of a Java Transformation with the target definition using the Autolink.. option.

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

Create Informatica Java Transformation Workflow

After you finish creating the Informatica Java Transformation Mapping, we have to create the workflow for it. PowerCenter Workflow Manager provides two approaches to create a workflow. 

• Create Workflow Manually
• Create Workflow using Wizard

In this Informatica Java Transformation example, we will create the Workflow manually. To do so, Please navigate to Workflows Menu and select the Create option.

It opens a Create Workflow window. Please provide a unique name (wf_JavaEmp) and leave the default settings.

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

Create Java Transformation Session

There are two types of sessions:

• Non-Reusable Session
• Reusable Session

For this Informatica Java Transformation example, we created a Non-reusable Session. To create Non-reusable Session, Please navigate to Tasks Menu and select the Create option as shown below.

Please provide a unique name (s_JavaEmp) for this session. Once you click on the Create button, a new window called Mappings will open. Here you have to select the mapping that you want to associate with this session, i.e., m_Java.

Double click on the Session task opens the Edit Tasks window. Within the mappings tab, we have to configure the Source, Target Connections.

First, let us configure the source connections by clicking on the SQ_SPSource source present in the Sources folder. Within the Connections, click on the Arrow button beside the Relational type, and select the Database Object (Database Name), i.e., Informatica Source

Now, we have to configure the Target Connection for this Informatica Java Transformation. So, click on the SPDestination present in the Targets folder. Within the Connections, click on the Arrow button beside the Relational type. Next, select the Database Object (Target Database Name), i.e., Informatica Target

Next, navigate to Workflows Menu and select the Validate option to validate the Workflow.

Now, Let me start the Informatica Java Transformation Workflow. To do so, navigate to the Workflows menu and select the Start Workflow option.

Let us open the SQL Server Management Studio and write following SQL Query.

The Joiner Transformation in Informatica is used to perform SQL Joins. There are four types of Joins in Informatica, and they Inner Join (Normal Join), Master Outer Join, Detail Outer Join and Full Outer Join. Joiner Transformation in Informatica is very useful to load data into the Dimension tables in Data Warehouse.

Normal Join: The Normal Join in Informatica is exactly similar to SQL Inner Join. It returns the records (or rows) present in both tables If there is at least one match between columns. Let us see the visual representation of the Normal Join for better understanding.  

In this article, we are going to perform Normal Join on two SQL tables using Joiner Transformation in Informatica. Before we start designing the Mapping let us look at our two source tables on which we are going to perform Normal Join using Informatica Joiner Transformation.

Employees Table inside the Database is:

Department Table inside the  

Joiner Transformation in Informatica Example

Before we start doing anything, First connect to Informatica repository service. In order to connect with Repository service we have to provide the Admin Console credentials so, Please provide the appropriate Username and Password and click on Connect button as shown below.

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

Step 1: Create Source Definition for Joiner Transformation in Informatica

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Joiner Transformation example, we are using Department and Employee tables from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition  

Step 2: Creating a Target Definition for Joiner Transformation in Informatica

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

Step 3: Create Joiner Transformation in Informatica Mapping

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

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

Next, Drag and drop the Employee and Department source definitions from Sources folder to the mapping designer. Once you drag the source, PowerCenter Designer will automatically create the default transformation called source qualifier.

Step 3(a): Creating Joiner Transformation in Informatica

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

Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please click on the arrow to select the required one from drop-down list. Let us select the Informatica Joiner transformation 

Next, Please specify the unique name for this Informatica Joiner Transformation (JNR_NORMAL) and click on Create button

Once you click on the Create button, the Informatica Joiner transformation will be added to the mapping designer. In order to perform Normal Join, the Joiner Transformation requires some data. So, we have to connect the Source definition with the transformation using Autolink.. option (or select and Drag required fields).

Double click on the Informatica Joiner transformation to configure the Join conditions. From the 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 it to a more meaningful name.
• Make Reusable: If you check mark this option then, it will become reusable transformation.
• Description: Please provide a valid description.

The list of available options in the Informatica Joiner 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: Columns that are check-marked under this section are the Input columns.
• O: Columns that are check-marked under this section are the Joiner transformation Output columns. If you unchecked any column then, that column will not be available to load in a target table.
• M: Please checkmark the Column(s) that you want to use as Master table. It is always good practice to select the table holding the least records as Master table. Here, we are selecting a Department table as our Master table.

NOTE: You don’t have to select each and every column present in the Master table. If you select One column from Employee then it will automatically the remaining columns.

Below screenshot will show you the list of available options in the Informatica Joiner Transformation Properties tab:  

• Join Type: In this example, we are performing Normal Join so, we are selecting the Normal Join from the drop-down list.
• Sorted Input: If we are using the sorted data then check mark this option.

Within the Condition tab, we have to specify the condition. In this example, we are going to perform Normal Join on Department and Employee table based on the condition Department. id = Employee.Depart Id. So, click on the New button (beside scissors) to create a new condition and select the columns names from Master and detail list

Once you finish configuring the Informatica Joiner Transformation properties, Click OK to close the window.

Next, Drag and drop the target definition from Targets folder to the mapping designer and connect the Joiner Transformation with the target definition. Please use the Autolink.. option to connect them. 

Before we close the Mapping, Let us validate the Informatica Joiner Transformation mapping by going to 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 create a workflow.

• Creating Workflow manually
• Creating Workflow using Wizard

In this Informatica Joiner 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_joiner_transformation) and leave the default settings. 

Once we created the workflow, our next step is to create a session for joiner transformation in informatica.

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

Step 4(a): Creating Session

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 Joiner Transformation example, we are going to create Non-reusable Session. In order to create Non-reusable Session, Please navigate to 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 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 that we created earlier (in Step 3).

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

Employee Source

Within the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser as shown below. Here, Our Employee table is coming from the Source, we are using the Connection variable that we created in our previous step i.e., $Source

Department Source

Within the Connections, select the Arrow button beside the relational type to open the Relational Connection Browser as shown below. Here, Our Department table is also coming from the Source so we are using the Connection variable that we created in our previous step i.e., $Source

From the below screenshot you can observe that we assigned the $target variable to Connection Value, we changed the Target Load Type option from Bulk to Normal mode and check marked the Truncate target table option to truncate the existing data from the destination table.

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

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

Let us open the SQL Server Management Studio to check whether we successfully performed the Normal Join using the Joiner Transformation in Informatica.

The Master Outer Join in Informatica is used to return all the existing records from Details table and only matching records from Master table.

Let us see the visual representation of the Master Outer Join in Informatica for better understanding.  

In this article, we are going to perform Informatica Master Outer Join on two SQL tables using Joiner Transformation in Informatica.

Before we start designing the Mapping let us look at our two source tables on which we are going to perform Master Outer Join in Informatica.

Employees Table inside the Database is: 

Department Table inside the

Master Outer Join in Informatica Example

Before we start doing anything, First connect to the repository service with your Admin Console credentials.

TIP: Please refer to Normal Join, Detail Outer Join, and Full Outer Join articles to understand the remaining Joins in Informatica.  

Step 1: Creating Source Definition for Informatica Master Outer Join

Once you have connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Master Outer Join transformation example, we use Department and Employee tables from the SQL Server database as our source definitions. Please refer to Database Source to understand the steps involved in creating a source definition

Step 2: Creating a Informatica Master Outer Join Target Definition

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

Step 3: Creating Mapping for Informatica Master Outer Join

In order to create a new mapping, Please navigate to Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window as shown below. For this Informatica Master Outer Join transformation, you have to write a unique name for this mapping (m_Master_Outer) and click OK button.

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

Next, Drag and drop the Employee and Department source definitions from Sources folder to the mapping designer. Once you drag the source, PowerCenter Designer will automatically create the default transformation called source qualifier.

Step 3(a): Creating Joiner Transformation in Informatica

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

Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please select the Joiner transformation from a drop-down list and specify the unique name for this transformation (JNR_MASTER) and click on Create button

Once you click on the Create button, the Joiner transformation will be added to the mapping designer. In order to perform Master Outer Join, Joiner Transformation requires some data.

So, we have to connect the Source definition with the transformation using the Autolink.. option (or select and Drag required fields).

Double click on the Joiner transformation to configure the join conditions. From the below screenshot you can see the list of available options in the Ports tab:

• M: Please checkmark the Column(s) that you want to use as Master table. It is always good practice to select the table holding the least records as Master table. Here, we are selecting a Department table as our Master table.

NOTE: You don’t have to select each and every column present in the Master table. If you select One column from Employee then it will automatically the remaining columns.

The list of available options in the Master Outer Join transformation Properties tab: 

• Join Type: In this example, we are performing Master Outer Join. So, we are selecting the Informatica Master Outer Join from the drop-down list.
• Sorted Input: If we are using the sorted data then check mark this option.

Within the Condition tab, we have to specify the condition. In this example, we are going to perform Informatica Master Outer Join on Department and Employee table based on the condition Department. id = Employee.Depart Id. So, click on the New button (beside scissors) to create a new condition and select the columns names from Master and detail list

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

Next, Drag and drop the target definition from Targets folder to the mapping designer and connect the Joiner Transformation with the target definition. Please use the Autolink.. option to connect them.

Before we close the Mapping, Let us validate the mapping by going to 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 Informatica master outer join. PowerCenter Workflow manager provides two approaches to create a workflow.

• Creating Workflow manually
• Creating Workflow using Wizard

In this Master Outer Join 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_Master_Outer) for this Informatica Master Outer Join transformation workflow and leave the default settings.

Once we created the Informatica master outer join workflow, our next step is to create a session.

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

Step 4(a): Creating Session for Master Outer Join in Informatica

There are two types of sessions:

• Non-reusable Session Task: Please refer to Session
• Reusable Session Task: Please refer to Reusable Session

For this Informatica Master Outer Join example, we are going to create Non-reusable Session. Please navigate to 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_Master_Outer) 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_Master_Outer) that we created earlier (in Step 3).

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.

We assigned the $target variable to Connection Value, we changed the Target Load Type option from Bulk to Normal mode and check marked the Truncate target table option to truncate the existing data from the destination table.

From the below screenshot you can observe that the Master Outer Join 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.

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our workflow is executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully performed the Master Outer Join in Informatica using the Joiner Transformation or not.

The Detail Outer Join in Informatica is used to return all the existing records from Master table and only matching records from Detail table.

Let us see the visual representation of the Detail Outer Join in Informatica for better understanding.  

In this article, we are going to perform Informatica Informatica Detail Outer Join on two SQL tables using Joiner Transformation. Before we start designing the Mapping let us look at our two source tables on which we are going to perform Detail Outer Join in Informatica.

Employees Table inside the Database is:

Department Table inside the Database is: 

Detail Outer Join in Informatica Example

Before we start doing anything, First connect to the repository service with your Admin Console credentials.

TIP: Please refer to Normal Join, Master Outer Join, and Full Outer Join articles to understand the remaining Joins in Informatica.

Step 1: Creating Source Definition for Detail Outer Join in Informatica

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica detail outer join example, we are using Department and Employee tables from SQL Server database as our source definitions. Please refer Database Source to understand the steps involved in creating source definition 

Step 2: Creating a Target Definition for Informatica Detail Outer Join

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

Step 3: Creating Informatica Detail Outer Join Mapping

For this Informatica detail outer join example, we created the m_Detail_Outer mapping. Please refer Mapping article to understand creating Mapping. Next, Drag and drop the Employee and Department source definitions from the Sources folder to the mapping designer. Once you drag the source, PowerCenter Designer will automatically create the default transformation called source qualifier.

Step 3(a): Creating Joiner Transformation in Informatica

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

Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please select the Joiner transformation from a drop-down list and specify the unique name (JNR_DETAIL) for this transformation and click on Create button

Once you click on the Create button, the Joiner transformation will be added to the mapping designer. In order to perform Detail Outer Join, Joiner Transformation requires some data so, we have to connect the Source definition with the transformation using Autolink.. option (or select and Drag required fields).

Double click on the Joiner transformation to configure the join conditions. From the below screenshot you can see the list of available options in the Ports tab:

• M: Please checkmark the Column(s) that you want to use as Master table. It is always good practice to select the table holding the least records as Master table. Here, we are selecting a Department table as our Master table.

NOTE: You don’t have to select each and every column present in the Master table. If you select One column from Employee then Informatica will automatically the remaining columns.

Below screenshot will show you the list of available options in the Properties tab:

• Join Type: In this example, we are performing Informatica Detail Outer Join. So, we are selecting the Detail Outer Join from the drop-down list.
• Sorted Input: If we are using the sorted data then check mark this option.

Within the Condition tab, we have to specify the condition. In this example, we are going to perform Informatica Detail Outer Join on Department and Employee table based on the condition Department. id = Employee.Depart Id. So, click on the New button (beside scissors) to create a new condition and select the columns names from Master and detail list 

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

Next, Drag and drop the target definition (Detail Outer Join in Informatica) from Targets folder to the mapping designer and connect the Joiner Transformation with the target definition. Please use the Autolink.. option to connect them.

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

Step 4: Creating a Workflow for Detail Outer Join in Informatica

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

• Creating Workflow manually
• Creating Workflow using Wizard

In this Informatica Detail Outer Join 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_Detail_Outer) and leave the default settings.

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

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

Step 4(a): Creating Session for Informatica Detail Outer Join

There are two types of sessions:

• Non Reusable Session
• Reusable Session

For this Informatica Detail Outer Join example, we are going to create Non-reusable Session. Please navigate to 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_Detail_Outer) 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_Detail_Outer) that we created earlier (in Step 3).

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 we assigned the $target variable to Connection Value, we changed the Target Load Type option from Bulk to Normal mode and check marked the Truncate target table option to truncate the existing data from the destination table.

From the below screenshot you can observe that the Informatica Detail Outer Join workflow is a valid one. Now, Let us start the Workflow by navigating to Workflows menu and selecting the Start Workflow option.

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our Informatica Detail Outer Join workflow is executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully performed the Detail Outer Join in Informatica using the Joiner Transformation or not.

The Full Outer Join in Informatica is another option available in Joiner Transformation which is used to return all the existing records from Master table and Detail table.

Let us see the visual representation of the Full Outer Join in Informatica for better understanding.  

In this article, we are going to perform Informatica Full Outer Join on two SQL tables using Joiner Transformation. Before we start designing the Mapping let us look at our two source tables on which we are going to perform Full Outer Join in Informatica.

Department Table inside the Database is:

Employees Table inside the Database is: 

Full Outer Join in Informatica Example

Before we start configuring Informatica Full Join, First connect to the repository service with your Admin Console credentials.

TIP: Please refer to Normal Join, Master Outer Join, and Detail Outer Join articles to understand the remaining Joins.

Step 1: Creating Source Definition for Full Outer Join in Informatica

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this Informatica Full outer join example, we are using Department and Employee tables from SQL Server database as our source definitions. 

Please refer Database Source to understand the steps involved in creating source definition

Step 2: Creating an Informatica Full Outer Join in Target Definition

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

Step 3: Creating Mapping for Full Outer Join in Informatica

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

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

Next, Drag and drop the Employee and Department source definitions from Sources folder to the mapping designer. Once you drag the source, PowerCenter Designer will automatically create the default transformation called source qualifier.

Step 3(a): Creating Joiner Transformation in Informatica

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

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

Once you click on the Create button, the Joiner transformation will be added to the mapping designer. In order to perform Full Outer Join, Joiner Transformation requires some data. So, we have to connect the Source definition with the transformation using the Autolink.. option (or select and Drag required fields).

Double click on the Joiner transformation to configure the join conditions. From the below screenshot you can see the list of available options in the Ports tab:

• M: Please checkmark the Column(s) that you want to use as Master table. From the below screenshot you can observe that Joiner Transformation automatically selected the Employee table as Master table

It is always good practice to select the table holding the least records as Master table. That’s why we are selecting a Department table as our Master table.

NOTE: You don’t have to select each and every column present in the Master table. If you select One column from Employee then Informatica will automatically the remaining columns.

Below Informatica Full outer join screenshot will show you the list of available options in the Properties tab:

• Join Type: In this example, we are performing Full Outer Join so, we are selecting the Full Outer Join from the drop-down list.
• Sorted Input: If we are using the sorted data then check mark this option.

Within the Condition tab, we have to specify the condition. In this example, we are going to perform Full Outer Join on Department and Employee table based on the condition Department. id = Employee.Depart Id. So, click on the New button (beside scissors) to create a new condition and select the columns names from Master and detail list

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

Next, Drag and drop the target definition (Full Outer Join in Informatica) from Targets folder to the mapping designer and connect the Joiner Transformation with the target definition. Please use the Autolink.. option to connect them.

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

Step 4: Create a Workflow for Full Outer Join in Informatica

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

• Creating Workflow manually
• Creating Workflow using Wizard

In this Informatica Full Outer Join 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_Full_Outer) and leave the default settings.

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

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

Step 4(a): Creating Session for Informatica Full Outer Join

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 to the Reusable Session article to understand the steps involved in it.

For this Informatica Full Outer Join example, we are going to create Non-reusable Session. Please navigate to 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_Full_Outer) 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_Full_Outer) that we created earlier (in Step 3).

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 we assigned the $target variable to Connection Value and check marked the Truncate target table option to truncate the existing data from the destination table.

From the below screenshot you can observe that the Full Outer Join in Informatica workflow is a valid one. Now, Let us start the Workflow by navigating to Workflows menu and selecting the Start Workflow option.

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our Informatica Full Outer Join workflow is executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully performed the Full Outer Join in Informatica using the Joiner Transformation or not.

The Lookup Transformation in Informatica is very useful to look up data present in Flat Files, Relational tables and Views. In this article, we will show you, How to perform the lookup operation on the SQL server database table using the Lookup Transformation in Informatica with an example.

Before we start designing the Mapping let us look at our source table on which we are going to perform Informatica Lookup operation and the lookup table  

Our Lookup table will be the table inside the Source Database is:

Our source table will be the below table inside the Database is:

In this Informatica Lookup Transformation example, our task is to compare the Country name present in the Source table with a Lookup table and 

• If they match then we will load them in [Lookup Matched Rows] target table along with Country code present in the Lookup table
• If they don’t match then we will load them in [Lookup Unmatched Rows] target table

TIP: You can extend this functionality by performing some operations on Non-matching records and store them in a matched table using the Union Transformation.

Lookup Transformation in Informatica Example

Before we start configuring the Lookup Transformation in Informatica, First connect to the Informatica 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 as shown below.

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

Step 1: Creating Informatica Lookup Transformation Source Definition

Once you connected successfully, Please navigate to Source Analyzer and define your Sources.

In this Informatica Lookup Transformation example, we are using the 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

Step 2: Creating a Target Definition for Informatica Lookup Transformation

Please navigate to Target Designer and define the Target. In this Informatica Lookup Transformation example, we are using the already created SQL tables ([Lookup Matched Rows] and [Lookup Unmatched Rows]) as our target definitions. Please refer Create Target table using Source Definition to understand the steps involved in creating a target definition

Step 3: Creating Informatica Lookup Transformation Mapping

In order to create a new mapping for Informatica Lookup Transformation, Please navigate to Mappings menu in Menu Bar and select the Create.. option. This will open the Mapping Name window as shown below.

Here, you have to write a unique name for this mapping (m_Lookup_Transformation) and click OK button.

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

Next, Drag and drop the 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.

Step 3(a): Creating Lookup Transformation in Informatica

In order to create Lookup Transformation in Informatica, Please navigate to Transformation menu in Menu Bar and select the Create.. option.

Once you click on the Create.. option, Create Transformation window will be opened as we shown below. Please select the Informatica Lookup Transformation from the drop-down list and specify the unique name (LKP_Country) for this transformation and click on Create button 

Once you click on the Create.. button, a Select Lookup Table for Lookup Transformation window will be opened as shown below. Here, we want to import a relational table from SQL database so, click on Import and select From Relational Table.. option

TIP: If your lookup table is present in Source or Target then select it.

Once you select From Relational Table.. option, Import tables window will be opened. Here we are selecting our lookup table called [LookupTransformationSource]. Please refer Import Data from Relation Database article to understand the steps involved in it. 

Once you click on the Create button, the Informatica Lookup Transformation will be added to the mapping designer. Double click on it to configure its settings.

The list of available options in the Ports tab. We added one new Input port (IN_CountryName) in this Informatica Lookup Transformation.

Within the Properties section, we change the Connection Information and assigned it with the $Source variable.

Within the condition tab we added one condition CountryName = IN_CountryName. Below condition states that it will check the lookup input source (i.e., Country Name) is equal to existing Country Name or Not, and if the condition is TRUE then it will return the corresponding Country Code.

Once you finish configuring the Informatica Lookup transformation, Please drag the Country Name field from the Source Qualifier to the IN_CountryName field in the lookup table

Step 3(b): Creating Router Transformation in Informatica

Please refer Router Transformation article to understand the steps involved in creating it. Once you created it, Please connect the Source qualifier with the router transformation by dragging the required fields.

From the below Informatica Lookup Transformation screenshot you can observe that we are adding all the columns from the Source Qualifier and Cid, Country Name from a Lookup table.

Double click on the Router transformation to add Groups. From the below screenshot, you can observe that we created one group for Non-Matching records and one default group.

Let us provide the expression or condition for the First group (Non-Matching) by clicking the arrow button as we shown below. This will open the Expression Editor to write a custom expression.

Here, we want the records whose Country Name is not present in the Lookup table. So, we are writing the below-shown expression (ISNULL(CountryName). It means all the Null values will be placed in Non Matched Group and Not null values will pass to Default Group.

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

Next, Drag and drop the target definitions ([Lookup Matched Rows] and [Lookup Unmatched Rows]) from Targets folder to the mapping designer as shown below. 

Next, Please connect the Informatica Lookup Transformation (Output Group 1) with the [Lookup Unmatched Rows] table and Default Group with Router [Lookup Matched Rows].

TIP: You can use the Autolink.. option to link those fields.

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

Step 4: Creating a Workflow for Informatica Lookup Transformation

Once we finish creating the Mapping for Informatica Lookup Transformation, we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.

• Creating Workflow manually
• Creating Workflow using Wizard

In this Informatica Lookup Transformation example, we will create the Workflow manually. Once we created the workflow, our next step is to create a session task for our mapping.

Step 4(a): Creating Session for Lookup Transformation in Informatica

There are two types of sessions:

• Non-reusable Session Task: Please refer Session article
• Reusable Session Task: Please refer to Reusable Session article

For this Informatica Lookup Transformation example, we are going to create Non-reusable Session. Please navigate to 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_Lookup_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.

In this Informatica Lookup Transformation example, we are selecting the mapping (m_Lookup_Transformation) that we created earlier (in Step 3).

Double click on the Session Task to configure it. Although we have to configure Sources, targets, and some common properties, we are not going to explain them here. We strictly recommend visiting the Session article to understand the properties.

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

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. And our Informatica Lookup Transformation workflow is executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully stored the unmatched records or not.

Let us open the SQL Server Management Studio to check whether we successfully stored the matched records using the lookup transformation in Informatica or not.

The Unconnected Lookup Transformation in Informatica is nothing but a programming function with parameter and in order to call this unconnected lookup, we have to use the Expression transformation.

In this article, we will show you, How to perform unconnected Lookup Transformation in Informatica with an example. Before we start designing the Mapping let us look at our source table on which we are going to perform Lookup operation and the Lookup table  

Employees Table inside the Source Database is:

Our Lookup table will be the Department Table inside the Database:

In this Informatica unconnected lookup transformation example, our task is to load [First Name] and [Last Name] from the source (Employee table) table into the target table and also [Department Name] from the Department table. We can achieve the same using the Full Outer Join also but we will show you the alternative i.e unconnected Lookup Transformation in Informatica.  

Unconnected Lookup Transformation in Informatica Example

Before we start configuring the unconnected Lookup Transformation in Informatica, First connect to Informatica 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 as shown below.

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

Step 1: Creating Source Definition for Unconnected Lookup Transformation in Informatica

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this Unconnected Lookup in Informatica example, we are using the Employee table from SQL Server database as our source definitions.

Please refer Database Source to understand the steps involved in creating source definition

Step 2: Create a Target Definition for Informatica Unconnected Lookup Transformation

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

Step 3: Create Informatica unconnected Lookup Transformation Mapping

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

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

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

Step 3(a): Creating Lookup Transformation in Informatica

In order to create Lookup Transformation in Informatica, Please navigate to Transformation menu in Menu Bar and select the Create.. option as we shown below.

Once you click on the Create.. option, Create Transformation window will be opened as we shown below. Please select the Lookup Transformation from the drop-down list and specify the unique name (LKP_Department) for this transformation and click on Create button

Once you click on the Create.. button, a Select Lookup Table for Lookup Transformation window will be opened as shown below. Here, we are selecting the department table present in the Source  

Once you click on the Create button, the Lookup Transformation will be added to the mapping designer. Since we are explaining about the unconnected lookup transformation there is no need to connect the transformation with the source qualifier.

The list of available options in the Informatica unconnected lookup transformation Ports tab. Here, we added one new Input port (IN_DeptID) and removed the id from the output port.

Within the condition tab, we added one condition id = IN_DeptID. Below condition states that whenever this lookup transformation is called they have to provide the parameter value of integer data type. 

Once they pass the value, Lookup transformation will check whether the value is equal to id or not, and if the condition is TRUE then it will return the corresponding department name otherwise NULL will be returned.

Step 3(b): Creating Expression Transformation

Please refer Expression Transformation article to understand the steps involved in creating it. Once you created it, Please connect the Source qualifier with the expression transformation by dragging the required fields.

We are adding [First Name], [Last Name] and [DepartID] to the expression transformation. Double click on the Expression transformation to write the custom expressions.

From the below screenshot you can observe that we created New Output Column called Department name. We are going to use this column to store the department name we get from the Informatica unconnected lookup transformation.

Click on the arrow button beside the Mapping Name as we shown below to write the custom expression

This will open the Expression Editor to write a custom expression. Here, we want to get the Department Name from the Lookup Transformation.

To do this, Go to the Functions tab and search for Lookups folder and select the Lookup that we created earlier. As we all know that, we assigned the condition in Lookup transformation. It means we have to provide the parameter value of type integer.

TIP: Lookup transformation will check the condition id = DepartID and if the condition is TRUE then it will return the corresponding department name otherwise NULL will be returned.

Once you finish writing the expressions, Click OK to close the transformation window. Next, Drag and drop the target definition (Unconnected Lookup) from Targets folder to the mapping designer and connect the expression transformation with the target definition. Please use the Autolink.. option to connect them.

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

Step 4: Creating a Workflow for Unconnected Lookup in Informatica

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

• Creating Workflow manually
• Creating Workflow using Wizard

In this Informatica Unconnected Lookup Transformation example, we will create the Workflow manually. Once we created the workflow, our next step is to create a session task for our mapping.

Step 4(a): Creating Session for Unconnected Lookup Transformation in Informatica

There are two types of sessions:

• Non- reusable Session
• Reusable Session

For this Unconnected Lookup in Informatica example, we are going to create Non-reusable Session. Please navigate to 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_Unconnected_Lookup) for the session.

Once you click on the Create button, a new window called Mappings will open. Here you have to select the mapping you want to associate with this session. In this Informatica unconnected lookup transformation, we are selecting the mapping (m_Unconnected_Lookup) that we created earlier (in Step 3).

Double click on the Session Task to configure it. Although we have to configure Sources, targets, and some common properties, we are not going to explain them here. We strictly recommend visiting the Session article to understand the properties.

The unconnected lookup transformation in informatica workflow is a valid one. Now, Let us start the Workflow by navigating to Workflows menu and selecting the Start Workflow option.

Once you select the Start Workflow option, the PowerCenter Workflow monitor will open to monitor the workflow. And our Informatica Unconnected Lookup workflow was executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully stored the stored the information from both Source and lookup table using the unconnected lookup transformation in informatica or not.

The Normalizer Transformation in Informatica is an Active, and Connected transformation. This Informatica Normalizer transformation is used to normalize the denormalized data. Or you can simply say, divide single in a table to multiple rows.

For this Informatica Normalizer transformation example, we are going to use the below show data (Denormalized Source table)  

And the destination tables are:

Normalizer Transformation in Informatica Example

Before we start configuring the Informatica Normalizer Transformation, First let me connect with the repository service. To do so, enter the Admin Console username and password you specified while installing the Server.

Create Normalizer Transformation Source Definition

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this example, we are using the Denormalized Source table from the SQL Server database as our source definitions. Please refer Database Source article 

Create Informatica Normalizer Transformation Target Definition

Please navigate to Target Designer and define the Target. In this Informatica Normalizer Transformation example, we are using the already created SQL tables (Normalized prosecutors and Sales) as our target definition. Please refer Target table using Source Definition to understand the target definition

Create Normalizer Transformation Mapping

To create a new mapping for Informatica Normalizer Transformation, Please navigate to Mappings menu in Menu Bar, and select the Create.. option.

It opens the Mapping Name window to write a unique name (m_normalizer) for this mapping. Next, click the OK button. 

Drag and drop the DenormalizedSource source definitions from Sources folder to the mapping designer. Once you drag the source, the Power Center designer automatically creates the Source Qualifier for you.

Next, let me create a Normalizer Transformation in Informatica. To do so, Please navigate to the Transformation menu and select the Create.. option

It opens the Create Transformation window. Please choose the normalizer transformation and provide the unique name (nrm_ProductSale) and then click on Create button

Now you can see the Informatica Normalizer Transformation in your mapping area. Remember, you can’t simply drag and drop fields from Source Qualifier to Normalizer. So, we have to create the fields inside it explicitly.

Double click on the Informatica Normalizer transformation to edit the Transformation. Next, go to the Normalizer tab, and use the New Field option to add new fields.

From the below screenshot, you can observe that We added 7 new columns using the New Port button. Remember, both the Data type and Precision should match with the input and output fields.

Next, we selected the Year column and clicked the Right Shift button beside the Level.

It creates a Level field, and shift the Year column to Level 2.

Similarly, we added the Sales and Orders columns to Level 2. In our Source table Year, Orders, and Sales are repeating for 4 times (2014, 2015, 2016, and 2017). That’s why we assigned 4 to Occurs. If this is not the case, then use an appropriate number.

Now you can see the Number of Input and Output ports that are automatically created by the Normalizer Transformation in Informatica.

You can see the Informatica Normalizer Transformation will all the required Filed name. Now we have to connect the Input and Outputs to this.

First, we connected the Source Qualifier with the Informatica Normalizer Transformation 

Next, Drag and drop the target definition from the Targets folder to the mapping designer

Connect the Output fields from Normalizer transformation to the target definitions using the Autolink.. option. Before we close the Informatica Normalizer transformation Mapping, Let us validate the mapping by going to Mapping and selecting the Validate option.

Create Normalizer Transformation in Informatica Workflow

After we finish creating the Mapping, we have to create a workflow for it. Power Center Workflow manager provides two approaches to create a workflow.

• Create Workflow Manually
• Create Workflow using Wizard

In this Informatica Normalizer transformation example, we will create the Workflow manually. To do so, Please navigate to Workflows Menu and select the Create option.

It opens the Create Workflow window to provide the unique name (wf_NormalizerTrans) and leave the default settings.

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

Create Normalizer Transformation Session

There are two types of sessions:

• Non-Reusable Session
• Reusable Session

For this Informatica Normalizer transformation example, we will create a Non-reusable Session. To create, Please navigate to Tasks Menu and select the Create option.

Please provide a unique name for this Informatica Normalizer transformation session. Here, we are naming it as s_NormalizerTran. Once you click on the Create button, a new window called Mappings will open. Here, we are selecting the m_normalizer

Please link the Start Task and the session task.

Double click on the Session task will open the Edit Tasks window. Within Properties Tab

$Source connection value: This property will store the relational source information in the $Source variable. So, click on the Arrow we marked below, and select the SQL Tutorial as the source information.

$Target connection value: This property will store the relational target information in the $Target variable. So, click on the Arrow we marked below, and select the SQL Test target as the Target information.

Within the mappings tab, we have to configure the Source, target Connections. First, let us configure the source connections by clicking on the SQ_DenormalizedSource source present in the Sources folder.

Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable that we created before, i.e., $Source

Now, we have to configure the Target Connection. So, click on the NormalizedProducts present in the Targets folder.

Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable $Target

Do the same for the NormalizedSales Target

Next, navigate to Workflows Menu and select the Validate option to validate the Workflow.

Now, Let me start the Informatica Normalizer Transformation Workflow. To do so, navigate to the Workflows menu and select the Start Workflow option.

Once you select the Start Workflow option, the PowerCenter Workflow monitor opened to monitor the workflow.

Let us open the SQL Server Management Studio to check whether we successfully Normalized the data or not. First, look at the products table:

Let me show you the sales.

The Informatica Rank Transformation is similar to SQL RANK function, which is used to select the Top or bottom rank of data. In this article, we are going to explain the steps involved in configuring the Informatica Rank Transformation with Group By along with an example.

For this example, we are going to use the below show data.  

Configure Informatica Rank Transformation with Group By

Before we start configuring the rank transformation, First connect to the Informatica 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 that you specified while installing the Server and click on Connect button as shown below.

TIP: Please refer Rank Transformation in Informatica article to understand the transformation without Grouping

Step 1: Creating Source Definition

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

Step 2: Creating a Target Definition

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

Step 3: Creating Mapping

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

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

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

Step 3(a): Creating Rank Transformation in Informatica

In order to create Rank transformation in Informatica with the group by, Please navigate to the Transformation menu in Menu Bar and select the Create.. option as shown below.

Once you click on the Create.. option, Create Transformation window will be opened as shown below. Please select the Rank Transformation from drop down list and specify the unique name (rnk_Income) for this transformation and click on Create button

Once you click on the Create button, the rank transformation will be added to the mapping designer. In order to rank the data, rank Transformation requires some data. So, we have to connect the Source definition with the transformation by dragging the required fields.

From the above screenshot you an observe the new item called RANKINDEX and this is default port created by the Rank Transformation. This output port will hold the ranking number so, we have to assign this output port to target table rank column.

Double click on the Rank transformation to provide the ranking factor (based on which column you want to rank). Below screenshot will show you the list of available options in the Ports tab:

• R: Please checkmark the Column that you want to use as ranking factor (based on which column you want to rank). For example, If you want to rank the data by yearly Income then you have to checkmark it as we shown below.
• Group By: Please checkmark the Column that you want to use for Group by. In this example, we want to group the Employ table using the Occupation and then we want to rank employees inside the individual group.

Below screenshot will show you the list of available options in the Properties tab.

• Top/Bottom: This will provide two options for the user i.e, Top and Bottom. If you want to rank the data from top to bottom (Z to A) then use the Top option or, if rank the data from bottom to top (A to Z) then use Bottom option.
• Number of Ranks: Please enter the number of rows you want to rank for. By default, the value will be 1 and it means, the rank transformation will select only 1 record.

For this example, we want to rank every record present in Employ table based on their Yearly Income (in descending order). That’s why we selected Top/Bottom property value as Top as and the Number of Ranks property value as 20 (our maximum records are 14)

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

Next, Drag and drop the target definition (Rank Transformation) from Targets folder to the mapping designer and connect the Transformation with the target definition. Please use the Autolink.. option to connect them.

Before we close the Mapping, Let us validate the mapping by going to 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 create a workflow.

• Creating Workflow manually
• Creating Workflow using Wizard

In this 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.

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

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

Step 4(a): Creating Session

There are two types of sessions:

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

For this example, we are going to create Non-reusable Session. Please navigate to 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).

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, we assigned the $target variable to Connection Value, we changed the Target Load Type option from Bulk to Normal mode and check marked the Truncate target table option to truncate the existing data from the destination table.

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

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our workflow is executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully ranked the records using Yearly Income using the Informatica Rank Transformation with a group by or not.

We can achieve the above output using the following SQL RANK Function Query:

SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,RANK() OVER (
                     PARTITION BY Occupation 
                     ORDER BY [YearlyIncome] DESC
          	   ) AS RANK
  FROM [Customers]

The Sequence Generator Transformation in Informatica is a connected transformation that generates numeric values. We can mainly use this transformation to generate primary keys, 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, 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

Configure Sequence Generator Transformation in Informatica

Before we start configuring the sequence generator transformation in Informatica, 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.  

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

Once you 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

Step 2: Create Informatica Sequence Generator Transformation 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

Creating Mapping for Sequence Generator Transformation in Informatica

In order to create a new mapping, Please navigate to 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 OK button. 

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

Next, Drag and drop the [Employ] source definition from 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 Sequence Generator transformation in Informatica, Please navigate to Transformation menu in Menu Bar and select the Create.. option as we shown below.

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

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

From the above screenshot, you can observe that 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 the 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.

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 unchecked any column then, that column will not be available to load in the target table.

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

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

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

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

Step 4: Creating a Workflow for Sequence Generator Transformation in Informatica

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

• Creating Workflow manually
• Creating Workflow using Wizard

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.

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

NOTE: We strictly recommend to refer 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 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).

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. 

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our workflow is executed without any errors.

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

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

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

The Sorter Transformation in Informatica is used to sort the source data in either Ascending or Descending order, similar to SQL command ORDER BY Statement.

In this section, we are going to demonstrate the steps involved in configuring the Informatica Sorter Transformation with an example. For this example, we are going to use the below show data  

Configure Sorter Transformation in Informatica

Before we start the Informatica Sorter Transformation demo, First, connect to the Informatica repository service. For this, provide the Admin Console credentials (Username and Password) and click on the Connect button

Step 1: Creating Source Definition for Sorter Transformation in Informatica

Once you connected successfully, Please navigate to Source Analyzer to set our Sources. In this Informatica Sorter Transformation example, we are using the already created SQL table (Fact Internet Sales) as our source definition. Please refer Database Source to know the steps involved in creating a source definition

Step 2: Create Mapping for Sorter Transformation in Informatica

To create a new mapping for Informatica Sorter Transformation, Please navigate to the Mappings menu in Menu Bar and select the Create.. option. Here, you have to write a unique name for this Mapping and click the OK button.  

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

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

Step 2(a): Creating Sorter Transformation in Informatica

To create a sorter transformation in Informatica, Please navigate to the Transformation menu in Menu Bar and select the Create.. option.  

It opens a Create Transformation window, as shown below.

Please click on the arrow to select the required transformation from the drop-down list. Let us choose the Informatica Sorter transformation

Next, Please specify the unique name for this Informatica Sorter transformation and click on Create button

Once you click on the Create button, the Informatica Sorter transformation will be added to the mapping designer. In order to perform sorting, transformation requires some data so, we have to connect it with Source definition.

The PowerCenter Designer provides multiple ways to connect the source qualifier with the newly created Sorter Transformation in Informatica. For this example, we are right-clicking on the empty space and selecting the Autolink.. option from that context menu.

NOTE: The Mapping designer allows us to delete the wrong mapping by selecting the link and deleting it. 

From the above screenshot, you can observe that we successfully connected the source qualifier to the sorter transformation.

Double click on the Informatica Sorter transformation to configure the sort properties. Below window will show you 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 sorter transformation to a more meaningful name.
• Make Reusable: If you check mark this option then, this transformation will become reusable transformation.
• Description: Please provide the valid description about this transformation.

The below screenshot will show you the list of available options in the Ports tab of an Informatica Sorter Transformation: 

• 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: Columns that are check marked under this section are the Sorter transformation Input columns.
• O: Columns that are check marked under this section are the Sorter transformation Output columns. If you unchecked any column then, that column will not be available to load in the target table.
• Key: Please checkmark the Column(s) that you want to use as Sort column. For example, If you want to sort the data by Unit Price and Order Quantity then you have to checkmark both of them.
• Direction: Here we have to select the Sort direction. This Informatica Sorter Transformation provides two options: Ascending order (A to Z) and Descending Order(Z to A)

To understand the Sorter Transformation in informatica, we are sorting one column with Ascending order and another with Descending order. From the below screenshot you can observe that, we are sorting the [Sales Amount] in Descending Order

Next, we are sorting the [Freight] in Ascending order. It means,

• First data is sorted by the [Sales Amount] in Descending Order and then
• Second, data will be sorted by the [Freight] in Ascending order.

Click OK to finish configuring the Sorter Transformation. 

Step 2(b): Target Definition for Sorted Transformation in Informatica

First, Drag and drop the target definition from the Targets folder to the mapping designer. Next, We have to connect it with the target definition. Please use the Autolink.. option to connect them.

TIP: Please refer to Create Target Table article to understand importing the target definition from the SQL Database.

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

Step 3: Creating a Workflow for Sorter Transformation in Informatica

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

• Creating Workflow manually
• Creating Workflow using Wizard

In this Informatica Sorter Transformation example, we created the Workflow manually. We strictly recommend to refer Workflow article to understand the steps involved in creating Workflow manually. Once we created the workflow, our next step is to create a session.

Step 3(a): Creating an Informatica Sorter Transformation Session

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 Sorted Transformation in Informatica example, we are going to create Non-reusable Session. In order to create Non-reusable Session, Please navigate to 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 for the session.

Once you click on the Create button, a new window called Mappings will open. Here we 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 that we created earlier (in Step 2).

Although we have to configure sources, Target, and some common properties, we are explaining only two properties. We strictly recommend visiting the Session article to understand the remaining properties.

From the below screenshot you can observe that, we changed the Target Load Type option from Bulk to Normal mode and check marked the Truncate target table option to truncate the existing data from the destination table.

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

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our workflow is executed without any errors.

Let us open the Management Studio to check whether we successfully transferred the sort data from Adventure Works DW to the target database. Please refer ORDER BY Statement article in SQL Server Tutorial.

In this article, we are going to explain how to remove duplicates using Sorter Transformation in Informatica with an example. For this example, we are going to use the below show data

From the above screenshot, you can observe that we have 60398 records with many duplicates. Our task is to select distinct records or remove duplicates using sorter transformation in Informatica. 

Remove Duplicates Using Sorter Transformation in Informatica

Before we start removing duplicates using sorter, connect to Informatica repository service. For this, we have to provide the Admin Console credentials. So, Please provide the appropriate Username and Password and click on the Connect button.

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

Step 1: Creating Source Definition

Once you connected successfully, Please navigate to Source Analyzer and define our Sources. In this example, we are using the already created SQL table (Internet Sales) as our source definition. Please refer Database Source to understand the steps involved in creating a source definition  

Step 2: Creating a Target Definition

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

Step 3: Remove Duplicates using Sorter Transformation in Informatica Mapping

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

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

Next, Drag and drop the [Internet Sales] source definition from Sources folder to the mapping designer and create a sorter transformation. The sorter transformation requires some data to remove the duplicates. So, we have to connect the Source definition with the transformation using the Autolink.. option.

TIP: Please refer to Sorter Transformation article to understand the steps involved in configuring Sorter transformation.

Double click on the Sorter transformation to configure the properties. From the below screenshot, you can observe the list of available properties in Properties tab. In this example, we want to remove duplicate records so, checkmark the Distinct property.

TIP: This will act as SQL SELECT DISTINCT Statement

Next, If you observe the Ports tab, Sorter Transformation is sorting all the input columns in ascending order. Remember, you don’t have to do anything here.

Click the OK button to finish configuring the properties.

Next, Drag and drop the [Duplicate Sorter Transformation in Informatica] target definition from Targets folder to the mapping designer and connect the Sorter Transformation with the target definition. Please use the Autolink.. option to connect them.

Before we close the Mapping, Let us validate the mapping by going to 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 to remove Duplicates using Sorter Transformation in Informatica. PowerCenter Workflow manager provides two approaches to create a workflow.

• Creating Workflow manually
• Creating Workflow using Wizard

In this example, we created the Workflow manually. Once we created the workflow, our next step is to create a session.

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

Step 4(a): Creating Session

There are two types of sessions:

• Non-reusable Session Task: Please refer Session article
• Reusable Session Task: Please refer Reusable Session article

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

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

Although we have to configure sources, Target, and some common properties, we are not going to explain them here. Please refer to Session article to understand the properties.

From the below screenshot, you can observe that the Remove Duplicates Using Sorter 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.

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot, you can observe that our workflow executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully remove duplicates / transferred the distinct records from Adventure Works DW to the target database. 

From the above screenshot, you can observe that there are only 158 distinct records out of 60398. Let see the same by writing the Distinct query in SQL

The Source Qualifier Transformation in Informatica is an Active, and Connected transformation. While creating a mapping, this is the default transformation generated by the power Center Designer.

This Informatica Source Qualifier Transformation converts the source data types to the native data types. In real-time, you can use this Informatica Source Qualifier Transformation to Join Multiple Sources, Write a custom query, Filter Rows, or Select Unique Records  

For this Informatica Source Qualifier Transformation example, we are going to use the below show data (Employee table)

and the Department table

and the Destination table is Source Qualifier. As you can see, it is an Empty table  

Configure Source Qualifier Transformation in Informatica

Before we start configuring the Informatica source qualifier transformation, First connect to the repository service by providing the Admin Console credential.

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

Create Source Qualifier Transformation Source Definition

Navigate to Analyzer and define your Sources. As we said before, we are using Employee and Department tables from the SQL Server database as our Informatica source definitions. Please refer Database to understand the creation of the source definition 

Create Informatica Source Qualifier Target Definition

Please navigate to Target Designer to define the Target. In this example, we are using the existing SQL table (SourceQualifier) as our target definition. You can refer to Create Target table using Definition to understand the process of creating a target definition

Create Source Qualifier Transformation in Informatica Mapping

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

Create Source Qualifier Transformation in Informatica

Drag and drop the Employee, and Department from Sources folder to the mapping designer. Once you drag the source, Power Center designer will automatically create the source qualifier transformation (default transformation) for you.

Let me remove the Department Table Source Qualifier

Next, we are adding the Department columns to the Employee source qualifier. This is one way of adding two tables

Let me remove the SQ_Employee (Employee Source Qualifier as well).

Create Informatica Source Qualifier Transformation

To explicitly create Source Qualifier transformation in Informatica, Please navigate to Transformation menu in Menu Bar. Next, select the Create.. option will open the Create Transformation window as shown below.

Please select the Source Qualifier Transformation from the drop-down list and provide the unique name (SQ_EmployeeDetails) for this transformation and click on Create button

That will open the below shown window as we shown below. Click OK to select the Department table and Employee table.

Now you can see the same image that you have seen before

Double click on the Source Qualifier transformation to see, and alter the properties. Below screenshot shows you the list of available properties in the Transformation tab:

• Select Transformation: By default, it will select the transformation you selected (or clicked on).
• Rename: This button helps you to rename the Source Qualifier transformation name.
• Description: Use this place to provide a valid description of this transformation.

Below screenshot shows you the available options in the Ports tab:

• Port Name: List of available column names. Use New column button to add new columns, scissors button to delete the unwanted columns. Here, we are deleting the DeptID column because it is a duplicate column
• I: Input columns.
• O: Here, Columns that are check-marked are the Source Qualifier transformation Output columns. If you unchecked any column then, that column will not be available to load in a target table.

Under Properties we have:

• SQL Query: Use this property to write a Custom SQL Query. This query will replace the default SQL Query generated by it.
• User Defined Join: Use this property to Join multiple data sources.
• Filter: This property is used to filter the selected rows. It is Equal to the Where Clause in SQL, or any database.
• Number of Sorted Ports: This is used to sort the data. Similar to Order By Clause
• Tracing Level: Specify how you want to trace (Detailed)
• Select Distinct: This property is used to select the Distinct (Unique records). Similar to SQL DISTINCT
• Pre SQL: Use this property to run the Command against the Source before the Integration Service Starts.
• Post SQL: Use this property to run the Command against the Source After the Integration Service Ends.

Let me use the User Defined Join property to Join the Employee table and Department table

As you can see we are using the below condition. You can refer SQL Joins article to understand the condition, or Joiner Transformation to understand the Joining concept. 

Next, Drag and drop the target definition (SourceQualifier) from Targets folder to the mapping designer. Next, connect the source qualifier Transformation with the target definition. Please use the Autolink.. option to connect them.

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

Create Source Qualifier Transformation in Informatica Workflow

After you finish creating the Mapping, we have to create the workflow for it. Power Center Workflow manager provides two approaches to create a workflow.

• Create Workflow Manually
• Create Workflow using Wizard

In this example, we will create the Workflow manually. To do so, 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_Source_Qualifier) and leave the default settings.

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

Create Source Qualifier Session

There are two types of sessions: 

• Non-resuable Session
• Reusable Session

For this example we created a Reusable Session, and named it as s_EmployeeDept_fromSQL_ToSQL for the session.Please link the Start Task, and the Session Task. Next, navigate to Workflows Menu and select the Validate option to validate the Workflow.

From the above screenshot you can observe that the Source Qualifier Transformation in Informatica workflow is a valid one.

Now, Let me start the Workflow. To do so, navigate to Workflows menu, and select the Start Workflow option.

Let us open the SQL Server to check whether we successfully performed the Joining using the Source Qualifier Transformation in Informatica

The Stored Procedure Transformation in Informatica helps you to use or call Stored procedures inside the Informatica Workflow. Using this transformation, you can operate Drop or Recreate Indexes, Check for Space in a Database, performing Complex Calculations.

This article shows how to use this Informatica Stored procedure Transformation to Rollback the passing records with an example. For this example, we are going to use SP Source and Destination tables.  

And the Stored procedure that we are going to use is:

From the above screenshot, you can see, that the stored procedure has one input parameter and one output parameter

TIP: We explained everything about the Stored procedure and its functionalities in the SQL Server tutorial. I suggest you refer Stored Procedures article to understand the topic.  

Stored Procedure Transformation in Informatica Example

Let us see how to use Stored Procedure Transformation in Informatica to call stored procedures inside a Workflow. Before we start configuring, First, let me connect with the Informatica repository service.

To do so, we have to provide the Admin Console credentials. So, Please provide the appropriate Username and Password you specified while installing the Server.

Source Definition for Stored Procedure Transformation in Informatica

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. Here, we are using the Sp Source table from the SQL database as our source definitions. Please refer Database Source to understand the steps in creating source definition  

Create Target Definition for Stored Procedure Transformation

Please navigate to Target Designer to define the Informatica Stored Procedure Transformation Target. In this example, we are using the existing table (SP Destination) as our target definition. You can refer to Create Target Table to understand the process of creating a target definition

Create Mapping for Informatica Stored Procedure Transformation

To create a new mapping, Please navigate to the Mappings menu in Menu Bar and select the Create.. option.

Once you select the Create.. option, a new Mapping Name window will open. Let me provide m_StoreProc as the mapping name and click the OK button.

TIP: Please refer Mapping article to understand the procedure to create Mapping

Drag and drop the SP Source from Sources folder to the mapping designer. Once you drag the source, Power Center Designer will automatically create the Source Qualifier Transformation (default transformation) for you.

Create Informatica Stored Procedure Transformation

To create Stored Procedure Transformation in Informatica, Please navigate to the Transformation menu in Menu Bar. Next, select the Create.. option

Selecting the Create.. option will open the Create Transformation window. Please select the Stored procedure Transformation from the drop-down list and provide a unique name (sp_Testing) and click on Create button

Clicking the Create button will pop up a new window called Import Stored Procedure. Please select the ODBC connection that will connect with the Source. It is where our Stored procedure was created. To create a new ODBC Connection, please refer to the ODBC Connection article.

From the below screenshot, see that we are selecting the spUpdateCust stored procedure.

By clicking OK will create a Informatica Stored Procedure Transformation for you. As you can see from the below screenshot, it has three ports: Return_value, EmpId, and New Income

Next, Drag and drop the target definition (SPDestination) from the Targets folder to the mapping designer.

Next, we are connecting the EmpId from Source Qualifier to Transformation. Because spUpdateCust accepts one input parameter (EmpId) to return New Income output.

Double click on the Informatica Stored Procedure Transformation to see and alter the properties. Below screenshot shows you the list of available properties in the Transformation tab:

• Select Transformation: By default, it will select the transformation you selected (or clicked on).
• Rename: This button helps you to rename the Transformation name.
• Description: Use this place to provide a valid description of this transformation.

Below screenshot shows you the available options in the Informatica stored procedure transformation Ports tab:

• Port Name: List of available column names. Use the New column button to add new columns, scissors button to delete the unwanted columns.
• I: Input columns.
• O: Here, Columns that are check-marked are the Output columns.

Within the properties tab, click on the Arrow button beside the Connection Information type, and use the Connection variable $Source because we used the SP that exists in information Source.

Drag and drop all the fields that exist in source qualifier Transformation to target definition. Please use the Autolink.. option to connect them. Next, connect the NewIncome field in transformation with target definition.

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

Create Workflow

After you finish creating the Informatica Stored Procedure Transformation Mapping, we have to create the workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.

• Create Workflow Manually
• Create Workflow using Wizard

In this example, we will create the Workflow manually. To do so, Please navigate to Workflows Menu and select the Create option.

It opens the Create Workflow window. Please provide the unique name (wf_StoredProc) and leave the default settings.

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

Create Session for Stored procedure Transformation in Informatica

There are two types of sessions:

• Non-Reusable Session
• Reusable Session

For this Informatica Stored Procedure Transformation example, we created a Non-reusable Session. To create Non-reusable Session, Please navigate to Tasks Menu and select the Create option.

Please provide a unique name for this session. Here, we are naming it as s_StoredProc. Once you click on the Create button, a new window called Mappings will open. Here you have to select the Mapping that you want to associate with this session, i.e., m_Storedproc.

Double click on the Session task will open the Edit Tasks window. Within Properties Tab

$Source connection value: This property will store the relational source information in the $Source variable. So, click on the Arrow we marked below, and select the Source as the source information.

$Target connection value: This property will store the relational target information in the $Target variable. So, click on the Arrow we marked below, and select the Target as the Target information.

Within the mappings tab, we have to configure the Source and Target Connections. First, let us configure the source connections by clicking on the SQ_SPSource source present in the Sources folder.

Within the Connections, click on the Arrow button beside the Relational type. And use the Connection variable that we created in our previous step i.e., $Source

Now, we have to configure the Informatica Stored Procedure Transformation Target Connection. So, click on the SPDestination present in the Targets folder. Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable $Target

Here, we can also configure the connection information. To do so, click on the SP_Testing present in the Transformations folder.

Next, navigate to Workflows Menu and select the Validate option to validate the Workflow.

Now, Let me start the Informatica Stored Procedure Transformation Workflow. To do so, navigate to the Workflows menu and select the Start Workflow option.

Let us open the Management Studio and write the following SQL Query.

The Transaction Control Transformation in Informatica allows you to control the Transactions within the Transformation. Using this transaction Control Transformation, you can Commit or Rollback the records (or rows) passing through it.

For this Informatica Transaction Control Transformation example, we are going to use SP Source and Destination tables. 

Transaction Control Transformation in Informatica Example

In this example, we will show you how to use Transaction Control Transformation in Informatica to perform the Rollback Transaction. Before we start configuring, First, let me connect with the repository service.

To do so, we have to provide the Admin Console credentials. So, Please provide the appropriate Username and Password you specified while installing the Server.

TIP:  We explained everything about the Transactions and its functionalities in the SQL Transaction article.  

Source Definition for Transaction Control Transformation in Informatica

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

Create a Target Definition for Informatica Transaction Control Transformation

Please navigate to Target Designer to define the Target. In this example, we are using the existing SQL table (SP Destination) as our target definition. You can refer to Create Target Table to understand the process of creating a target definition

Create Mapping for Transaction Control Transformation in Informatica

To create a new mapping, Please navigate to the Mappings menu in Menu Bar and select the Create.. option. 

Once you select the Create.. option, a new Mapping Name window will be opened. Let me provide m_Transaction as the mapping name and click OK button.

Drag and drop the SP Source from Sources folder to the mapping designer. Once you drag the source, Power Center Designer will automatically create the Source Qualifier Transformation (default transformation) for you.

Create Informatica Transaction Control Transformation

To create Transaction Control Transformation in Informatica, Please navigate to the Transformation menu in Menu Bar. Next, select the Create.. option

Selecting the Create.. option will open the Create Transformation window. Please select the Transaction Control Transformation from the drop-down list, and provide a unique name (Tran_Employee), and click on Create button

By clicking OK will create a Transaction Control Transformation for you.

Let me drag all the fields that are available in source definition to Transaction Control Transformation. Next, Drag and drop the target definition (SP Destination) from the Targets folder to the mapping designer. Lastly, connect the Transaction Control Transformation fields with the target definition using the Autolink.. option. 

Double click on the Informatica Transaction Control Transformation to edit or configure the transformation. Please go to the Properties tab, and click on the Arrow beside the Transaction Control Condition to apply transactions at the mapping level.

Once you click the Arrow, an expression window will open. The Expression window in Transaction Control Transformation uses the IIF Function to test each row against the condition.

The Informatica Transaction Control Transformation provides the following built-in variables to apply transactions. For the demonstration purpose, let us take the above specified SP Source table and condition is EmpID = 5. 

• TC_CONTINUE_TRANSACTION: This is the default value for this option. It does not perform any change for this row.
• TC_COMMIT_BEFORE: This is nothing but a Commit transaction in any relational database. If the condition is true, then it will first Commit the transaction. Next, it will begin a new transaction and writes the current row. Example: It will commit records up to EmpID 4 and start a new transaction to commit records from 5
• TC_COMMIT_AFTER: If the condition is true, then it will first write the current row and Commit the transaction. Next, it will begin a new transaction. Example: It will write and commit records up to EmpID 5 and begin a new transaction to commit records from 6
• TC_ROLLBACK_BEFORE: This is nothing a Rollback transaction in any relational database. If the condition is true, it will first Rollback the transaction. Next, it will begin a new transaction and writes the current row. Example: It will rollback records up to EmpID 4 and begin a new transaction to commit records from 5
• TC_ROLLBACK_AFTER: If the condition is true, it will first write the current row and then Rollback the transaction. Next, it will begin a new transaction. Example: It will rollback records up to EmpID 5 and begin a new transaction to commit records from 6

Click Apply and then click OK to close the Informatica Transaction Control Transformation window.

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

Create a Workflow for Transaction Control Transformation in Informatica

After you finish creating the Informatica Transaction Control Transformation Mapping, we have to create the Workflow for it. PowerCenter Workflow manager provides two approaches to create a workflow.

• Create Workflow Manually
• Create Workflow using Wizard

In this Informatica Transaction Control Transformation example, we will create the Workflow manually. To do so, Please navigate to Workflows Menu and select the Create option.

It will open the Create Workflow window. Please provide the unique name (wf_Transaction) and leave the default settings.

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

Create a Session for Transaction Control Transformation in Informatica

There are two types of sessions:

• Non-Reusable Session
• Reusable Session

For this Informatica Transaction Control Transformation example, we created a Non-reusable Session. To create Non-reusable Session, Please navigate to Tasks Menu and select the Create option as shown below.

Here, we are naming this session as s_Transaction. Once you click on the Create button, a new window called Mappings will open. Here, select the Mapping (m_Transaction) that you want to associate with this session. 

Double click on the Session task will open the Edit Tasks window. Within the mappings tab, we have to configure the Source, Target Connections.

First, let us configure the source connections by clicking on the SQ_SPSource source present in the Sources folder. Within the Connections, click on the Arrow button beside the Relational type, and select the Database Object (Database Name) i.e., Source

Now, we have to configure the Target Connection. So, click on the SPDestination present in the Targets folder. Within the Connections, click on the Arrow button beside the Relational type, and select the Database Object (Target Database Name) i.e., target 

Please change the Target Load Type to Normal or Bulk as per your requirements. Let me checkmark the Truncate target table option as well.

Next, navigate to Workflows Menu and select the Validate option to validate the Workflow.

Now, Let me start the Informatica Transaction Control Transformation Workflow. To do so, navigate to the Workflows menu and select the Start Workflow option. 

Let us open the SQL Server Management Studio and write the following SQL Query to check whether we successfully transferred the data from a source or not. As you see that our target table is displaying records where EmpID = 10. Because Rollback will start before processing the record whose EmpID, is 10

This time we will use the TC_ROLLBACK_AFTER as the second argument in IIF. It means the Rollback option will perform after passing the EmpID 10.

As you can see, our target table is displaying records where EmpID is greater than 10

The Update Strategy Transformation in Informatica is an Active and Connected transformation. It is useful to update, insert, or delete the records in a target based on source table data. And the beauty of this transformation is: you can restrict the records from not reaching into the target table.

This article explains how to configure Informatica Update Strategy Transformation. Or, the steps involved in configuring the Update Strategy to update the records in the target table. 

For this Informatica Update Strategy Transformation example, we are going to use the below show data (Customer Record table)

And the destination table is:

Update Strategy Transformation in Informatica Example

If you compare the Source table with the destination table, there are multiple changes in Email Address and Professional columns. In this example, our task is to use the Informatica Update Strategy Transformation to update the changes in the destination table. 

Before we start configuring the Informatica Update Strategy Transformation, First let me connect with the repository service. To do so, enter the Admin Console username and password you specified while installing the Server.

Create  Informatica Update Strategy Transformation Source Definition

Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this example, we are using the [Customer Record] table from the SQL Server database as our source definitions. Please refer Database Source article

Create Informatica Update Strategy Transformation Target Definition

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

Create Update Strategy in Informatica Mapping

To create a new mapping, Please navigate to Mappings menu in Menu Bar, and select the Create.. option.

It opens the Mapping Name window to write a unique name for this mapping. Let me write m_update_trans and click the OK button.

Drag and drop the Customer record source definitions from Sources folder to the mapping designer. Once you drag the source, the Power Center designer automatically creates the Source Qualifier Transformation for you.

Creating Update Strategy Transformation in Informatica

To create Update Strategy Transformation in Informatica, Please navigate to the Transformation menu and select the Create.. option.

Once you click on the Create.. option, Create Transformation window will open. Please select the Update Strategy Transformation from the drop-down list. Next, specify the unique name (ups_Customers) and then click on Create button

Once you click on the Create button, Update Strategy Transformation added to the mapping designer. 

Next, Drag and drop the target definition (DupCust) from Targets folder to the mapping designer

In this example, we want to update the changes that happened in Email Address and Professional Columns. So, drag those two fields along with Key column to the Update Strategy Transformation in Informatica

Double click on the Informatica Update Strategy Transformation to configure it. From the below screenshot, you can see the list of available properties in the Update Strategy Transformation tab: 

• Select Transformation: By default, it will select the transformation you clicked on.
• Rename: This button will help you to rename the Update Strategy Transformation to a more meaningful name.
• Make Reusable: If you checkmark this option, this transformation will become a reusable transformation.
• Description: Please provide a valid description.

The below screenshot shows the list of available options in the Informatica Update Strategy Transformation Ports tab:

• Port Name: List of available column names inside this Informatica Update Strategy. By clicking the New column button, you can add new columns, and by clicking the scissors button, you can delete the unwanted columns.
• I: Columns that are check-marked under this section are Input columns.
• O: Columns that are check-marked under this section are Output columns. If you unchecked any column, that column would not be available to load it in the target table.

Under the Informatica Update Strategy Transformation properties tab, we had:

• Update Strategy Expression: Here you have to specify whether you want to perform Insert, Delete, or an Update
  • DD_INSERT: Numeric Value is 0. Used to insert records.
  • DD_UPDATE: This is used to update rows, and the Numeric Value is 1.
  • DD_DELETE: Numeric Value is 2. Used to delete rows.
  • DD_REJECT: Numeric Value is 3. Used to reject rows.
• Forward Rejected Row: If you want to forward the rejected rows to the next transformation, then you have to checkmark this option.
• Tracing Level: Keep this to default Normal.

It will open the Expression Editor to write a custom expression. Here, we are using the DD_UPDATE expression 

Click Apply and click OK to close the window.

Next, connect the Informatica Update Strategy transformation with the target definition using the Autolink.. option.

Before we close the Mapping, Let us validate the Informatica Update Strategy Transformation mapping by going to Mapping and chose the Validate option. 

Create Update Strategy Transformation Workflow

After we finish creating the Mapping, we have to create a workflow for it. It provides two approaches to creating a workflow.

• Create Workflow Manually
• Create Workflow using Wizard

In this Informatica Update Strategy Transformation example, we will create the Workflow manually. So, navigate to Workflows Menu and select the Create option.

It opens the Create Workflow window. Please provide the unique name (wf_update_tran) and leave the default settings. 

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

Create a Session for Update Strategy Transformation

There are two types of sessions:

• Non-Reusable Session
• Reusable Session

For this Informatica Update Strategy Transformation example, we will create a Non-reusable Session. For this, go to Tasks Menu and select the Create option. 

Please provide a unique name (s_update_tran) for this session. Once you click on the Create button, a new window called Mappings will open. Here, we are selecting the m_update_trans

Please link the Start Task with a newly created session task

Double click on the Session task will open the Edit Tasks window. Within Properties Tab 

Treat Source Rows as: By default, Data-Driven is selected in this case. If not, please change it to Data-Driven. Remember, the Data-Driven option means, instead of performing default insertion, it will use the Update Strategy Transformation option to load the target table.

$Source connection value: This property will store the relational source information in the $Source variable. So, click on the Arrow we marked below, and select the SQL Tutorial as the source information.

$Target connection value: This property will store the relational target information in the $Target variable. So, click on the Arrow we marked below, and select the SQL Test target as the Target information.

Within the mappings tab, we have to configure the Source, target Connections. First, let us configure the source connections by clicking on the SQ_CustomerRecord source present in the Sources folder.

Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable that we created in our previous step, i.e., $Source

Now, we have to configure the Target Connection. So, click on the DupCust present in the Targets folder. Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable $Target

Next, Let me start the Informatica Update Strategy Transformation Workflow. To do so, navigate to the Workflows menu and select the Start Workflow option.

Let us open the SQL Server Management Studio to check whether we successfully performed the Informatica update strategy.

In this article, we are going to explain, How to achieve Informatica Update Strategy using Session Properties. Or, the steps involved in configuring the Session Properties to achieve the Update Strategy without using any Update Strategy Transformation with an example.

In Informatica, you can use the Session Properties to achieve the update strategy. But, this approach is one of the worst approaches in real-time. Because, instead of Lookup this will blindly update existing records (even there is no change), or insert each and every record. This approach is fine for the small table. 

For this example, we are going to use the below show data (Employee Duplicate table)

And the destination table is:

As you can that the destination table has 1 record and two extra columns. These extra columns will help you to understand the problems you might face in this approach.  

Informatica Update Strategy using Session Properties

Before we start configuring the Session Properties, First let me connect with the Informatica repository service. In order to do so, we have to provide the Admin Console credentials. So, Please provide the appropriate Username and Password, and click the Connect button.

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

Step 1: Create a Source Definition

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

Step 2: Create Target Definition

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

Step 3: Create a Mapping

To create a new mapping, Please navigate to Mappings menu in Menu Bar, and select the Create.. option.

This will open the Mapping Name window to write a unique name for this mapping. Let me write m_update_with_session and click OK button.

TIP: Please refer Mapping article to understand the procedure to create Mapping

Drag and drop the Employee Duplicate source definitions from Sources folder to the mapping designer. Once you drag the source, Power Center designer will automatically create the source qualifier for you. I suggest you refer the Source Qualifier Transformation article.

Next, create an Expression Transformation, and Drag all the fields from Source Qualifier to Expression Transformation. I Suggest you refer Expression Transformation article for better understanding.

Double click on the Expression transformation to add new fields, or write to custom expressions. From the below screenshot you can observe that We added 2 new columns using the New Port button. We are going to use these new columns to store the Created Date and Modified Date

Let us write the custom expression. To do this, Click on the arrow button beside the CreatedOn.

TIP: All these new columns are output ports. So, please check mark O

This will open the Expression Editor to write a custom expression. Here, we want the system date so, write SYSDATE.

From the below screenshot you can observe that We did the same for the UpdatedOn column

Next, Drag and drop the target definition from Targets folder to the mapping designer, and connected the Expression transformation with the target definition. Please use the Autolink.. option to connect them.

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

Step 4: Create a Workflow

After we finish creating the Mapping, we have to create a workflow for it. Power Center Workflow manager provides two approaches to create a workflow.

• Create Workflow Manually
• Create Workflow using Wizard

In this example, we will create the Workflow manually. To do so, 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_update_using_session) and leave the default settings.

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

Step 4(a): Create a Session

There are two types of sessions:

• Non-Reusable Session
• Reusable Session

For this example, we will create a Non-reusable Session. In order to create the same, Please navigate to Tasks Menu and select the Create option as shown below.

Please provide a unique name for this session. Here, we are naming it as s_update_using_session. Once you click on the Create button, a new window called Mappings will be opened. Here, we are selecting the m_update_with_session

Double click on the Session task will open the Edit Tasks window. Within Properties Tab 

$Source connection value: This property will store the relational source information in $Source variable. So, click on the Arrow we marked below, and select the SQL Tutorial as the source information.

$Target connection value: This property will store the relational target information in $Target variable. So, click on the Arrow we marked below, and select the SQL Test target as the Target information.

Treat Source Rows as: Change the default Insert to Update. It means, instead of performing the only insertion, it will perform update too.

Within the mappings tab, we have to configure the Source, target Connections. First, let us configure the source connections by clicking on the SQ_EmployeeDuplicate source present in the Sources folder.

Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable that we created in our previous step i.e., $Source

Now, we have to configure the Target Connection. So, click on the EmployeeDup present in the Targets folder. Within the Connections, click on the Arrow button beside the Relational type, and use the Connection variable $Target

Next, checkmark the Update else Insert.

Update else Insert: If the records already exist then it will perform UPDATE on that records. And, if the records do not exist then it will perform INSERT

Please link the Start Task and the session task.

Next, navigate to Workflows Menu and select the Validate option to validate the Workflow. Now, Let me start the Workflow. To do so, navigate to the Workflows menu, and select the Start Workflow option.

Once you select the Start Workflow option, the PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that our workflow is executed without any errors.

Let us open the SQL Server Management Studio to check whether we successfully performed the update strategy. If you look at the first record, this record is updated along with the insertion. In a simple language, we can say, this approach is simply replacing all the existing target table records with the source table records.

Let me update Yearly Income for EmpID 3 and add one more record.

From the below screenshot you can observe that our workflow is executed without any errors.

As you can see, again all the records are inserted freshly.

The XML Generator Transformation in Informatica is an Active and Connected transformation. This transformation is useful in generating XML files inside a pipeline.

In this article, we will show you how to use XML Generator Transformation in Informatica to generate an XML file from the Database table with an example.

In this article, we are going to explain, How to use Pre SQL and Post SQL in Informatica. Or, the steps involved in configuring the Pre and Post SQL in Source Qualifier Transformation with an example.

For this Informatica Pre and Post SQL example, we are going to use the below show data (EmpDetails table)  

And the Destination table is Pre and Post SQL Employees. As you can see, it is an Empty table

Pre SQL and Post SQL in Informatica Example

Before we start configuring the Pre and Post SQL in Informatica, First connect to the Informatica repository service by providing the Admin Console  Username and password that you specified while installing the Server.

Create Pre and Post SQL in Informatica Source Definition

Navigate to Source Analyzer and define your Sources. Here, we are using the EmpDetails table from the SQL Server database as our source definitions. I suggest you refer to Database Source to understand the creation of the source definition  

Create Pre and Post SQL in Informatica Target Definition

Please navigate to Target Designer to define the Target. In this example, we are using the existing SQL table as our target definition. You can refer to Create Target table using Source Definition to understand the process of creating a target definition

Create Informatica Pre and Post SQL Mapping

To create a new mapping, Please navigate to the Mappings menu and select the Create.. option. It opens the Mapping Name window to write a unique name ( m_Pre_and_Post) and click the OK button.

Drag and drop the EmpDetails source definitions from Sources folder to the mapping designer. Once you drag the source, the Power Center designer will automatically create the Source Qualifier for you.

Double click on the Source Qualifier transformation, and go to properties Tab to configure the Informatica Pre and Post SQL

Pre: This property is used to run the Command against the Source before the Integration Service Starts. Click the arrow

Here we are writing an Insert Statement to insert new records into the Emp Details table before the integration service starts.

Post: Use this property to run the Command against the Source After the Integration Service Ends. Here, we are writing a Delete Statement to delete the record whose employee Id is equal to 9

You can also alter the remaining properties. But for now, we are clicking OK button to close the properties windows

Next, Drag and drop the target definition from Targets folder to the mapping designer. Next, join the source qualifier Transformation with the target definition using the Autolink.. option.

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

Create Informatica Pre and Post SQL Workflow

After we finish creating the Informatica Pre and Post SQL Mapping, we have to create the workflow for it. Power Center Workflow manager provides two approaches to create a workflow.

• Create Workflow Manually
• Create Workflow using Wizard

In this example, we will create the Workflow manually. First, navigate to Workflows Menu and select the Create option will open Create Workflow window. Please provide the unique name (wf_Pre_and_Post) and leave the default settings. 

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

Create Session for Pre and Post SQL

There are two types of sessions:

• Non-reusable Session
• Reusable Session

For this Informatica Pre and Post SQL example, we created a Non-reusable Session and named it as s_Pre_and_Post for the session.

Please link the Start Task, and the Session Task. Next, navigate to Workflows Menu and select the Validate option to validate the Workflow. From the below screenshot, you can observe that the workflow is a valid one.

Now, Let me start the Informatica Pre and Post SQL Workflow. To do so, navigate to the Workflows menu and select the Start Workflow option.

Let us open the Server Management Studio. As you can, it has 16 records: 15 from EmpDetails table, and 1 records from Insert Statement in Pre SQL

If you observe the original Table after the Integration process. There are 15 records

The Delete Statement in Post deleted the record with EmpID 9, and The Insert Statement in Pre SQL inserts new records.