Loading multiple files with ODI

If your source is file and you need to load multiple files into your database you need to do more than something. If you want to load your files using ODI interfaces you need to make some customization within ODI. There are some utilities or commands to load files into databases such as Sybase uses BCP, Oracle uses SQL Loader, MS SQL Server uses Bulk Insert. Some of them support multiple files, some of them support transformations. But there is a bit problem if name of the file is generated by source system, that means you don’t know the names of the files.

I want to explain a methodology loading multiple files into Oracle using external tables, which I think the best and easiest way.

First the environmental stuff and some prerequisites;

1. A Knowledge Module to load files using file name as a ODI Variable.
2. Some operating system folders for loading, rejected and log files.
3. A table named ETL_FILE_LOG to hold the file name in ODI Variable and to log in database.
4. 4 ODI Procedure getting file list, work with processed files, work with rejected files and rename files.
5. An ODI Interface to make transformations and loading.
6. 2 ODI Variables to manage file_name and file_id.
7. An ODI Package to manage everything.

Let’s start to do some work.