In this article, we are going to explain, How to configure Update Strategy in Informatica with an example. For this example, we are going to use the below show data (Customer table)
And the destination table is:
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 Transformation. Before we start configuring, First let me connect with the Informatica repository service.
In order to do so, we have to provide the Informatica Admin Console credentials. So, Please provide the appropriate Username and Password, and click the Connect button.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Step 1: Create Source Definition for Update Strategy in Informatica
Once you connected successfully, Please navigate to Source Analyzer and define your Sources. In this example, we are using Customer table from SQL Server database as our source definitions. Please refer Database Source in Informatica to understand the steps involved in creating source definition
Step 2: Create Target Definition for Informatica Update Strategy
Please navigate to Target Designer and define the 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 steps involved in creating a target definition
Step 3: Create a Mapping for Update Strategy in Informatica
To create a new mapping, Please navigate to Mappings menu in Menu Bar, and select the Create.. option.
This will open the Mapping Name window to write a unique name for this mapping. Let me write m_update_Cust and click OK button.
TIP: Please refer Informatica Mapping article to understand the procedure to create Mapping
Drag and drop the Customer record source definitions from Sources folder to the mapping designer. Once you drag the source, Power Center designer will automatically create the source qualifier for you. I suggest you refer the Source Qualifier Transformation article.
Step 3(a): Create a Lookup Transformation
In order to create a Lookup Transformation, Please navigate to Transformation menu in Menu Bar and select the Create.. option as we shown below.
Once you click on the Create.. option, Create Transformation window will be opened as we shown below. Please select the Lookup Transformation from the drop-down list and specify the unique name (lkp_Cust) and click on Create button
Once you click on the Create.. button, a Select Lookup Table for Lookup Transformation window will be opened as shown below. 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.
Once you click on the Create button, the Lookup Transformation will be added to the mapping designer. Next, drag and drop the Customer key from Source Qualifier.
Double click on the lookup transformation to configure its settings. 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).
Within the Properties section, we changed the Lookup Policy on Multiple Match to Use First value.
Within the condition tab, we added one condition CustmerKey = CustmerKey_lkp. Below condition states that Lookup transformation will check the lookup input source (i.e., Customer Key) is equal to existing Customer Key or Not.
Step 3(b): Create an Expression Transformation
In order to create an Expression Transformation, Please navigate to Transformation menu in Menu Bar and select the Create.. option as we shown below. As you can see, we assigned the name as Exp_Cust
Now let me add all the fields from Source Qualifier to Expression Transformation and the Key column from Lookup Transformation.
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
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.
Here, Informatica ISNULL is to check whether it is Null or not
Below specified expression will check whether the Customer key column coming from the lookup field is Empty or not. If it is empty then 1 will be returned otherwise 0. And the expression is IIF(ISNULL(CustomerKey_lkp), 1, 0). Please refer IIF to understand the syntax of a Informatica IIF.
Click OK to Apply the setting
Step 3(c): Creating Router Transformation
Please refer Router Transformation in Informatica article to understand the steps involved in creating it.
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.
Double click on the Router transformation to add Groups. Use Add New Group button to add required groups
From the below screenshot you can observe that we created one group for Inserting New Records and another for Updating existing records.
Let us provide the expression or condition for the First group by clicking the arrow button. This will open the Expression Editor to write a custom expression. Here, we want the Customers whose Customer key is Null which means, non-existing records
For Update group, we have written the expression as Flag = 0 which means, all the existing records
Once you finish configuring the properties, Click OK to close the transformation window.
Step 3(d): Create Informatica Update Strategy Transformation
Please refer 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.
Drag the fields from Insert group (Flag = 1) to Insert_Cust and drag fields from Update group (Flag = 0) to Update_Cust
Double click on the Insert_Cust Update Strategy to configure it. Let me change the Update Strategy Expression to 0 or DD_INSERT
Double click on the Update_Cust Update Strategy to configure it. Let me change the Update Strategy Expression to 1 or DD_UPDATE. This is for an update
Next, Drag and drop the target definition from Targets folder to the mapping designer. Repeat the same for one more time, and Auto link fields as we shown below.
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 Mapping, we have to create a workflow for it. Power Center Workflow manager provides two approaches 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.
This will open Create Workflow window as shown below. Please provide the unique name (wf_Customers) and leave the default settings.
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 example, we will create a Non-reusable Session. In order to create the same, Please navigate to Tasks Menu and select the Create option as shown below.
Please provide a unique name for this session. Here, we are naming it as s_Customers. Once you click on the Create button, a new window called Mappings will be opened. Here, we are selecting the m_update_Cust
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 $Source variable. So, click on the Arrow we marked below, and select the Informatica Source as the source information.
$Target connection value: This property will store the relational target information in $Target variable. So, click on the Arrow we marked below, and select the Informatica Target as the Target information.
Within the Mappings tab, we have to configure the Source and Target Connections. 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 in our previous step i.e., $Source
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
Next, Let me start the 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 update strategy or not.
Thank You for visiting Our Blog