Read table data and fetch file from path specified in table column

663468

Hi,

I have a situation where file path information is stored in database table along with other columns - I need to read data from SQL Server table row by row, go to the path identified by the record, check for existance of the file - if the file is available copy it to a staging area, then read the next record. Repeat this process until the last record is fetched from the table. At the end, zip all the files found and FTP to a different location. If the file is not found in the path specified, write the database record to a seperate text file.

At end of the process, send an email with records for which files were not found at specified location.

Thanks

Edited by: user4566019 on Oct 5, 2008 4:32 AM

Cezar Santos

Hi,

That is not complex to solve at ODI.

Try the following.

1) Create a package

2) create a variable to receive the path as parameter and drag and drop it at package as "Declared"

3) drag and drop the ODI Tool "odiFileMove" using and let the variable as patch at ODI Tool parameter.

Use the parameters -ACTION=MOVE -TIMEOUT=100 and -NOFILE_ERROR=NO (take a look at ODI Toos Reference for other parameters)


3) Create a "KO" (error) flow to write the variable at a text file. That will allow the process knows what file is missing

4) generate a scenario of this package

5) create a procedure

6) at first step, put the query from SQL Server (set the Logical Schema to the right connection)

7) at Target tab, put the call to the scenario generated using the returned value from query parameter. Plus, let the -SYNC_MODE=1. That will call one scenario by time.

8) create a new step to zip the files at stage directory

9) create a step to make the ftp

10) create a step to mail the txt generated with files not founded. If you wish, you can make a validation on the file to see if is necessary to send the mail, it means, if there is any file missing.


Does it help you?