[INFORMATICA] Dynamic Target Flat File Name Generation

Dynamic Target Flat File Name Generation in Informatica

Informatica 8.x or later versions provides a feature for generating the target files dynamically. This feature allows you to

• Create a new file for every session run
• create a new file for each transaction.

Informatica provides a special port,"FileName" in the Target file definition. This port you have to add explicitly. See the below diagram for adding the "FileName" port.

Go to the Target Designer or Warehouse builder and edit the file definition. You have to click on the button indicated in red color circle to add the special port.

Now we will see some informatica mapping examples for creating the target file name dynamically and load the data.

way 1. Generate a new file for every session run.

Whenever the session runs you need to create a new file dynamically and load the source data into that file. To do this just follow the below steps:

STEP 1: Connect the source qualifier to an expression transformation. In the expression transformation create an output port (call it as File_Name) and assign the expression as 'EMP_'||to_char(sessstarttime, 'YYYYMMDDHH24MISS')||'.dat'

STEP 2: Now connect the expression transformation to the target and connect eh File_Name port of expression transformation to the FileName port of the target file definition.

STEP 3: Create a workflow and run the workflow.

Here I have used sessstarttime, as it is constant throughout the session run. If you have used sysdate, a new file will be created whenever a new transaction occurs in the session run. 

The target file names created would look like EMP_20120101125040.dat. 

Way 2. Create a new file for every session run. The file name should contain suffix as numbers (EMP_n.dat)

In the above mapping scenario, the target flat file name contains the suffix as 'timestamp.dat'. Here we have to create the suffix as a number. So, the file names should looks as EMP_1.dat, EMP_2.dat and so on. Follow the below steps:

STEP 1: Go the mappings parameters and variables -> Create a new variable, $$COUNT_VAR and its data type should be Integer , Aggregation (count)

STEP 2: Connect the source Qualifier to the expression transformation. In the expression transformation create the following new ports and assign the expressions.

v_count (variable port) = v_count+1

v_file_count (variable port) = IIF(v_count = 1, SETVARIABLE($$COUNT_VAR,$$COUNT_VAR+1),$$COUNT_VAR)

o_file_name (output port) = 'EMP_'||v_file_count||'.dat'

way 3. Create a flat file based on the values in a port.

You can create a new file for each distinct values in a port. As an example consider the employees table as the source. I want to create a file for each department id and load the appropriate data into the files.

STEP 1: Sort the data on department_id. You can either use the source qualifier or sorter transformation to sort the data.

STEP  2: Connect to the expression transformation. In the expression transformation create the below ports and assign expressions.

v_curr_dept_id (variable port) = dept_id

v_flag (variable port) = IIF(v_curr_dept_id=v_prev_dept_id,0,1)

v_prev_dept_id (variable port) = dept_id

o_flag (output port) = v_flag

o_file_name (output port) = dept_id||'.dat'

STEP 3: Now connect the expression transformation to the transaction control transformation and specify the transaction control condition as

IIF(o_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)