How to load multiple files(of same structure) to single target table using single interface/Mapping in ODI?

How to load multiple files (of same structure) to single target table using single interface/Mapping in ODI?

Pre-requisities:

File Structure of all the files to be same.

Naming conversion of the file should be decide earlier.

In this example, I am considering sample1,sample2 and sample3 as naming conversion of the file and File Structure as follows for all the files.

ID,PROJECT_NAME (.csv delimited with ',')

Procedure:

Step 1:

Topology Configuration

Create Physical Architecture for Target 

• Expand Technologies -- Right click Oracle Technology -- Click on New Dataserver
• Provide DB details and save it
• Right click the Dataserver and select Select New Physical Schema and provide Schema details

Create Physical Architecture for Source

• Expand Technologies -- Right click File Technology -- Click on New Dataserver
• Provide File server details and save it
• Right click the Dataserver and select Select New Physical Schema and provide Folder location

Step 2:

Create Logical Architecture for Target

• Expand Technologies -- Right click Oracle Technology -- select New Logical Schema
• Provide Logical Schema Name (whatever you want)

Create Logical Architecture for Source

• Expand Technologies -- Right click FileTechnology -- select New Logical Schema
• Provide Logical Schema Name (whatever you want)

Step 3:

Create Context

• Click on New Context
• Provide Context Name (whatever you want)
• Go to Schemas tab and Select Physical Schema for appropriate Logical Schema(

Step 4:

Create Target Model 

• Click New Model
• Provide Name , Select Technology as Oracle and Select Logical Schema which you created earlier.
• Go to Reverse Engineering Tab -- Select Context which we created earlier and provide table name for "Mask" input text box
• Go to selective Reverse Engineering tab and select appropriate table and click on Reverse Engineering
• Your datastore is created successfully

Create Source Model

• Click New Model
• Provide Name , Select Technology as File and Select Logical Schema which you created earlier.
• Right on your Model and select New Datastore
• Provide name and select the sample1.csv file
• Go to Files tab -- File Format -- Delimited and Header -- 1
• Under Field Separator -- Others -- provide ','
• Go to Attributes and click on reverse Engineering
• Go to Definition -- Resource Name : Sample#<PROJECT_NAME>.<VARIABLE_NAME>.csv and save it

Step 5:

Create Project

• Create new mapping
• Drag and drop both source and target datastores. Link the source output connector to target input connector. Do mappings as per your requirement
• Go to Physical -- Select staging area and select KM as LKM File to SQL and Select Target datatstore and select KM as IKM SQL control Append

IMPORTANT (MAIN LOGIC)

Step 6:

Create Package

• Create Variable in your project
• Create Package and drag and drop your variable and mapping which you created earlier
• Connect 'OK' from variable to Mapping
• Generate Scenario to that package

Step 7:

Create Procedure

• Create Proceudre, In target side select technology as ODI tools and for Source side select Oracle as technology and select logical schema as well.
• Target side : OdiStartScen "-SCEN_NAME=PKG_MULTI_FILES_LOAD" "-SCEN_VERSION=001" -<PROJECT_NAME>.<VARIABLE_NAME>=#FN
• Source Side : SELECT REGEXP_SUBSTR('1,2,3','[^,]+',1,LEVEL) FN FROM DUALCONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('1,2,3','[^,]+'))+1
• Select appropriate logical Schema for Source Technology and save it

Completed Succesfully!!!

Input Files:

Sample1 

ID,PROJECT_NAME

1,QUOTING&CONTRACT

2,EMPR

Sample2

ID,PROJECT_NAME

3,CONVERSIONS

4,DWH

Sample3

ID,PROJECT_NAME

5,SUPPORT

6,EAM_DM

Output:

Sample