Passing parameters dynamically to scheduled job

[girishoak]

Hi,

I have a job which loads data from the sequential file to Oracle database. I know how to load the data from the seq. file to Oracle. But the problem I am facing is that the name of data file contains date. the name of data file appears as somefilename_dd_mon_yyyy.csv. How can be this handled. I want to run this job on daily basis.

Please help. Thanks in advance.
Girish Oak

[kduke]

Girish Oak

You will need to write a batch job which figures out the filename and sends it to the job as a parameter. You need to openpath to the directory where these files are. You can then select the directory and get a list of all the files in this directory.

MyDir = '/usr/csvfiles'
openpath MyDir to MyDirPtr else stop
SSELECT MyFilePtr
loop while readnext FileName
... start job here and pass FileName as parameter ...
repeat

Kim.

[kcbland]

I personally would not parameterize a file name. I would prefer to fix the file name in the job and use a symbolic link to point to the appropriate file. Your question points back to a holistic issue with induction processing architecture.

Rather than go off on a long discussion of induction processing, persistent staging databases, etc, I'll just say a quick and dirty approach is to simply create a symbolic link in the work directory where your job is expecting the file. The link can point to the current file for processing. On the next day, recreate the link to point to the next appropriate file.

[kduke]

Kenneth

I disagree. To go outside DataStage and create a link to each file that needs to be processed is not as clean as parameterizing the filename. Using a parameter messes up metadata. The same is true for DSNs. All of agree that DSNs almost have to be parameters. When you have a alot of files to process then the same is true for filenames. On one project we had one file per day for 60 days to process. We had 12 mainframes. That is a lot links to setup. Also you can use multiple instances and process more than one file at a time. Lots of advantages to having a filename as a parameter.

Kim.

[kcbland]

I agree in the case of using a instantiated job to handle an indeterminate or dynamic number of source files, if that is your approach. For instance, if you have 5000 indentical files being FTP'd to your server each day, you could run 5000 instances to handle each file independently. This leads to congestion about the target table, because of the number of jobs all trying to get their fingers in the pie.

I didn't want to go into a holistic discussion, but for a situation like this I would have used a persistent staging database. There's a whole induction layer in your warehouse (look at Inmon's GIF, go to TDWI and read Karolyn Duncan, of course read Kimball) a lot of companies skip building.

If you're parameterizing the whole file name, not just a portion for instantiation reasons, then you're going to have to deal with impact analysis difficulties if you use a common name across a lot of jobs for disparate files. For example, if you said: show me all jobs that read/write this file, you get a ton of hits whereas an explicit name will not give false positives.

Tomato, tomahhto.

[spracht]

Assuming, the file to be processed has today's date in its name, you could have a controlling job that evokes the job processing the file. Derive the filename something like that:

Code: Select all

filename="somefilename":oconv(date(), "D_DMY[2,A3,2]"):".csv" 

After that you can pass the filename as a parameter to your job using DSSetParam.