LOADING DATA FROM MULTIPLE FLAT FILES TO SINGLE TARGET TABLE

Implementing the steps for loading the data from multiple flat files to

 single oracle table

Source:

• Let us consider the Flat files(txt files) namely

            1) flatfile1.txt

            2) flatfile2.txt

            3) flatfile3.txt

            4) flatfile4.txt

            5) flatfile5.txt

keep all these files in a folder and save it in a Local Drive.

• These flat files are having same structure with unique data and to be considered as the source.

• In oracle DB, create a new schema as source and grant DBA permissions to it. In this schema, create a table(KC) with the columns as FID, FILENAME, STATUS and insert the values as follows

FID             FILENAME              STATUS

-----            ---------------            ------------

  1                flatfile1                    N

  2                flatfile2                    N

  3                flatfile3                    N

  4                flatfile4                    N

  5                flatfile5                    N

• This table could be called by refreshing variable at ODI level.

Target:

• In Oracle DB, in the same schema create a table (CL) with columns similar to the flat files structure (i.e., the columns present in the flat files are to be taken).

• Data is to be loaded after executing the ETL process.

Loading data from Source to Target:

• In ODI, login to work repository. We have the source as Flat file. So in Physical Architecture select the Technology as File Technology.

• File Technology -->New Data Server --> Name: Give any name.

• Go to  JDBC ,

JDBC Driver: Click on search icon, keep the JDBC Driver as appears and

                        click ok.

JDBC Url      : Click on search icon, keep the Url up to jdbc:snps:dbfile and

                        delete the   remaining and click ok.

• Data Server for source is created.
• Now create Physical Schema for this Source Data Server.
• Right click on the Data Server select Physical Schema.
• In the Physical Schema (Definition), select

                 Directory (schema)          : Place the path were the flat files are saved

                 Directory (work schema): Place the same path as above

• Now move to context and set the logical schema. Select context as Global & set Logical Schema with any name ending with “_LS”.

• Hence, the process completed for file technology.
• Now select the Oracle Technology in Physical Architecture for the Target.
• Oracle Technology -->New Data Server --> Name: Give any name.

• Give the User name & Password as the Schema and password created for the Target.

• Move to JDBC,

          JDBC Driver: Click on search icon, keep the JDBC Driver as it appears and click ok.

          JDBC URL    : Click on Search icon, set

                                    Hostname as localhost, Port number as 1521, SID as orcl

• Data Server is created for Target.

• Now create Physical Schema for the Target Data Server.
• Right click on the Data Server, select Physical Schema.
• In the Physical Schema (Definition), select

Directory (schema)           : Point out the Schema which was created

                                           for target.

Directory (work schema) : Same as above

• Now move to context and set the logical schema.

• Hence, the process completed for the Oracle Technology.

• Now go to Designer component and create a new Model folder

• Now create a new model for source (i.e., for flat files) as follows

• Here, the source is Flat file. So for this we have to create Datastore.
• Right click on the source model and select New Datastore. It displays a window.

• In this, for Resource Name: Point out the flat file name with “.txt”  extension as below

• Now create a model for Target

• Move to selective Reverse-Engineering and  follow as below

• Reverse Engineering for target is completed.

• Create a Global Variable as below

• Move to Refreshing, set the Schema and for Select Query write the query as follows

• Create a New Project

• In this Project, Create a Project Variable as below

• Move to Refreshing, set the Logical Schema and Select Query as below—

• Now create an Interface

Now change your resource name as the global variable name .

Note: change resource name before using them in the mappings.

• Now go for Mappings

• Import the Knowledge Modules as per the requirement

For LKM select FILE TO SQL

For IKM select SQL CONTROL APPEND

• Set  Flow Control as TRUE
• Set Truncate as False

• Create a Procedure as follows

• Go to Execution and make as follows

• Create a Package in this Project and arrange as below

1. v_filecount as refresh variable
2. v_filecount as evaluate variable and remaining variables as
3. refresh variables

• The arrangements should appear like below

• Now save and execute it.
• Check the status in Operator Component.
• Hence, the data loaded into Target Oracle Table.

• Also check the KC table in DB. Observe that the status was updated to “Y”.

              FID              FILENAME              STATUS

-----             ---------------            -----------

     1                  flatfile1                       Y

     2                  flatfile2                       y

     3                  flatfile3                       y

     4                  flatfile4                       y

     5                  flatfile5                       y