Some time you want to update a single table from different Flat File Sources.

This post shows how to carry out such logic with the assumption all the file are stored in a single directory and they all have the same structure and file formatting.

I have implemented this using two option

1. Using a Temp Data store

2. Using Procedure.

Variable Used

LOOP	USED TO ASSIGN AND INCREMENT  NO OF TIME FLOW HAS TO BE PROCESSED
GET_INTERFACE	SOURCE FILE TO BE PROCESSED BY INTERFACE
CHECK	USED TO CHECK WHETHER ALL FILES ARE PROCESSED OR NOT AT EVERY LOOP

All the variable are Alphanumeric and Historize property.

Using Temp Data store

Temporary Interface – FILE_LIST

Get the list of files in the Temporary Datastore  in Work schema, so that we can process Interface sequentially based on the list of files.

Next Step is to Assign the variable  ‘ LOOP ‘ = 0 (Zero )

Next Step is to get the File Name to be processed the Interface . Refresh the Variable.

Apply the required query so it fetches the next record in the loop.

Here iam getting the ‘ File name ‘ to be processed based on the Loop Number so the First loop will fetch the First file , second loop next file and  keep doing so for others.

SELECT  FILE_NAME FROM <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST
WHERE S_NO=#LOOP

The Next step is to use that File and process the Interface. Here in the Model i have used the  ‘ Resource Name – #GET_INTEFACE ‘ as i want to get  the right file for processing.

Next Step is to Refresh the Variable ‘ Check ‘ ,which checks if all the interface is been processed or not.

In this Step iam checking whether all the interface is been processed or not.

The logic is pretty simple to subtract the Loop value from the Total count , so if i have  10 files  in the first loop i would have

Count(*) = 10

Loop        = 1 so remaining interface 10-1 =9 to be process and when its Zero i stop the Flow .

SELECT COUNT(*)-#LOOP  FROM  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST

Next Step is to increment the Value of  ‘ LOOP’  as there are still interface to be processed.

This is summary of  the overall flow.

1. Get the File list into the Temporpary Datastore ( ‘FILE_LIST’)
2. Assign the Loop to Zero
3. Get the Next File to processed.
4. Process the Interface by passing the value from #GET_INTERFACE  variable
5.  Refresh the Variable Check
6. Check if there are still  Files to be processed or not. If so Next step , else Exit .
7. Increment the Loop by 1.

Using ODI Procedure

You can perform the above logic , using an ODI Procedure , instead of a temporary datastore.

I personally would prefer this method as its easy to maintain and modify when required.

The steps are very simple and as follows.

1. Drop the Temporary Table.
2. Create the Temporary table in the Work Schema.
3. Insert the Required File and Values.

DROP TABLE <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST

CREATE TABLE <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST
(S_NO NUMBER(3),
FILE_NAME VARCHAR2(50))

INSERT ALL
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST VALUES (1,’FILE1.TXT’)
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST VALUES (2,’FILE2.TXT’)
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.FILE_LIST VALUES (3,’FILE3.TXT’)
SELECT * FROM dual

[ Note : Please note its a best practise to use a  unique work_schema and use in all the Physical schema defination . I have used a common work_schema called ‘ WORK_SCHEMA’  , so when i create all the temporary data store or procedure they are all in the Temporary work schema ‘ WORK_SCHEMA’ . The other good point will be that if any error occurs during the Interface  and due to some reason the temporary objects like C$_,I$_ etc dont get dropped and they will be seen in the main table.]