Transfer data from one Flat File to another Relational Table.

 

Open your SQL DEVELOPER and connect to the Administrator role.

Create the new schema/user by executing the following SQL commands:

Create user ODI_STAGE identified by oracle1

default tablespace users temporary tablespace temp;

Grant connect, resource, create trigger, create view to ODI_STAGE;

Now connect to your schema by mentionaing ODI_STAGE as user and oracle1 as password.

Now Create table in ODI_STAGE schema by executing following query in SQL Developer.

CREATE table "TRG_SALES_PERSON"(

"SALES_PERSON_ID" NUMBER (8, 0) NOT NULL,

"FIRST_NAME" VARCHAR2 (80),

"LAST_NAME" VARCHAR2 (80),

Constraint "TRG_SALES_PERSON_PK" primary key ("SALES_PERSON_ID")

)

Now create a project in ODI Suit. And name the project as ODIExp_FT_RT and save the changes.

Also import IKM SQL Incremental Update Knowledge module

Now import the Knowledge module LKM File to SQL .

Now go to Oracle technology under Physical Architecture and right click and select New Data Server.

Name the Data Server as ODI_STAGE. And specify the instance as localhost , User as ODI_STAGE and password as oracle1.

Then go to JDBC tab and specify the driver and url.

Save the result and click ok on dialogue that appears for physical schema.

Now right click on Data Server that is newly created and select new physical schema option.

Fill out the required detail that is Directory as ODI_STAGE and Directory (Work Schema) as ODI_STAGE. Save the changes and click ok on dialogue that appers to specify the context.

Now go to Logical Architecture tab and right click on the Oracle technology and select New Logical Schema.

Name the Logical schema as ODI_STAGE and set the physical schema for the context inside logical schema window.

Now go to Designer tab and click on the icon in Model window and select New Model Folder.

Name the Folder as RDBMS and save the changes.

Right click on the folder just created and select New Model option.

Name the Model as Oracle_RDBMS1 and select technology as Oracle and logical schema as ODI_STAGE.

Now right click on model just created and select Reverse Engineer.

Now go to File technology and create and create a physical schema for our file.

Create logical schema for the file.

Now create a model folder in model tab.

Now create a new model inside files folder.

Create a DataStore inside model of file.

Selct the SRC_SALES_PERSON.TXT as 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.

Right click on data store newly created and select view data option.

You can see the data inside file.

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

Name the interface as INT-EXP-FF-RT .

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 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 Incremental Update from the IKM drop-down list if not already selected.

Now right click on the interface and select the 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_RT > Steps > INT_exp_FF_RT, and view the execution results for each step. Double-click any step and click the Description and Execution tabs to see the execution results.

To verify the result connect to ODI_STAGE user and execute the following SQL command:

Select * from TRG_SALES_PERSON;

You can see that data is inserted successfully.