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.
- Target_file which store your all file_name with id and status
- 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 2:We have Three CSV file with following Structure.
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.
step 4: Create target table TEMP_TABLE which has same structure as source file
step 5: Create data server for file technology
step 6: Give name of data server
step 7:Define JDBC driver and JDBC URL for file
step 8: Create physical schema for file technology
step 9:define schema and work schema(source file location)
step 10: define logical schema
step 11: Define data server for oracle technology
step 12: give name of data server and give connection detail of Oracle DB.
step 13: Define JDBC connection
step 14: Test connection
step 15: Define physical schema for data server
step 16: Select Schema from Oracle DB
step 17: Creating logical schema.
step 18: Creating source model for file
step 19:Give name of model and define technology and logical schema
step 20: Create new data store to store file data which has same strcture
as a file
step 21: Give name of data store
step 22: define file format
step 23: Add attribute which same as your file
step 24: Creating target Model
step 25: Give name of Model
step 26: Select reverse engineer type as standard
step 27: Define Selective Reverse-Engineering and Select TEMP_TABLE
step 28: import TEMP_TABLE
step 29: Source: Multiple_source
step 30: Create New mapping
step 31: Give mapping name
step 32: Drag source and target.
Map source and target Attribute(automatically by name or Manually)
Define integration type
step 33: Define ID as Key.
step 34: Define LKM file to SQL
step 35: Select IKM
step 36: Define CKM
step 37: Validate mapping
step 38: Create Variable File_count
step 39: Give name of variable
step 40: Define query to get number of file and validate query
Select count(*) from hr.target_file where status=’N’
step 41: Create variable File_name to get name of file
step 42: Define query to get file name
(Select file_name from hr.target_file where status=’N’)
step 43: Create procedure to update status of Processed file
step 44: Define name of Procedure
step 45: Click target command to define query
step 46: Query to update file status
step 47: create package to Define Execution flow
step 48: Drag and drop component in Package window
step 49: Refreshing value of File_count
step 50: Evaluating file count variable.
step 51: Refreshing value of File_name
step 52: Mapping to load file into target table
step 53: updating file status using procedure
step 54: again drag same variable file_count
step 55: click to run package
step 56: Check status in Operator
step 57: Now you can see data in TEMP_TABLE
step 58: See all file status is Y it means All file processed successfully.