Update Strategy in Informatica

In this article, we are going to explain how to configure Update Strategy in Informatica with an example. For this Informatica Update Strategy example, we are going to use the below show data (Customer table)

Update Strategy in Informatica Example 1

And the destination table is:

Update Strategy in Informatica Example 2

Update Strategy in Informatica Example

In our previous article, we already explained the Update Strategy Transformation. In this example, we will show you another way of using this 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 Source Definition for Update Strategy in Informatica

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

Update Strategy in Informatica Example 3

Create Target Definition for Informatica Update Strategy

Please navigate to Target Designer and define the Informatica Update Strategy Target. In this example, we are using the already created SQL table (New Customers) as our target definition. Please refer Create Informatica Target table using Source Definition to understand the target definition

Update Strategy in Informatica Example 4

Step 3: Create a Mapping for Update Strategy in Informatica

To create a new Informatica Update Strategy mapping, Please navigate to the Mappings menu and select the Create.. option.

Update Strategy in Informatica Example 5

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

Update Strategy in Informatica Example 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 will automatically create the Source Qualifier for you. 

Step 3(a): Create a Lookup Transformation

To create a Lookup Transformation, Please navigate to the Transformation menu and select the Create.. option.

Update Strategy in Informatica Example 7

Once you click on the Create.. option, Create Transformation window will open. Please select the Lookup Transformation from the drop-down list and specify the unique name (lkp_Cust) and click on Create button

Update Strategy in Informatica Example 8

Once you click on the Create.. button, a Select Lookup Table for Lookup Transformation window will open. Here, we want to use the Target table as the lookup source.

TIP: If your lookup table is present in Source, then select Source. Here, you can also Import from an external source.

Update Strategy in Informatica Example 9

Clicking the Create button adds the Lookup Transformation to the mapping designer. Next, drag and drop the Customer key from Source Qualifier.

Update Strategy in Informatica Example 10

Double click on the lookup transformation to configure its settings. The below screenshot will show you the list of available options in the Ports tab. From the below screenshot, you can observe that We changed the Input port name (CustomerKey_lkp).

Update Strategy in Informatica Example 11

Within the Properties section, we changed the Lookup Policy on Multiple Match to Use First value.

Update Strategy in Informatica Example 12

Within the condition tab, we added one condition CustmerKey = CustmerKey_lkp. The below condition states that Lookup transformation will check the lookup input source (i.e., Customer Key) is equal to existing Customer Key or Not.

Update Strategy in Informatica Example 13

Step 3(b): Create an Expression Transformation

To create an Expression Transformation, select the Create.. option from the Transformation menu. As you can see, we assigned the name as Exp_Cust

Update Strategy in Informatica Example 14

Now let me add all the fields from Source Qualifier to Expression Transformation and the Key column from Lookup Transformation.

Update Strategy in Informatica Example 15

As you can see, we changed the column name (column added from lookup). Next, we would like to add one Flag field using New Column button

Update Strategy in Informatica Example 16

For this flag field, let me write an expression by clicking the Arrow. Remember, If you want to perform Delete or some other operation based on some condition, then use multiple Tags.

Update Strategy in Informatica Example 17

Here, Informatica ISNULL is to check whether it is Null or not

Below specified expression check whether the Customer key column coming from the lookup field is Empty or not. If it is empty, one will return otherwise 0. And the expression is IIF(ISNULL(CustomerKey_lkp), 1, 0). Please refer to IIF to understand the syntax of an Informatica IIF.

Update Strategy in Informatica Example 18

Click OK to Apply the setting

Update Strategy in Informatica Example 19

Step 3(c): Creating Router Transformation

Please refer to Router Transformation in Informatica article to understand the steps involved in creating it.

Update Strategy in Informatica Example 20

Once you created it, Please connect the Expression Transformation with the router transformation by dragging the required fields. From the below screenshot, you can observe that we are adding all the fields except the lookup column field.

Update Strategy in Informatica Example 21

Double click on the Router transformation to add Groups. Use Add New Group button to add required groups

Update Strategy in Informatica Example 22

From the below screenshot, you can observe that we created one group for Inserting New Records and another for Updating existing records.

Update Strategy in Informatica Example 23

Let us provide the expression or condition for the First group by clicking the arrow button. It opens the Expression Editor to write a custom expression. Here, we want the Customers whose Customer key is Null which means, non-existing records

Update Strategy in Informatica Example 24

For Update group, we have written the expression as Flag = 0 which means, all the existing records

Update Strategy in Informatica Example 25

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

Update Strategy in Informatica Example 26

Step 3(d): Create Informatica Update Strategy Transformation

Please refer to Update Strategy Transformation in Informatica to understand the steps involved in creating it. Let me add two Update Strategies:

  • Insert_Cust: This is for inserting New records into a target table.
  • Update_Cust: This is to update existing records in a target table.
Update Strategy in Informatica Example 27

Drag the fields from Insert group (Flag = 1) to Insert_Cust and drag fields from Update group (Flag = 0) to Update_Cust

Update Strategy in Informatica Example 28

Double click on the Insert_Cust Update Strategy to configure it. Let me change the Update Strategy Expression to 0 or DD_INSERT

Update Strategy in Informatica Example 29

Double click on the Update_Cust Update Strategy to configure it. Let me change the Update Strategy Expression to 1 or DD_UPDATE for an update

Update Strategy in Informatica Example 30

Next, Drag and drop the target definition from the Targets folder to the mapping designer. Repeat the same for one more time and Auto link fields.

Update Strategy in Informatica Example 31

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

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

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

Update Strategy in Informatica Example 32

It opens the Create Workflow window. Provide a unique name (wf_Customers) and leave the default settings.

Update Strategy in Informatica Example 33

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

Step 4(a): Create a Session for Update Strategy in Informatica

There are two types of sessions in Informatica:

For this Informatica Update Strategy example, we will create a Non-reusable Session. Select the Create option from Tasks Menu

Update Strategy in Informatica Example 34

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

Update Strategy in Informatica Example 35

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 Informatica Source as the source information.

Update Strategy in Informatica Example 36

$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 Informatica Target as the Target information.

Update Strategy in Informatica Example 37

Within the Mappings tab, we have to configure the Source and Target Connections for Informatica Update Strategy. First, let us configure the source connections by clicking on the SQ_Customer 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

Update Strategy in Informatica Example 38

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

Update Strategy in Informatica Example 39

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

Update Strategy in Informatica Example 40

Let us open the SQL Server Management Studio to verify whether we successfully performed the update strategy or not.

Update Strategy in Informatica Example 41