Hello everyone!
My today’s tutorial will focus on migrating data from a flat file to an Oracle table. I would be creating a simple text file (.txt). Using Oracle Data Integrator, I will load the source file data into my target table in Oracle.
Pre-requisites: Oracle 10g Express Edition with *SQL Plus, Oracle Data Integrator 11g (Build Version 11.1.1.7.0)
Create a flat file named file_data.txt that contains the following:
Id Name
1 Karan
2 Mahesh
3 Prasad
4 Hanif
1 Karan
2 Mahesh
3 Prasad
4 Hanif
Create a table named KARAN_FILE in the Oracle database.
create table KARAN_FILE(custid int primary key, custname varchar2( 30 )); |
Open ODI Studio. Connect to your work repository and follow below steps!
Step 1: Create data server and physical schema for table
Step 2: Create logical schema for table
Note: Make sure you test your connection by clicking on the Test Connection button while creating your data server!
Step 3: Create data server and physical schema for flat file
Step 4: Create logical schema for flat file
Note: Make sure you test your connection by clicking on the Test Connection button while creating your data server!
Step 5: Create new model folders to contain table and file data models respectively
Under the Designer tab, go to Models and create a new model folder named customer_data. Add a new model with same name to that folder by reverse engineering your target table.
Similarly, create a new model for file named file_data under folder namedfile_model as follows:
Right click the created Model –> New Datastore. Add details to the data store.
Reverse engineer the file and add the appropriate column names and data types.
Step 6: Create a new interface named file_to_table
Perform Mapping between source i.e flat file and target i.e oracle table. Select LKM as File to SQL and IKM as Oracle Incremental Update underFlow.
Make sure no errors are present. Now, run the interface and check the status of the operation under the Operator tab.
If the operation is successful the data from your file is now successfully inserted in the target table!
That completes this tutorial. Keep visiting for more tutorials on ODI
Comments
Post a Comment