how to retrieve the source file name dynamically

[Hadi]

Hi,

1. I have an extract file which needs to be loaded into target database table. It's okay. But one of the requirements is that I have to get source file name and pass that name as a column value into the target db table. How can I retrieve the file name dynamically. Because this job will run everyday and file name will be different everyday. For example, file name today is ABC_10042006 but tomorrow will be ABC_11042006.

2. Another question is: the target table is Teradata. But there are two teradata stage option: Teradata Enterprise & Teradata Multiload. I've checked the Teradata section in the PX documentation but could'nt find specific information. Which one should I use as a target and why?

Could you provide any help on these.

[kcbland]

Use any method you know to look at a directory and fetch the file name. Feed the file name as a job parameter and use in the sequential stage as well as a derivation for your particular column.

You need to read your Teradata manuals to learn more about the bulk loader for Teradata (multiload) and when/why/whynot/how to use it.

[Hadi]

Use any method you know to look at a directory and fetch the file name.

What about you kcbland? Do you any method? I wouldn't have asked if I knew any method to achieve this.

[kcbland]

In your job control (Batch, Sequencer, or custom job control) you can use a Unix "ls" command to get a file listing, capture the screen output and parse it for the name. You could also use "find" to search in case you don't know which directory the file will be in. I guess you could also write a shell, perl, or whatever script to do the work to search for and cleanup the found file.

[Hadi]

Kenneth,

In your job control (Batch, Sequencer, or custom job control)

1- "ls" command to get a file listing,
2- capture the screen output
3- parse it for the name.

Is there an example code you can provide? It will be my starting point.

[kcbland]

Here's some logic you can paste into a DS Batch job and see how things work. Ultimately, get it working how you need and then move it into a DS Routine. Use it in a Sequencer job to get the file name and then start your transformation job giving the file name parameter value.

This logic should get a file listing in a directory, then loop thru each line in the screen output and put a message in the job log.

Code: Select all

Call DSExecute("UNIX", "ls -1 /here/there/wherever/myfiles*", ScreenOutput, ReturnCode)

NumberOfScreenLines=DCOUNT(ScreenOutput, @AM)
For LinePtr=1 to NumberOfScreenLines
   Line=ScreenOutput<LinePtr>
   If Line <> "" Then
      FileName=Line
      Call DSLogInfo("Filename [":FileName:"] found", "Msg")
   End
Next LinePtr

[Hadi]

Kenneth,

Thank you very much. I appreciated it. One last question. I tried this code in server routine and it works very well but can I use server routine in a parallel job? If the answer is no then does this have to be implemented in a parallel routine? Any other way to do this? Also you said, "use it in a sequencer job". Again, is it possible to use sequencer job in PX?

Could you tell me bit more please.

[richdhan]

Hi Hadi,

A Job Sequence is a server kind of job and is capable of running both server and parallel jobs as job activities.

Yes you can execute the server routine using the routine activity.

Activities are nothing but stages in the Sequence Job

HTH
--Rich