Informatica 9.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.
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:
STEP1:
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'
STPE2: 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.
STEP3: 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.
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:
STPE1: Go the mappings parameters and variables -> Create a new variable, $$COUNT_VAR and its data type should be Integer
STPE2:
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'
STEP3:
Now connect the expression transformation to the target and connect the
o_file_name port of expression transformation to the FileName port of
the target.
3. Create a new file once a day.
You
can create a new file only once in a day and can run the session
multiple times in the day to load the data. You can either overwrite the
file or append the new data.
This is similar to the first
problem. Just change the expression in expression transformation to
'EMP_'||to_char(sessstarttime, 'YYYYMMDD')||'.dat'. To avoid overwriting
the file, use Append If Exists option in the session properties.
4. 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.
STEP1: Sort the data on department_id. You can either use the source qualifier or sorter transformation to sort the data.
STEP2: 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'
STEP4:
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)
STEP5: Now connect to the target file definition.
Informatica online training - Informatica Jobs
![clip_image014[1]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidbZJMBDqWBYUkK4xnz3tj-qtA6EwGFaKouhhY1qTF_695FWzOpbSnalu-ll61H2Ali4mycnJtBFjFdPZh2AoxKUFaJ2vnMubLmqXQJcnq3LxFQApS2jEUBXJdcddUUCtIowMSekv2Fwbj/?imgmax=800 "clip_image014[1]"){kind=small}
