LOADING DATA FROM FLAT FILE to FLAT FILE

LOADING DATA FROM FLAT FILE to FLAT FILE

1. A common task that is performed using ODI is to export data from a flat file and load this data into another flat file. This tutorial walks you through the steps that are needed to create an interface that will import a file to a staging area, perform some minor transformations, and then write the data to a file.
2. You also execute the interface and verify the execution using ODI Operator.
3. Actually ODI is a ELT tool ,in this scenario it is going to act as ETL tool .
4. As we are using Source and Target as flatfiles if we want do any transformations then need to achieve by introducing A staging area .

STEP 1 : Creating and Reverse Engineering Source

1. Go to Any one path and create a flat file with below data

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 7369,SMITH,CLERK,7902,17-DEC-80,960,,20 7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30 7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30 7566,JONES,MANAGER,7839,02-APR-81,2975,,20 7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30 7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30 7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10 7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20 7839,KING,PRESIDENT,,17-NOV-81,5000,,10 7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30 7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20 7900,JAMES,CLERK,7698,03-DEC-81,950,,30 7902,FORD,ANALYST,7566,03-DEC-81,3000,,20 7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

2. Save this file with the name of EMP.txt

3. Create a data server under FIle Technology

4. Create a physical schema as shown

5. Create a logical schema and map physical and logical schemas as shown

6. Create a model as shown

7. Create a EMP data store

STEP 2 : Creating and Reverse Engineering Target

1. Duplicate EMP data store and rename as TGT_EMP

STEP 3 : Creating Interface/Mapping

1. Create interface with the name of m_FlatFile_to_FlatFile
2. Select Staging area different from target  → Select In Memory Engine Sunposis_memory_engine

3. Click on mapping tab → Drag and drop source and target in respective locations

4. Click on Flow tab
5. Select source and select LKM File To SQL
6. Select Target and select IKM SQL to File Append

7. Click on SAVE
8. Click on RUN

9. Observe Target flat file data