Tuesday, February 8, 2022

ODI - Read local Flat csv file and insert to Oracle database


  • Go to Topology and under Physical Architecture, choose File technology and create new data server. 
    • Provide dataserver name.
  • Under created dataserver , create new physical schema 
    • provide the name, directory path as directory(schema) and directory(work schema).
    • Create a context and add the logical schema name
  • Choose Technology as Oracle and create new data server.
    • Provide data server name
    • User and pwd 
    • Choose jndi connection or use credential file. Here we chose use credential file and selected the path.
  • Under created dataserver, create new physical schema
    • Provide name
    • Provide db user as schema,(schema and work schema)
    • Context and add logical schema
  • Go to Design tab and create a model folder 
    • Create a model for file 
      • Provide name, technology as File, choose created file logical schema and save
      • Under the file model, create a datastore 
        • Provide name, choose a sample test.csv file as resource name.
        • Go to files and provide the format like delimited, no of headings, unix or ms dos os, field separator as ","  etc
        • Go to attributes and reverse engineer, you can see all the file fields here.
      • Under the create file datastore, view data
    • Create a model for oracle db
      • Provide name, choose technology as Oracle , logical schema
      • Reverse engineer and provide mask , here %POC%
      • Selective Reverse engineering and select all the required options and it will show your table and click on reverse engineer.
  • Projects and create a new project and ceate a new folder and go to mappings and create a map
    • Drag and drop created data stores and map all the fields
    • Go to physical and choose LKM as LKM file to SQL and IKM as IKM Oracle insert or IKM Oracle control append. And flow control as false as we dont have any PK on datastore.
  • Run the map and go to operator tab
    • Open session list and date check all steps and also verify in table where rows inserted or not.

POC Screenshots:

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...