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.
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.
Next step will be, create the mapping to generate the flat file as shown below.
This expression transformation is responsible for generating the target file name dynamically.
Lets look at the Expression Transformation below.
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.