[INFORMATICA] How To Split Target File Dynamically Based On The Content

How to Split target file dynamically based on the content

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.

As the first step lets create the flat file definition using Target Designer

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

Apart from setting NEW_FILE_FLAG, we need to create the port FileName, based on which the target file name is dynamically changed.

• FileName as Output  Port.

And the following is the expression for the port.

• FileName :- 'Customer_Master_' || STATE || '.csv'

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)

Now the mapping will be as we have it in the picture.

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.