Transfer data from one Flat File to another Flat File.

First of all connect your ODI to work repository. And then go to Topology tab.

In Physical schema window of Topology tab select the technology as Flat_File.

Now expand the Flat_File technology and select and right click on FILE_GENERIC data server. You can also create your own data server.

Now create a new physical shema inside FILE_GENERIC Data Server.

Now go to you’re my computer and select any Drive and create a folder and inside that folder create two file.

1. SRC_SALES_PERSON.TXT
2. TRG_SALES_PERSON.TXT

And the copy the location of your folder in which above two files residing.

Also put some data inside your SRC_SALES_PERSON and save it.

Leave blank your TRG_SALES_PERSON file .

Now inside Physical schema paste the location you have copied inside Directory (Schema) and Directory (Work Schema) and save your application.

Now go to Logical Architecture tab and select New Logical Schema.

Now create a Logical Schema inside Flat_File technology inside the Logical Architecture window.

And select the Physical Schema which you have created for context.

Now go to Physical Schema window and select the logical schema for physical schema in context tab.

Now go to Designer tab and create a new project .

Name the project and ODIExp_FT_FT and save the changes.

Now expand the project and select Knowledge Module.

What is Knowledge Modules ?

Knowledge modules (KMs) in Oracle Data Integrator are components that implement reusable transformation and ELT (extract, load, and transform) strategies across different technologies. One example of this is extracting data through change data capture from Oracle Database 10g and loading the transformed data into a partitioned fact table in Oracle Database 11g, or creating timestamp-based extracts from a Microsoft SQL Server database and loading this data into a Teradata enterprise data warehouse.

Right click on Knowledge module and select Import Knowledge Module.

Select IKM SQL to File Append Knowledge module and click on Ok. And then close the window of imported Knowledge Module.

Again right click on Knowledge module and select Import Knowledge Module. Select LKM File to SQL Append Knowledge module and click on Ok. And then close the window of imported Knowledge Module.

Now go to Model tab and create a New Model.

Name the model as Flat_File_1 and select the Technology as File and Logical Schema which you have created.

Now select the context as Global in Reverse Engineer tab.

Now right click on model which you have created (Flat_File_1) and select New DataStore.

What is DataStore ?

A DataStore is a structure that allows data to be stored. It can be:

§ a table,

§ a file,

§ a message queue

§ Or any other data structure accessible by middleware used by Oracle Data Integrator (JDBC/ODBC, JMS or JNDI).

In our present case DataStore is a file.

Name the DataStore as SRC_SALES_PERSON and select the source file from where data is to be fetch by clicking on magnifier symbol of Resource Name.

Now switch to Files tab and select File Format as Fixed.

Now select Reverse Engineer icon inside Column tab of DataStore window.

Point the cursor, and then click the ruler at the beginning of each column as shown below. The wizard marks each column.

After the columns are marked, click each column data and set the Name and Data type for each column. Click OK when you have finished.

Click OK to save the changes.

Now right click on SRC_SALES_PERSON under Model tab and select Duplicate Selection option. A copy of SRC_SALES_PERSON will be created. Now right click on it and select View Option.

Rename the file as TRG_SALES_PERSON and select TRG_SALES_PERSON.txt as Resource Name. And select File Format as Fixed in file tab.

Now go to your Project and select Interface option. Right click on Interface icon and create a new Interface.

What is Interface ?

An interface is an object in Oracle Data Integrator which populates one datastore, called the target, which data coming from one or more other datastores, known as sources.

The columns in the source datastore are linked to the columns in the target datastore through business rules.

Name the interface and select checkbox below Operation context (Staging Area Different From Target) and select In-Memory Engine: SUNOPSIS_MEMORY_ENGINE

Now go to Mapping tab and drag and drop the SRC_SALES_PERSON datastore from the Files folder into the Sources container. Drag the TRG_SALES_PERSON.txt datastore from Files into the Target Datastore container.

When Designer asks "Do you want to perform an Automatic Mapping?" click Yes. The Diagram tab should look as follows. Click the Flow tab.

Click the SS_0 (1 - FILE_GENERIC) source datastore. This makes the properties for the source appear below. In the LKM Selector section, select LKM File to SQL from the LKM drop-down list if not already selected.

Click Target (FILE_GENERIC). This makes the properties for the target appear below. For IKM, select IKM SQL to File Append from the IKM drop-down list if not already selected.

To test your interface right click on interface you have created and select Execute option.

The following screen appears. Retain the defaults and click OK. On the screen that follows, click OK.

Click Ok.

To verify that your interface was executed successfully, you need to open ODI Operator. Click the ODI Operator tab. In ODI Operator, click Hierarchal Sessions. Expand Date > Today > INT_exp_FF_FF > Steps > INT_exp_FF_FF, and view the execution results for each step. Double-click any step and click the Description and Execution tabs to see the execution results.

Now go to your folder in My Computer and verify whether data has been written to TRG_SALES_PERSON.TXT file or not.