Search This Blog

Thursday, March 24, 2011

Informatica: dynamic filelist generation from metadata

Purpose: How to build dynamic filelist for Informatica.
Required:  File and Source metadata

Step1:  Create Informatica mapping.
Step2:  Drop source(s) in mapping that will drive unique filename, source name, filelist name.  One example would be building a filelist of files to load that have not been logged (loaded in ETL system) or run but logged a failure (re-run due to failure).
Step3: I suggest using an aggregator post joiner (if used) to get a list of unique filenames, source names, filelist names.
Step4: Add a sort transform to put data in order by filelist name
Step5: Add function transform to generate output columns.
Needed: full file path (from variable) appended to filename (ex: $$landing_dir||'/'||FILENAME).
Add 3 ports.
vNEW_FILENAME_IND (var): IIF(FILELIST_NAME = vPREV_FILELIST_NAME,0,1)
vPREV_FILELIST_NAME (var): FILELIST_NAME 
NEW_FILENAME_IND (output port): vNEW_FILENAME_IND
Step6: Add transaction control transform
Map the filename+full_path port, New Filename Ind port, Filelist name port
For the TX control condition: IIF(NEW_FILENAME_IND=1,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)
Step7:  Map to Flat file target
Filelist name port and Filename+path port

No comments:

Post a Comment