Dynamic Target Flat File Name Generation in Informatica

  • Updated
  • Posted in Programming
  • 4 mins read


Informatica 8.x or later variations gives a function for producing the goal information dynamically. This function means that you can

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

Informatica gives a particular port,”FileName” in the Target file definition. This port you must add explicitly. See the beneath diagram for including the “FileName” port.

Go to the Target Designer or Warehouse builder and edit the file definition. You must click on on the button indicated in purple shade circle so as to add the particular port.

Now we’ll see some informatica mapping examples for creating the goal file title dynamically and cargo the info.

1. Generate a brand new file for each session run.

Whenever the session runs that you must create a brand new file dynamically and cargo the supply knowledge into that file. To do that simply comply with the beneath steps:

STEP1: Connect the supply qualifier to an expression transformation. In the expression transformation create an output port (name it as File_Name) and assign the expression as ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDDHH24MISS’)||’.dat’

STPE2: Now join the expression transformation to the goal and join eh File_Name port of expression transformation to the FileName port of the goal file definition.

STEP3: Create a workflow and run the workflow.

Here I’ve used sessstarttime, as it’s fixed all through the session run. If you will have used sysdate, a brand new file can be created at any time when a brand new transaction happens in the session run.

The goal file names created would appear to be EMP_20120101125040.dat.

2. Create a brand new file for each session run. The file title ought to comprise suffix as numbers (EMP_n.dat)

In the above mapping situation, the goal flat file title comprises the suffix as ‘timestamp.dat’. Here we’ve got to create the suffix as a quantity. So, the file names ought to appears to be like as EMP_1.dat, EMP_2.dat and so forth. Follow the beneath steps:

STPE1: Go the mappings parameters and variables -> Create a brand new variable, $$COUNT_VAR and its knowledge sort needs to be Integer

STPE2: Connect the supply Qualifier to the expression transformation. In the expression transformation create the next 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'

STEP3: Now join the expression transformation to the goal and join the o_file_name port of expression transformation to the FileName port of the goal.

3. Create a brand new file as soon as a day.

You can create a brand new file solely as soon as in a day and might run the session a number of occasions in the day to load the info. You can both overwrite the file or append the brand new knowledge.

This is much like the primary downside. Just change the expression in expression transformation to ‘EMP_’||to_char(sessstarttime, ‘YYYYMMDD’)||’.dat’. To keep away from overwriting the file, use Append If Exists possibility in the session properties.

4. Create a flat file based mostly on the values in a port.

You can create a brand new file for every distinct values in a port. As an instance take into account the workers desk because the supply. I need to create a file for every division id and cargo the suitable knowledge into the information.

STEP1: Sort the info on department_id. You can both use the supply qualifier or sorter transformation to kind the info.

STEP2: Connect to the expression transformation. In the expression transformation create the beneath 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'

STEP4: Now join the expression transformation to the transaction management transformation and specify the transaction management situation as

IIF(o_flag = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

STEP5: Now connect with the goal file definition.

If you want this publish, then please share it on google by clicking on the +1 button.

Leave a Reply