Tuesday, August 26, 2008

How to generate target file names dynamically from the mapping?

I've seen this question many times in the Devnet forums very recently. I thought I will just elaborate the PowerCenter documentation here.



In order to generate the target file names from the mapping, we should make use of the special "FileName" port in the target file. You can't create this special port from the usual New port button. There is a special button with label "F" on it to the right most corner of the target flat file when viewed in "Target Designer".



Below two screen-shots tell you how to create the special port in your target file.






Once this is done, the job is done. When you want to create the file name with a timestamp attached to it, just use a port from an Expression transformation before the target to pass a value of Output Port with expression $$FILE_NAMEto_char(sessstarttime, 'YYYYMMDDHH24:MISS')'.csv'.


Please note that $$FILE_NAME is a parameter to the mapping and I've used sessstarttime because it will be constant through out the session run.


If you use sysdate, it will change if you have 100s of millions of records and if the session may run for an hour, each second a new file will get created.


Please note that a new file gets created with the current value of the port when the port value which maps to the FileName changes.

We'll come to the mapping again. This mapping generates two files. One is a dummy file with zero bytes size and the file name is what is given in the Session properties under 'Mappings' tab for target file name. The other file is the actual file created with the desired file name and data.

When you have different sets of input data with different target files created, use the same instance, but with a Transaction Control transformation which defines the boundary for the source sets. A demonstration of this is available in the PowerCenter documentation.

72 comments:

Asha said...

Hi Radha,

Thank you so much for such a detailed information. It helped me a lot in my work.

Sashi said...

RadhaKrishna,

The point you mentioned in the blog was what exactly i was looking for since 2weeks.
Its an amazing way to acheive my requirement.
But the only thing I kinda dont like is it creates 2 files.
Is there a way to get rid of the file which is mentioned in the session prop- Mapping tab
Target file. I tried to make it empty but doesn't work.

Let me know if there is a way to get jus one file

Radhakrishna Sarma said...

Hi Sashi,

I'm happy that my blog was helpful to you. Unfortunately, there is no way to make the dummy file disappear. It is the way this FileName port works. Whenever tha value changes on this port, it creates a file. To avoid the frustration, I use a post-session command to remove the dummy file. This way, after your session run, you wil only see one file.

By the way, are you not getting the empty file. What do you mean by "I tried to make it empty"?

Sashi said...

I mean, in the Session - mapping Properties, we need to specify the Output File name for the target.
I tried to leave it empty which we could nt do.

So let me know what should be written in the post session.

Thanks..

Sashi said...

I found a different way to do this.

Edit the Workflow and add variable $$NEWVARIABLE.
Then in the assignment, assign the defined variable, $$NEWVARIABLE = Concat(Concat('test_',TO_CHAR(trunc(sysdate),'yyyy_mm_dd')),'.xml')

Now after the Start, have the assignment and whatever session you may want.
In the Target file Name in session properties, we need to put $$NEWVARIABLE.

This way you will have just one file.

Dorset said...

This is great info to know.

chaitanya said...

Hi Sarma,
Can i add Header and Footer for those Mulitple files generated.

Radhakrishna Sarma said...

Yes Chaitanya, you can.

Cheers
Sarma.

Adarsh said...

Hi, this is very good feature..but i can't see the "File" option in my warehouse designer on the flat file. Informatica version is 7.1.3, has it come in the later version.

Thanks,
Adarsh

Radhakrishna Sarma said...

I have stopped using 7.x for more than 2 years now, I think we don't have this option in 7.x, not sure though. BTW, I have a very vague feeling that I used this 7.1.4.

Cheers
Sarma.

Adarsh said...

That seems to be the only probable reason i am not able to see it..Anyways Thanks Sarma...

Satish said...

hi..
i need to create only one file using filename port ..but that file shuld be dynamic how to to do..that..in informatica..

Radhakrishna Sarma said...

Hi Satish,

Yes possible. If you look through the method described by Sashi in the comments section of this article, you will know how to do it.

Cheers
Sarma.

Anonymous said...

yes but that is not working..for me..

When i used the Filename port of the target defention the data in the outpufile started to display in sequential fashion not row by row.is there any other settings to be done..
When i remove the filename port or dont pass to target data comes row by row

Mikael said...

Hey Sarma,

I´ve done the procedure like you said but my mapping isn´t creating 1 file per location.
I´ve followed the INFA tutorial on "Creating target flat files by transaction", and i couldn´t make step 2 (Expression transformation. Determines when a new city appears in a row and passes an integer to the New City port of the Transaction Control transformation. ???Passes 0 by default and passes 1 when the row contains a new city.??? ) work. Could you please guide me thru ??

