Split Your Informatica PowerCenter Target File Dynamically Based on the Content
There can be scenarios, where you need to generate multiple flat file 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 months 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, lets take a simple business case where we need to split the customer data into multiple files based on the customer residential state.
Now lets 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.
Lets 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.
Now we need to adding 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
- FileName as Output Port.
And the following is the expression for the port.
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.
As the last step map all the columns to the target table. Our finished mapping will look as it is in the image below
We are all done... Now build and run the workflow; you will see multiple files is generated in your Target File directory with Customer_Master_XX.csv as file name. There is not any specific setting required at the session level.