Informatica Advance techniques --File Watcher (Using Java Transformation)

We are going to have a look at File watcher utility in informatica to look for incoming files from external server. It utilises Java transformation in informatica and create indicator/trigger files once file has been downloaded completely.

It is particularly useful in those scenarios where external system is sending file to our server but we don’t know which job so we can’t setup dependency in scheduling tool (Control M/Autosys etc) .Normally we use time condition to cater for this which is not always correct. Our job fails if file does not arrive on time.

We can utilise file watcher along with Informatica event wait feature to cater for these kinds of scenarios.

Design a mapping Let say we call as m_File_Watcher_Start

We need to create a Java transformation to cater for this. 

Create one input port as STAGING_FILE and three output port as 

FILE_SIZE

FILE_EXISTS

FILE_TIME

Write below code in import packages.

import java.io.File; // Used to facilitate file and directory object representations

import java.text.DecimalFormat; // Used to format file and directory objects size

import java.util.*;

import java.text.*;

Write below code in on input row.

FILE_SIZE = getFileSize( STAGING_FILE);

FILE_TIME =getFileTime(STAGING_FILE);

File f = new File(STAGING_FILE);

if(f.exists()){ FILE_EXISTS="Y"; }else{ FILE_EXISTS="N"; } 

Here we have got below details about File size, File Time and whether file exists or not.

We can create a table with below details: FILE_WATCHER_DETAILS

CREATE TABLE FILE_WATCHER_DETAILS

  (

STAGING_FILE  VARCHAR2(100 BYTE), 

TRIGGER_FILE VARCHAR2(100 BYTE), 

FILE_TIME VARCHAR2(100 BYTE), 

FILE_SIZE_KB VARCHAR2(100 BYTE), 

FILE_EXISTS VARCHAR2(1 BYTE), 

TRIGGER_DONE VARCHAR2(1 BYTE), 

TRIGGER_PICKED VARCHAR2(1 BYTE)

  )

Suppose we have file Input_file.txt and size of file is 50 KB below will be data in table

STAGING_FILE Input_file.txt   

TRIGGER_FILE Input_file.txt.trg (append .trg at end of file)   

FILE_TIME 04/02/2014 13:05:09   

FILE_EXISTS Y   

FILE_SIZW 50 KB   

TRIGGER_DONE N  

Design a new mapping Let say we call as m_File_Watcher_End

Create mapping similar to earlier one but slight difference in java transformation with two input ports STAGING_FILE and Trigger File and output port Trigger_done (Which will be assigned value ‘Y’ in case file size is same ).

We will call these two mapping between two sessions with time interval of 5 mins.This is done to make sure file has been completely downloaded.

In this Java transformation we are check new file size vs old size as there will be 5 min gap between running of these two mappings.If size is same then create trigger file

FILE_SIZE_KB = getFileSize( STAGING_FILE);

OLD_FILE_SIZE_KB=OLD_FILE_SIZE_KB;

if (FILE_SIZE_KB.equals(OLD_FILE_SIZE_KB) ) {

File file = new File(TRIGGER_FILE); 

boolean fileCreated = false; 

try { 

        fileCreated = file.createNewFile();

      } 

catch (IOException ioe) { 

System.out.println("Error while creating empty file: " + ioe); 

} 

}

Final Value in table will be 

STAGING_FILE Input_file.txt   

TRIGGER_FILE Input_file.txt.trg (append .trg at end of file)   

FILE_TIME 04/02/2014 13:05:09   

FILE_EXISTS Y   

FILE_SIZW 50 KB   

TRIGGER_DONE Y  

In the end we need to create a Workflow with two sessions and a timer of 5 minutes between them.