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: 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)
And the destination table is:
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 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.
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
To create a new mapping for Informatica update Strategy transformation, Please navigate to the Mappings menu in Menu Bar, and select the Create.. option.
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.
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.
Creating Update Strategy Transformation in Informatica
To create Update Strategy Transformation in Informatica, 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 the Create button, Update Strategy Transformation is added to the mapping designer.
Next, Drag and drop the target definition (DupCust) from the Targets folder to the mapping designer.
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.
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. 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.
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 choosing the Validate option.
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.
It opens the Create Workflow window. Please provide the unique name (wf_update_tran) and leave the default settings.
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.
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
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.
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.
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
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.