Radhakrishna Sarma said...

Hi Mikael,

Can you tell me what all transformations you have from source till the target? The method I described and the ones in the Informatica help, both - working fine for me.

Cheers
Sarma.

Mikael said...

Sure !
i´m using one source attached on a source qualifier.Then a made a Group objectto group all my record´s by city. then i created an expression where i created an FLAG port that should be 0 when previous record is equal or 1 if it´s different.
Then i used de transaction control where i check the flag content and commit the record.

my question is the validating 1 or 0 on teh expression... what´s the best way to do it?

Radhakrishna Sarma said...

Hi Mikael,

You should have a sorter transformation before the expression. Then you will be able to say that all the records of the current city are processed and the new city comes now.

Let us assume City is the input port to the expression.

Your expression after the sorter can have something like this as an output port.


v_new_City (Integer Variable port)---> iif(v_prev_city = City, 0, 1)

v_prev_city (String Variable port) ---> City

O_new_city (Integer Output port) ----> v_new_city

You take the O_new_city value to the next transformation after expression.

Note that the order of the variable ports should be like declared above because, the previous city values should be stored after the new city value is checked.

I hope now it helps.

Thanks
Sarma.

Mikael said...

Sarma your are genius ! !

It worked perfect !

Now,i have another challenge for you.
In another mapping i need to have 10k of record MAX in a flat file. If i have more than 10k, i need to create a new file..

Any ideas?

Radhakrishna Sarma said...

Hi Mikael,

Very simple if you can follow what I've suggested earlier. You don't need a sorter in this case. In the expression, you just keep incrementing a variable and change the value of the Filename port here when the counter is 10000.

v_counter = iif(v_counter = 0, 1, v_counter+1)

v_File_Name = iif(mod(v_counter, 10000) = 0, 'File_Name_'||v_counter, v_File_Name)


Cheers
Sarma.

Mikael said...

HEY SARMA !

Thx again for the help, but it isn´t workig.
I´m getting "WRT_8004 Writer initialization failed [Error opening session output file [/home/infrmtca/Informatica/PowerCenter8.6.0/server/infa_shared/TgtFiles/] [error=Is a directory]]. Writer terminating.
WRT_8064 Error opening session output file [/home/infrmtca/Informatica/PowerCenter8.6.0/server/infa_shared/TgtFiles/] [errno=21] [error=Is a directory]"

Another thing...

For each file that reaches 10k records i need to put an _1, _2 and on and on...

Is that the same logic?

BR

Radhakrishna Sarma said...

If you could apply a bit of logic, it isn't that difficult.

v_File_Name = iif(v_counter = 1, 'File_Name_1', iif(mod(v_counter, 10000) = 0, 'File_Name_'||v_counter/10000, v_File_Name))

This may not be the exact solution, but if you play around with this. You will be able to manage.

Cheers
Sarma.

Sandeep said...

RK,
Is there a way to create tables dynamically using informatica 811 sp2?
What is the file size limitation while creating them dynamically?

Radhakrishna Sarma said...

Hi Sandeep,

You can not create tables dynamically. There are ways to do that, but I would always discourage design that demands this.

There is no file size limitation, that I know of for dynamically created files.

Cheers
Sarma.

Naresh Achary said...

Hi Radhlrishna,
I have a requirement of creating target files with dynamic names. I followed ur article. Its really helped me alot. but, I need to have the header and trailer in the target excel file with the dynamic file name. Can I have both the options? If u had faced this kind of scenario pls let me know, How it can be achieved?

Radhakrishna Sarma said...

Hi Naresh,

It is quite simple because you will only be treating the filename contents of the expression port before the target as any other port.

For eg: In the expression before target, I would create a flag like this.

v_flag (variable, small int) --> iif(v_flag = 0, 1, -1)

v_filename (variable, string) --> (based on your requirement)

v_detail (variable, string) --> (driven by your business needs)

O_Line (output port, string) --> iif(v_flag = 1, v_file_name||chr(10)||v_detail, v_detail)

I hope the above expression ports satisfy your requirement, unless your target definition is defined with columns in it.

In that case, I would follow the same approach, but while mapping to the first target column, I would use the O_Line method to precede the first record with the file name value.

I have not tried it out but I'm confident that it works. Please let me know if you are stuck anywhere.

Cheers
Sarma.

Ram said...

Hi Sarma,

Thanks for your great explanation..
For me the only problem is , the file has been generated in sequential fashion not row by row.is there any other settings to be done.?

Please help me on this?

