How to set up a trick called ‘put a file in a folder and it’ll dissappear in DWH’ with Oracle Data Integrator.

Imagine that we need to upload a text file in DWH whenever it appears in some folder with no defined schedule.

It really easy to do in ODI:

1. Create a package with ODIStep ‘WaitForFile’ which will scan the directory on predefined basis and will move found file in a folder where it’ll be processed. I usually archive and timestamp file on the way — this saves a lot of time in “where did this number come from” arguments.
2. Insert a scenario that will process the file
3. Steps 1-2 work only once, so we need to cycle things up, so that after processing a file, system starts waiting for a new one.  This is as easy as making a scenario of a package we’re creating and inserting as step 3 )

So we’ve got a scenario which grabs files from a folder and processes it. Just run it and it’ll wait in background and process all the files you throw at it. But what happens if we reboot ODI agent server? To ensure that this task starts automatically — just add a schedule for this scenario with ‘Start on Startup’ option.

Bingo: disaster-prone file vacuum cleaner )