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