Radhakrishna Sarma said...

Hi Ram,

I don't think there should be any problem with the format of the file. You should see every line with a carriage return at least. Do not link any port to the target FileName port and see the target file. Does it have data properly?

Cheers
Sarma.

Anonymous said...

Hello Radhakrishna,
This information you posted is very useful. Thank you. Since this is an older post, I'm not sure if you're still replying here, I have a question. I'm working on Informatica v.8.5. I'm using indirect file list as my source and I want one target file to be created per source file. I'm also generating fileID per source file in EXP transformation using the logic
IIF(v_filename=filename, v_fileID, v_fileID+1) then I assign v_filename = filename. When I'm trying to generate separate target file per source file, I only am getting one target file. Here is the logic I'm using:

IIF(filename=v_filename, $$Filename, v_fileID||'_test.dat')

$$Filename is a mapping variable with initial value 1_test.dat

Can you please point out what I'm missing?

Radhakrishna Sarma said...

Hi Anonymous,

It would've been easier for me if you had left your email address or something. I have prepared screen shots for you. Let me know and I will send you an email.

Cheers
Sarma.

viswanath said...

Hi Radhakrishna Sarma,

Thanks a lot for how to create FILED_NAME. I tried this but it is not working.

File having data as below.:
visu,b,c
x,y,z
a,b,c
x,y,z
a,b,c
x,y,z
a,b,c
x,y,z
a,b,c
x,y,z

The outputs are create as below
1) visu (it is having all the records)
2) x (it is having only one record as x,y,z)
3) a (it is having only one record as a,b,c)

but in 2 file (x)it should have 5 records and in file 3 also it should have 4 recrods.

In the mapping i have
sq->exp->trascation control(properties:TC_CONTINUE_TRANSACTION) ->trg.

let me know is there any problem in my mapping.

viswanath said...

Hi Radhakrishna Sarma,
Below are the questions

1) You have any idea how to call x workflow from y workflow.

2)Before run the the workflow X we need to check atleast one row should be there in table AAA. If there is no row then we the workflow should not run it has to wait until anyone entered row in table AAA.

viswanath said...

Hi Radhakrishna Sarma,
Below are the questions

1) You have any idea how to call x workflow from y workflow.

2)Before run the the workflow X we need to check atleast one row should be there in table AAA. If there is no row then we the workflow should not run it has to wait until anyone entered row in table AAA.

Animesh said...

Hi,

I have a requirment in which i have to create a file for each Employname. I will explain the complete requirment with the following example.

I am having a flat file and its a source. now i contains the following information

First_name, Email
Abc Abc@gmail.com
XYZ XYZ@gmail.com
Pqr Pqr@gmail.com
Pqr Pqr@gmail.com
Pqr Pqr@gmail.com
Pqr Pqr@gmail.com
Abc Abc@gmail.com
Abc Abc@gmail.com
Abc Abc@gmail.com

Now what is require is i need to generate target filesin such a manner that for every First_name there should be one file geberated and in that file it should contains the all record for that first_name. For the above source records the output should be in such a manner.

First file with name Abc
Abc Abc@gmail.com
Abc Abc@gmail.com
Abc Abc@gmail.com
Abc Abc@gmail.com

Second File with name Pqr
Pqr Pqr@gmail.com
Pqr Pqr@gmail.com
Pqr Pqr@gmail.com
Pqr Pqr@gmail.com

Third file with name XYZ
XYZ XYZ@gmail.com

for achiveing this task

I am using the following mapping

Source -----> SQ----> Sorter ----> EXP ---> Tansaction control ----> Target

In sorter I am just sorting the names based on first_name and distinct is not selected.

IN EXP i am checking the current and previous record and storing in new_first_name variable and then in another port i am checking the logic, so that when ever the name changes i wil get
NEWFIELD = iif(new_first_name = First_Name,0,1).

In transaction control i am checking
IIF(NEWFIELD=1,TC_COMMIT_AFTER)

And then linking to the target. when i execute this i am getting three files generated and with the correct name also the default file is also generated with zero byte. But my problem is i am getting only one records in the files as in the example there are four records so four records should come in the target.

Please let me know if i am missing some property or any thing in the transaction control transformation



Regards
Animesh

Anonymous said...

Thanks Radhakrishna. I had a similar requirement but need FTP file to a different location. I guess the file port option cannot be used with FTP connections. It works when the file is generated locally, but fails when I use FTP. Any suggestions on how to go about this? (pls note that unix scripting is not an option. It is a secure FTP and changes must be done from within the mapping/session itself) Thanks for your help.

Radhakrishna Sarma said...

