Is there a simpler way to load multiple files?

StianAtWork

Hi,

I have been in several projects loading multiple files/XML by looping a pre populated table with file names. But what bothers me is the need of this extra step to create a flat file, load flat file to table before looping it.

What if there was a way to use the Source Command and Target Command in a procedure to fix this. A couple of ideas that I have not been able to implement and not sure is possible:

1. Using Groovy, Java, Jython or any other supported language in Source Command to feed INSERT INTO <TABLE> in Target Command.
   
   
2. Alternatively if I could use ODI SDK and groovy to log on to the database. Loop the files and insert into table in one script. I have not figured out how to do this without hard coding the database password.

Any suggestions, ideas?

Regards

Stian

SH_INT

You could create a procedure step that iterates over all the files in a specified directory and inserts direct to a table in a scripting language such as jython or groovy. Then the the next step of the procedure would be to consume that table data using ODIs in built iteration capability.

That said I don't know why that would be more preferable to using the approach where a file is created, same number of steps and actually less code to write.

SH_INT

You should be able to query the file direct as a table in the Source Tab of your procedure and then have ODIs built in iteration call the target tab command multiple times ... check out one of John's early blogs demonstrating how to get the syntax for the direct file query required in the source tab

More to life...: ODI Series – Processing all files in a directory

StianAtWork

Thanks for replying but this is basically how I've always done it with a twist.

I can not understand why we always need to generate a file containing the file names to load. There should be a way to get that list in a table directly from an OS command or Groovy for that sakes.

Just a simple example to explain better:

SH_INT

You could create a procedure step that iterates over all the files in a specified directory and inserts direct to a table in a scripting language such as jython or groovy. Then the the next step of the procedure would be to consume that table data using ODIs in built iteration capability.

That said I don't know why that would be more preferable to using the approach where a file is created, same number of steps and actually less code to write.

StianAtWork

You know what. I had completely forgotten or missed out on the getJDBCConnection in substitution APIs.

Since I'm using groovy to list all files anyways I just insert them to the table at the same time.