Skip to main content

Multiple file loading into single target

Loading Multiple file with same structure into Single target in ODI 12C

  • Suppose if we have multiple files of same structure to be loaded into a single target table. we can use a single Mapping to load all the flat files of same structure into the single target table.
    i.e. Suppose If we have 80,000 file with same structure and we need
    to load that File into single target using single mapping in ODI 12C
  • Suppose if we have three Flat files namely  src1.csv, src2.csv and src3.csv to be loaded into Temp_table which is table of ORACLE database.
Solution
1. Create two table in database

  1. Target_file which store your all file_name with id and status
  2.  Temp_table which has same structure as your source file.

2. Create data server for file and oracle technology and register physical and logical schema for each.

3. Create two variable file_count to get number of file and File_name to get name of file

4. Create Mapping to load file into target table.

5. Create procedure to update status of file.
Default status of File is N and it showing file is not Processed.

6. Create Package to define flow of Execution

step 1: Consider we have three csv File
y25

step 2:We have Three CSV file with following Structure.
y25

step 3: Create database table TARGET_FILE and insert your file_name with
default status N.

In Real time scenario for it We usually write script java or python
to create table and insert file data.
y25

step 4: Create target table TEMP_TABLE which has same structure as source file

y4
step 5: Create data server for file technology
y25

step 6: Give name of data server
y25

step 7:Define JDBC driver and JDBC URL for file
y25

step 8: Create physical schema for file technology
y25

step 9:define schema and work schema(source file location)
y25

step 10: define logical schema

y25

step 11: Define data server for oracle technology
y25

step 12: give name of data server and give connection detail of Oracle DB.
y25

step 13: Define JDBC connection
y25

step 14: Test connection
y25

step 15: Define physical schema for data server
y25

step 16: Select Schema from Oracle DB
y25

step 17: Creating logical schema.
y25

step 18: Creating source model for file

7

step 19:Give name of model and define technology and logical schema
y25

step 20: Create new data store to store file data which has same strcture
as a file
y25

step 21: Give name of data store
y222

step 22: define file format
y25

step 23: Add attribute which same as your file
y25

step 24: Creating target Model
y25

step 25: Give name of Model
y25

step 26: Select reverse engineer type as standard
y25

step 27: Define Selective Reverse-Engineering and Select TEMP_TABLE
y25

step 28: import TEMP_TABLE
y25

step 29: Source: Multiple_source
Target: TEMP_TABLE
y25

step 30: Create New mapping
y25

step 31: Give mapping name
y25

step 32: Drag source and target.
Map source and target Attribute(automatically by name or Manually)
Define integration type
y25

step 33: Define ID as Key.
y25

step 34: Define LKM file to SQL
y25

step 35: Select IKM
y25

step 36: Define CKM
y25

step 37: Validate mapping
y25

step 38: Create Variable File_count
y25

step 39: Give name of variable
y25

step 40: Define query to get number of file and validate query
Select count(*) from hr.target_file where status=’N’
y25

step 41: Create variable File_name to get name of file

y25

step 42: Define query to get file name
(Select file_name from hr.target_file where status=’N’)
y25

step 43: Create procedure to update status of Processed file
y25

step 44: Define name of Procedure
y25

step 45: Click target command to define query
y25

step 46: Query to update file status
y25

step 47: create package to Define Execution flow
y25

step 48: Drag and drop component in Package window
y25

step 49: Refreshing value of File_count
y25

step 50: Evaluating file count variable.
y25

step 51: Refreshing value of File_name
y25

step 52: Mapping to load file into target table
y25

step 53: updating file status using procedure
y25

step 54: again drag same variable file_count
y25

step 55: click to run package
y25

step 56: Check status in Operator
y25

step 57: Now you can see data in TEMP_TABLE
y25

step 58: See all file status is Y it means All file processed successfully.
y25