I don't think there is any other option for FTP.

koustuva said...

hi sir
i have mix up data means it contain distinct record as well dup records.i want to generate file dynamically as result how distinct records are there it will create tht much of target file ,and plz desc how to generate tht coz am failed to achieve it.
Thanks
koustuva
koustuva8@gmail.com

Anonymous said...

Hi RadhaKrishna,

I am doing one project in Informatica powercenter 8.6. In that I am trying to use flat files for source and target . But in target I am unable to generate it. I did it till import file but after that I am unable to do .
Can you please tell me the step by step process ?

Looking forward to your reply.

You can email me @ rojasai@gmail.com

Regards,
Shashank

syamprasad said...

Hi sarma good-evening,

I am new to this INFOMATICA ETL TOOL, please help me u , in which situation we uses this dyanamic-lookup, is it not possible with the help of static look-up? suppose if i run the mapping which contains defatult lookup for every 10 days with 10 days new data, is it not possible to maintain entire history in this way , suppose if i used dyanamic lookup , then the mapping is continus running state , for to contionus monitor of source , if is there any changes came in source, suppose if applied lookup on target table, what is use to update in lookup cache after it insert into the target table,


MY MAIL-ID : NIMMALA.NVS@GMAILC.COM

PLEASE SEND ME ANSWER TO MY MAIL, AND ALSO IF IS THERE ANY MATERIAL WHICH U PEREAPED OF THIS SEND ME ,


AND ALSO I HAVE LOT OF DOUGHTS IN DATA-LODING IN DIMESTION TABLE AND FACT TABLE , IF U HAVE ANY PPT OF RELATE TO THIS WHICH U PREPARED PLESE SEND ME ,


Thank's & Reagard's
N.V.Syamprasad,

Anonymous said...

GE Sarma,

I am new to ur blog. You r doing really good job.

Here I have a scenario like while processing file to table I need to skip header and trailer which file had.

Header is having no constant data.
Trailer is mentioned like '000001254'.

Any suggestions to implement this

Thanx in advance........

Ashish Sinha said...

Hi Radha, Do we really need to use transaction control? Can we simply not connect the identifier field in filename port name and make the file in append mode? let me know your thoughts..
Thanks!
Ashish

Arjun said...

Hi Radha Krishna,

I am reading your blogs frequently. And your recent blog already solved my problem.

Can you please help me how to populate the data in hierarchial type by using oracle as source and .csv file as a target. Please do the needful.

Arjun

rizwan said...

hi radha,
1)how to generate flatfile along with the field having its filename?
2)how to generate dynamic output files using Transaction Control Transformation with single source,for example emp table,there should be a separate file for dept10,dept20,dept30?

send ur reply to my mail id: rizwan.dwh@gmail.com

Anonymous said...

Hi Radhakrishna,

Thanks a lot for your inof.it helpem me a lot.

Thanks!!

Hardik Solanki said...

Hi Radhakrishna,
Your blog on "How to generate target file names dynamically from the mapping?" helped me to generate target file names dynamically. However I have to create reject file names also dynamically. Is there any option using which I can create both target file names and reject file names dynamically for a particular mapping?
My email id is: hardik0907@gmail.com

raj said...

Thanks.

raj said...

Raja
i am fresher mca candidate.Recently i done the course of informatica power center 8.6.0.but i am very dispoint i don't have any knowledge in project so please send the project explanation documention.Mail id is:raja.allanki@gmail.com

Rachat de credit said...

Thanks a lot it was a fantastic support, now to generate target file names dynamically from the mapping? is simple utilizing your advice. Kudos

Anonymous said...

´m getting "WRT_8004 Writer initialization failed [Error opening session output file [/home/infrmtca/Informatica/PowerCenter8.6.0/server/infa_shared/TgtFiles/] [error=Is a directory]]. Writer terminating.
WRT_8064 Error opening session output file [/home/infrmtca/Informatica/PowerCenter8.6.0/server/infa_shared/TgtFiles/] [errno=21] [error=Is a directory]"

WHAT WOULD BE THE REASON FOR THIS AND I HAVE ALL PERMISSIONS ON TARGET FOLDER...

Arvind said...

Hi Radhakrishan,

Thanks for your info.My requirement is to append a timestamp to the target .csv file and SFTP to a remote server.Could you please share some info on how we can achieve this?

preythap said...

Hi Radhakrishna sarma..
thank you so much for the valuble info.
am newbie in Informatica if you have any scenario based questions or examples please mail me.

Thanks

preythap said...

forgot to mention mailid
preythap@gmail.com

