Automation of daily file load into Oracle table.

Virupan · Post by **Virupan** » Thu Jan 22, 2009 7:24 am

Hi All,

I am loading one dat file to oracle table daily.The dat file name is in the format 'filename_yyyymmdd'. I have parameterized the file name and file path.At run time I am passing the current file name and loading the data.Now I have to automate this process of daily file load to oracle table.I have to capture the date at which file is loaded.I created a sequence with 'wait_for _file activity'stage and a 'job activity'stage with all the parameters passed.
Can any body please let me know how can I completely automate this process without passing the file name at run time.

Many thanks in advance

chulett · Post by **chulett** » Thu Jan 22, 2009 8:43 am

You won't be able to use the WFF stage with wildcards, only a static filename. You may need to build a loop to check, possibly in conjuntion with the Folder stage which does support wildcards or a script you leverage from the Sequence.

kandyshandy · Post by **kandyshandy** » Thu Jan 22, 2009 8:52 am

Convert sysdate (@DATE) to yyyymmdd format in a user variable stage and then pass the same to your job as the 'file name' parameter

Code: Select all

e.g filename:'_':UserVariable:'.dat'

If you are not sure that you would load filename_20081010.dat on 20081010 then you may want to maintain the date in a table and keep on incrementing by 1 after successful load of the file. Whenever the load starts on any day, you need to get the date from this table and pass to the job which reads the dat file.

chulett · Post by **chulett** » Thu Jan 22, 2009 9:15 am

I prefer simply looking for whatever files are there on any given day and moving / archiving them elsewhere when complete. That way you don't have to worry about your date getting out of sync and you can process multiple files on any given day if need be. You may only need to track what days you've processed if that is one of your requirements or the possibility exists of receiving the same file multiple times where that would be a Bad Thing.

We do this on a regular basis, typically one file delivered daily but we only process them on the weekdays, so need to pick up three files on Monday.

ray.wurlod · Post by **ray.wurlod** » Thu Jan 22, 2009 3:01 pm

One of the recent webinars ("Files and DataStage Job Sequences") at the DSXchange Learning Center shows precisely how to do this with a loop in a job sequence.