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

Update Strategy Transformation in Informatica 1

And the destination table is:

Update Strategy Transformation in Informatica 2

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 Informatica repository service. To do so, enter the Admin Console username and password you specified while installing the Informatica 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 in Informatica article

Update Strategy Transformation in Informatica 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 are using the already created SQL table (DupCust) as our target definition. Please refer Create Informatica Target table using Source Definition to understand the steps involved in creating a target definition

Update Strategy Transformation in Informatica 4

Create Update Strategy in Informatica Mapping

To create a new mapping, Please navigate to 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. 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 Sources folder to the mapping designer. Once you drag the source, the Power Center designer automatically creates the Source Qualifier Transformation for you.

Update Strategy Transformation in Informatica 7

Creating Update Strategy Transformation in Informatica

To create Update Strategy Transformation in Informatica, Please 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 the 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 on the Create button, Update Strategy Transformation added to the mapping designer.

Update Strategy Transformation in Informatica 10

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

Update Strategy Transformation in Informatica 11

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

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

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

Update Strategy Transformation in Informatica 16

Click Apply and click OK to close the window.

Update Strategy Transformation in Informatica 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 chose the Validate option.

Create Update Strategy Transformation Workflow

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

In this Informatica Update Strategy Transformation example, we will create the Workflow manually. 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 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 in Informatica:

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.

Update Strategy Transformation in Informatica 21

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

Update Strategy Transformation in Informatica 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 selected in this case. If not, please change it to Data-Driven. Remember, Data Driven option means, instead of performing Informatica default insertion, and it will use the Update Strategy Transformation option to load the target table.

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

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

Update Strategy Transformation in Informatica 26

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

Update Strategy Transformation in Informatica 27

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

Update Strategy Transformation in Informatica 28

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.

Update Strategy Transformation in Informatica 29

Comments are closed.