Load data from Flat File to Oracle DB

Advantage:

Using this, we can load data from  flat file to Oracle easily,

To export Flat File to Oracle DB, we have to follow below steps.

Ø  For that, we have to Create project to create a interface

Ø  We have to   import knowledge modules

Ø  we need to define the 2 Data Servers, the 2 physical schemas and associate them with the relevant logical schemas in ODI Topology Navigator.  

Ø  Need to define 2 data models & create 2 data stores.

	Create project to create interface: Go to Designer Navigator. Select new project
	Import Knowledge modules:
	For this interface, It has to import a flat file into the SQL staging area, and then export the file to a different flat file. The Knowledge Modules (KMs) that are required for this are LKM File to SQL and IKM SQL to SQL Control Append. To import the KMs, click the Project tab in the left panel and expand the Knowledge Modules folder. Right-click Loading (LKM) and select Import Knowledge Modules.  Note: In this example, the generic SQL KMs are used. However, specific KMs for the RDBMS technology can be used as well.
	1.     On the screen that follows, in the File Name, navigate to xml reference directory as shown in the following screenshot. Click Open. The files to import should appear in the Import Knowledge Modules window. Press and hold the CTRL key to select IKM SQL to SQL Control Append and LKM File to SQL. Click OK.
	On Import Report window, click Close. Expand the Loading (LKM) and Integration (IKM) folders, and view each imported KM in the tree view as shown below.     Create a Data Server for FILE DB in Topology Manager Step: 1) Open up Topology Manager and go to Physical Architecture. Right click on the File technology and select New Data Server. In the Data Server window enter a name in the Name field. Step: 2) Go to the JDBC tab. Select the com.sunopsis.jdbc.driver.file.FileDriver  in the JDBC Driver List and jdbc url for jdbc:snps:dbfile  Step: 3) Click on Test and make sure you get a successful connection.

Create physical schema for file DB (technology):

 

	Click Topology tab. In Topology navigator, click the Physical Architecture tab, select Technologies->> File. Right-click FILE_GENERIC, and then select New Physical Schema. Enter the path to the directory, where your input flat files are located (C:\Users\Administrator\Desktop).   Create logical schema for file DB (technology): Click Topology tab. In Topology navigator, click the Logical Architecture tab, select Technologies->> File. Right-click and then select New Logical Schema. Specify the name and map it with Contexts.       Create a Data Server for Oracle DB in Topology Manager Step: 1) Open up Topology Manager and go to Physical Architecture. Right click on the Oracle technology and select New Data Server. In the Data Server window enter a name in the Name field. Step: 2) Go to the JDBC tab. Select the oracle.jdbc.OracleDriver in the JDBC Driver List and JDBC URL for jdbc:oracle:thin:@190.160.0.85:1521:SAPTRAINING Step: 3) Click on Test and make sure you get a successful connection. Create physical schema for Oracle DB (technology): Click Topology tab. In Topology navigator, click the Physical Architecture tab, select Technologies->> Oracle. Right-click SAP_TRAINING, and then select New Physical Schema. Enter the schema details. Create logical schema for Oracle DB (Technology): Click Topology tab. In Topology navigator, click the Logical Architecture tab, select Technologies->> Oracle. Right-click and then select New Logical Schema. Specify the name and map it with Contexts.
	Create Data model for source DB (FILE Technology):   Step: 1) Go to the Designer Navigator select model tab and create new model folder for Files ,  used for easily identification. Step: 2) Create new model for Files, used for storing tables & data stores. Specify the Name, Technology and Logical schema while creating new model.   Create data stores for source DB (FILE Technology)
Source: Flat File
		Step: 1) Expand the Flat Files folder. Select text file folder, right-click and then select New Datastore. On the screen that appears, set Name to source. Click the button  next to the Resource Name field. Select the Source_File.txt file and click Open.
		Step: 2) Click the Files tab. Set the File Format to Delimited & File Separate to comma(,) and then click the Columns tab.
		Step: 3) Check the Columns in the data store. Clicks save.   Step: 4) Expand flat file folder>text file >source (Source_File.txt) >Columns   to view your source model. Verify that the columns were successfully created as shown in the screenshot.     Create Data model & Data store for Target(Oracle DB)   Target: Oracle DB Step: 1) Go to the Designer Navigator select model tab and create new model folder for Oracle DB, used for easily identification.   Step: 2) Create new model for Files, used for storing tables & data stores. Specify the Name, Technology and Logical schema while creating new model.   Step: 3) Create data stores for target DB. Expand Oracle DB folder>SAP_TRAINING. Right click & select new data store   . Then enter the data store name & add the column specified data type. Click Save.     Step: 4) Check the columns in Data store. Expand Oracle DB folder>SAP_TRAINING> Columns   to view your source model. Verify that the columns were successfully created as shown in the screenshot.

 

Create interface for loading data from source to target table:

Step: 1) Go to Designer, click the Projects tab. Expand your project SAMPLE _WORK_ON_INTERFCAES_VTH_DIFF DB, and then expand First Folder.  Right-click interfaces and selects new Interface.

	Step: 2) Click the Models tab. Drag the source & target data store into interface mapping. When Designer asks "Do you want to perform an Automatic Mapping?" click Yes. The Diagram tab should look as follows. Click the Quick edit tab & set primary key.   Step: 3) Go to flow tab. Click the source table. 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 Step: 4) Click the target table. This makes the properties for the target appear below. This makes the properties for the target appear below. For IKM, select IKM SQL to SQL Control Append from the IKM drop-down list if not already selected. Set the IKM options create target table to true & Flow control to false as shown below. Click Save icon to save your interface. Step: 5) To test your interface click Execute icon. Then following screen appears.              Step: 3) To verify that your interface was executed successfully, you need to open ODI Operator Navigator. Click the ODI Operator tab. In ODI Operator, click Session List. Expand Sessions > FLAT TO Oracle, and view the execution results for each step. Double-click any step and click the Description and Execution tabs to see the execution results. Double-click step 7. View the number of rows that are inserted into the target flat file. Click OK. Close the tabs.   Step: 3) Go to Oracle DB model and select target data store(Flat to Oracle).  Next right click the target data store and select the view data for output. Note: First Target table have empty records. After execute the interface target table have 14 records