top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Split Your Informatica PowerCenter Target File Dynamically Based on the Content

+3 votes
1,622 views

There can be scenarios, where you need to generate multiple flat files using an informatica mapping based on the source data content or some other business rule, from a single data source. For example, you may need to generate last month’s top revenue generating customer list, which is split into multiple files based on the customer residence state. In this article we will see how to split your target file dynamically based on the business rule.

For the demonstration purpose, let’s take a simple business case where we need to split the customer data into multiple files based on the customer residential state.

As the first step let’s create the flat file definition using Target Designer
enter image description here

Now let’s add one new column 'FileName' using the 'Add File Name Column' button which is highlighted at the top right corner as shown in the below image. This is the column based on the file name is dynamically changed.
enter image description here

Let’s build the mapping as we have it in below image, Here we are sorting the data using the SORTER transformation. After the sorting, we will get the customers grouped together based on customer's state of residence.
enter image description here

Now we need to add an EXPRESSION transformation to set a boundary flag for each customer group based on customer's state of residence. We are creating a port NEW_FILE_FLAG for the same

In the expression transformation we are adding following ports.
- V_NEW_FILE_FLAG as Variable Port.
- V_STATE as Variable Port.
- NEW_FILE_FLAG as Output Port.

And the following are the expressions for the ports.
- V_NEW_FILE_FLAG :- IIF(STATE = V_STATE, 'N', 'Y')
- V_STATE :- STATE
- NEW_FILE_FLAG :- V_NEW_FILE_FLAG

Apart from setting NEW_FILE_FLAG, we need to create the port FileName, based on which the target file name is dynamically changed.
- File Name as Output Port.

And the following is the expression for the port.
- FileName :- 'Customer_Master_' || STATE || '.csv'
enter image description here

Based on the flag we created in the EXPRESSION transformation, we are going to split the file in the next step. We will use TRANSACTION CONTROL transformation to do the same. Set the 'Transaction Control Condition' property of the transformation with the below expression.

  • IIF(NEW_FILE_FLAG='Y',TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)
posted Apr 24, 2014 by Shatark Bajpai

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

Dynamically Change Your Target Flat File Name Using Informatica Mapping

Whenever you create a flat file using a PowerCenter Workflow, the name of the file will be static, which is specified in the Session 'Output Filename' property. There can be business cases where you need to generate flat files with dynamic file name, like add Timestamp with the file name.

As the first step let’s create the flat file definition using Target Designer.
enter image description here

Now let’s add one new column 'FileName' using the 'Add File Name Column' button which is highlighted at the top right corner as shown in the below image. Please note that this column is not going to be in the target file, this is the column based on the file name is dynamically changed.
enter image description here

Next step will be, create the mapping to generate the flat file as shown below.
enter image description here

This expression transformation is responsible for generating the target file name dynamically.

Lets look at the Expression Transformation below.
enter image description here

Here in this transformation, we have to create an output port 'FileName' and give the expression value as 'Customer_Master_' || TO_CHAR (SYSDATE,'YYYY-MM-DD') || '.csv'. Using this expression we are dynamically attaching the Date along with the file name. You can customize this expression as your need to change the file name.

Now from the expression transformation, map the 'FileName' port to the target table and all the remaining ports.

Now create and run the workflow, you will see the file is generated in your Target File directory with the date as part of the file name.

There is not any specific setting required at the session level.

 READ MORE
...