Update Strategy Transformation in Informatica

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 Informatica Update Strategy Transformation is that you can restrict the records from not reaching 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)

Source Table 1

And the destination table is:

Destination table 2

Update Strategy Transformation in Informatica Example

If you compare the Source table with the destination table, multiple changes exist in the 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 Update Strategy Transformation, 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 have connected successfully, Please navigate to Source Analyzer and define your Sources. In this example, we use the [Customer Record] table from the SQL Server database as our source definitions. Please refer Database Source article.

Source Definition 3

Create Informatica Update Strategy Transformation Target Definition

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

Target Designer 4

Create Mapping

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

Update Strategy Transformation in Informatica 5

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

Update Strategy Transformation in Informatica 6

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

Update Strategy Transformation in Informatica 7

Creating Update Strategy Transformation in Informatica

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

Update Strategy Transformation in Informatica 8

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

Update Strategy Transformation in Informatica 9

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

Update Strategy Transformation in Informatica 10

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

Add Target Table 11

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

Update Strategy Transformation in Informatica 12

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.
Update Strategy Transformation in Informatica 13

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. You can add new columns by clicking the New column button, 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 in the target table.
Check the Input and output ports 14

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.
Open the Expression editor 15

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

Expression Editor 16

Click Apply and click OK to close the window.

View properties Tab to check the rejected rows 17

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

Update Strategy Transformation in Informatica 18

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

Create Workflow

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

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

Update Strategy Transformation in Informatica 19

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

Update Strategy Transformation in Informatica 20

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

Create a Session

There are two types of sessions:

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.

Create a Session 21

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

Choose the Mapping 22

Please link the Start Task with a newly created session task

Update Strategy Transformation in Informatica 23

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.

Treat Source Rows as Data Driven 24
$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.

Choose database Object 25
$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.

Target database 26

We have to configure the Source and Target Connections within the mappings tab. First, configure the source connections by clicking on the SQ_CustomerRecord source in the Sources folder.

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

Source Connection Variable 27

N.ow, 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 then use the Connection variable $Target

Target Connection Variable 28

Next, Let me start the Informatica Update Strategy Transformation Workflow. To do so, navigate 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.

Destination Table 29

Comments are closed.