CODEWITHSUNDEEP
oracle-databasefileoracle12coracle-data-integratorfile-import
number005
number005

Reputation: 27

How to call a Mapping for each incoming file in Oracle Data Integrator (ODI)

I am working with Oracle Data Integrator 12c and I have a number of txt files, which somebody copies into a folder "/import". ODI should notice this and call a Mapping for each file. Then each file should be saved in another folder "/export"

My mapping is working perfectly for one file. I specified this file in the model part. But how can I dynamically load each file and let the mapping also work with each file and then save it?

I hope it is clear what I want :)

Thanks in advance

Upvotes: 0

Views: 3713

Answers (2)

Rachit Gupta
Rachit Gupta

Reputation: 1

As per your requirement, you would need to poll into the import folder where somebody places the file. You can choose the polling time as required in seconds.

You will need to create a package with the below flow:

  1. ODI Utility ODIFileWait, will help you to wait till file with or without a particular pattern arrives in the given folder. Once ODI finds or notices the file, it will move to next step.

  2. Using the OSCommand ls>>filenames.txt, get all the file names in a separate file. Now create a DB table, where the file names with there load status can be populated with a unique sequence number against each record. Read this filenames.txt and populate the DB table with the load status against each file name.

  3. Create a variable which counts and stores total number of files based on the load status (either pending for processing or not) from the DB table and check if it is greater than zero before proceeding.

  4. In another variable, take the minimum of the sequence number from the same DB table and on basis of the sequence number retrieve the file name from the same table.

  5. Now process your mapping or interface but make sure While creating a source datastore (file), provide a variable name(which was used for retrieval of file name in previous step) in resource name field.

  6. Go to step 3 until count of unprocessed file is 0.

Upvotes: 0

Canburak Tümer
Canburak Tümer

Reputation: 1063

I have completed a similar process to your need. It was not an exact need but steps may help you. My solution was a bit tricky, since a third party send files into a directory via FTP and there is no limitation for number of files or file name standard.

Since the job was in a former client I am not able to provide you whole step codes and screenshots but I may tell the outline. You will need an ODI package

  1. In OSCommand component I gave ls >> filelist.txt
  2. I had an interface reading file filelist.txt into a table with a sequence number for each row.
  3. I had an ODI Variable #MAX_ROW_NUM which reads the maximum file number from table above.
  4. Evaluate variable for #MAX_ROW_NUM > 0
  5. Get file name into #FILE_NAME variable from table where ROW_NUM = #MAX_ROW_NUM
  6. Using #FILE_NAME execute the interface (source datastore must be dynamic in model)
  7. Using OSCommand component move file with #FILE_NAME to /completed directory.
  8. Using and ODI Procedure delete row with #MAX_ROW_NUMBER from filelist table
  9. Increment value of #MAX_ROW_NUMBER by -1
  10. GO TO 4

Optional step, you can KO from step 4 to another OSCommand which deletes the filelist.txt

I hope, I could explain my process clearly, in case of further help do not hesitate to ask.

Upvotes: 1

Related Questions