chaitu said...

Hi radha krishna garu,

add new file (F) icon is not seen in my system. can you please tell me how can i retrieve that.

chaitanya

Arun said...

Heyy Radha....

I have used this option in the mapping and connected this port of the target definition with
'a_'||Filename_dt_v||'_b.txt'. But the desired file is not being generated in my target directory.

The dummy file fileoutput.txt is alone being generated in the output directory.

venubabu said...

Hi RadhaKrishna,
Can you please help me on the below design.
I have a requirement of creating target files of each 25000 records in it with dynamic file names and also need to have the header and trailer for each target flat file with the dynamic file name.The source is RDBMS.

Note : I am able to generate multipl files with dynamic names but only the logic i am missing to populate header and trailer for each file.

Please find my email venu.etl123@gmail.com.

Thank you,
venu

Ranith said...

Radhakrishna,

Really your explanation is too good but here i have another query for you after creating the files dynamically i want to know how many records exist in that file how can you achieve this ......

Thanks in advance!

Rijju Akbar said...

U R doing an excellent job.

Rijju Akbar said...

Hi Radha really excellent

Anonymous said...

Really excellent. You can find some more scenarios on dynamic file creation at Dynamic File Creation in informatica

Krishna Prasad said...

Hi Sarma,
I am relatively new to informatica compared to other bloggers here.I am working on a reaquirement where I need to create a file per each state with timestamp suffix.

state,x,y,z
NY
NY
NY
PA
NJ

exp filenames - (ny_08302012.txt, nj_08302012.txt, pa_08302012.txt).
I know others have already asked you this question but i don't know which of your responses to follow.

Please email if you wish to - krishnaj.prsd@gmail.com

Anonymous said...

Hello Radhakrishna
Thanks for the detailed explanation. We are building a real time messaging solution with UMQ. It seems this option doesnt work with real time messaging. Any ideas? Please let me know.

Thanks

Prasad said...

Hi Sharma

Its good you are helping many people through this blog.

Could you please tell me when the target is DB like oracle is it possible to generate file names along with the records.

Thanks
Prasad

Anonymous said...

How to remove # from headers for files created dynamically

I have a mapping as below which creates files dynamically.


SQ -> SRT -> EXP -> TRANS_CONTROL -> TGT-


I am using the filename port in my target to create the files and its working fine.


I want to generate column header's for each file. I am using the 'Header options' in my session properties.
But this adds a '#' as the first character to the header row which I don't require.


How do I ignore the '#' in my header row for the files generated dynamically?


Thanks,

data warehouse forum said...

Thanks for the solution. I have also written a similar post on flat file generation.


Check out the link

Dynamic Target Flat File Name Generation in Informatica

Anonymous said...

Hi Sharma,

i have requiremnet similar to this but here target is XML.
If from the source table is trying to more than 4400 records then i have create one more XML file .
How could i do thru Informatica Dyanmically..

can you please provide me some inputs on this.iam stuck on this issue. ihave one more alternative is thru unix Scripts..Can you please guide me on the same

Am i Missing Something...?? said...

But one issue is. when there are "zero records to pull" im not able to see the empty file created? i there a way to over come this issue using the above explained method.

Anonymous said...

Thank you! Thank you! Thank you! I was going nuts with what I was missing. It was the 'special port'

Anonymous said...

SUPERB, MARVELOUS, EXCELLENT work Radhakrishna.....

Anonymous said...

HI Radha,

When i have zero records in source.I am unable to create flatfile with date_stamp.Only Filename file with zero byte is created .
Can you please helpe me out on this.

regards,
Sripriyav

MANISH KUMAR said...

hi .....this is manish
supose my source is flat file data is look like a -99.99 imy target is -9999 how it possible in informatica

Nagarjuna said...

Hi Radha,

I have one issue on dynamic file creation.

I have to generate one dynamic flat file from three flows (database).

It should be like below :

Source 1:
***************
Eno Ename DeptNo
---- -------- --------
1 X 10
2 Y 20

Source 2:
********************
DeptNo Dname
----------- -------
10 Finance
20 HR


and my target flat file should look like below :

Target :
-----------

Eno Ename DeptNo
--------- ------------- ---------
1 X 10
2 Y 20

DeptNo Dname
---------- --------
10 Finance
20 HR

Please help me on the above issue.

Thanks and Regards,

Nag

Sreekanth Addagada said...

Hi RadhaKrishna,

I have a requirement where I need to capture the source filename, date(File created), no: of records, file size. Can you please help me on how to capture this information.

Thanks & Regards,

Srikanth

 
HTML Hit Counters