Loading flat file into Oracle using Oracle Data Integrator – Part 5. Pushing flat file data to Oracle table using Oracle Data Integrator

In my previous blogs, I have covered the following: -

• Part 1. Creating the Physical Architecture and Physical Schema for File : http://exploitsinhyperion.blogspot.in/2016/11/loading-flat-file-into-oracle-using.html
• Part 2. Creating the Logical Schema and the Data Store for File : http://exploitsinhyperion.blogspot.in/2016/11/loading-flat-file-into-oracle-using_19.html
• Part 3. Creating the Data Server and Physical Schema for Oracle Database : http://exploitsinhyperion.blogspot.in/2016/11/loading-flat-file-into-oracle-using_20.html
• Part 4. Creating the Logical Schema and Data Model for Oracle Database : http://exploitsinhyperion.blogspot.in/2016/11/loading-flat-file-into-oracle-using_69.html

In the final blog, I would be talking about how to push data from a flat file to Oracle Database table using Oracle Data Integrator. All the previous blogs, were a run up to this stage since this is the fun part. In order to push data from a flat file to a table, we need to create mappings in ODI. 

In order to create a mapping, the first thing that I do is create a Project in the Designer tab as shown in the below snapshot. 

Now, if you expand the projects folder, as shown in the below snapshot, you will see a mappings button.  

Right click on the mappings to create a new mapping as shown in the below snapshot.

I give a name to the mapping as shown below and save it.

On hitting the Save button, the designer screen pops up as shown in the below snapshot. This can be populated by dragging and dropping components (files, tables, etc.) from the Model folder. 

The first thing I do is drag the source file from Models and push it to the Designer pane as shown in the below snapshot. 

Observe that the fields are populated based on the File mapping we created previously as shown in the below snapshot.

The next snapshot shows me pulling the Oracle flat file target table to the designer pane as shown in the next snapshot. 

Observe that the FLAT_FILE_TARGET has the same fields as the Oracle database table.
Now, I connect the two by forming a link between them as shown by the line in the above snapshot.
On creating a Link, the ODI studio asks me how I want to match the fields/attributes. This is shown in the next snapshot. 

I select by position and Auto Map as shown in the above snapshot and click on the OK button.
The source and target systems connected after the mapping is as shown in the below snapshot. 

The next snapshot shows the contents of the FLAT_FILE_TARGET table in Oracle. This table is currently empty. 

In order to run the Mapping, I click on the Green play button as shown in the below snapshot. 

The system asks me if I want to lock the object for concurrency control and I click on the Yes button to lock the artifacts.
The next snapshot shows the system asking me the configuration parameters for the Run. 

I keep the defaults as is and hit on the OK button to start the load. (For folks who use FDMEE, observe the Log Level is set to 5 which the black box mode in FDMEE. ODI engine works at backend for FDMEE.)
 

Now, to check the status of the load, you need to go to the Operator tab and click on the Session tree node to expand it as shown in the below snapshot. Observe that the load has completed successfully.

On clicking the View Data for the Oracle table, observe that the data has been pushed successfully to the Oracle table as shown in the below snapshots.