Sequence Stage problem

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 1:56 am

All,

I have the below requirement. I have already designed a job to load the header & footer information in a log file. The job will be used as a template for all the process. The job will take the filename as the input and split the header & footer and then insert them into an etl_log table.

We may receive 4 or 5 files every month. We have to process the header and footer information before the actual loading process. Basically i have to run the job (which i designed to load the header & footer) 4 or 5 times with different file name.

So i have designed a job as below.

Code: Select all


ExecuteCommand   --->   Start Loop  ---> Job Activity ---> EndLoop
                                           ^                                             V
                                           |                                               |
                                            ---------------------------------------

Execute Command:

Command = ls sec*.txt
(Note the file name starts with "sec")

Parameter = #InputFileDirectory#

PROCESS_HDR_FTR..JobControl (@Execute_Command_0): Executed: ls sec*.txt /bis_data/input/
Reply=2
Output from command ====>
ls: sec*.txt: No such file or directory
/bis_data/input/:
ai.backup
deleteit.csv
perl-date-calc.txt.pl
perlout.log
sample.txt
sec_fund_data_with_names.txt
sec_fund_data_with_names_short_names.txt
sec_fund_returns.txt
sec_fund_returns_WITH_EXP.txt
sec_index_returns_names.txt

Start Loop:

Loop Type: List Loop
Loop Definition: #Execute_Command_0.$CommandOutput#
Delimeter : Comma

I just captured the below information from the director log.

PROCESS_HDR_FTR..JobControl (@Job_Activity_2): Controller problem: Error calling DSSetParam(FileName), code=-4
[ParamValue/Limitvalue is not appropriate]

PROCESS_HDR_FTR..JobControl (@Coordinator): Summary of sequence run
02:52:01: Sequence started
02:52:01: Execute_Command_0 (COMMAND ls sec*.txt) started
02:52:01: Execute_Command_0 finished, reply=2
02:52:01: StartLoop_Activity_1 loop iteration 1 started
02:52:01: Job_Activity_2 (JOB AI_00_GET_HDR_FTR) started
02:52:01: Exception raised: @Job_Activity_2, Error calling DSSetParam(FileName), code=-4 [ParamValue/Limitvalue is not appropriate]
02:52:01: Sequence failed

Can you help me to resolve this issue?

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 2:08 am

I have also tried ls -m and ls- f.

ls -m

PROCESS_HDR_FTR..JobControl (@Execute_Command_0): Executed: ls -m sec* /bis_data/input/
Reply=2
Output from command ====>
ls: sec*: No such file or directory
/bis_data/input/:
ai.backup, deleteit.csv, perl-date-calc.txt.pl, perlout.log, sample.txt,
sec_fund_data_with_names.txt, sec_fund_data_with_names_short_names.txt,
sec_fund_returns.txt, sec_fund_returns_WITH_EXP.txt,
sec_index_returns_names.txt

ls -f

PROCESS_HDR_FTR..JobControl (@Execute_Command_0): Executed: ls -f sec* /bis_data/input/
Reply=2
Output from command ====>
ls: sec*: No such file or directory
/bis_data/input/:
.
..
ai.backup
sample.txt
sec_fund_returns.txt
sec_index_returns_names.txt
deleteit.csv
perl-date-calc.txt.pl
sec_fund_data_with_names.txt
perlout.log
sec_fund_returns_WITH_EXP.txt
sec_fund_data_with_names_short_names.txt

Actualy this is not coming under "warning" or "fatal". it is coming under "info" only. Its colour is also "Green".

is it causing the issue?

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 3:32 am

when i run the below command in unix box, it is working fine.

Code: Select all

$ ls -m sec*
sec_fund_data_with_names_short_names.txt, sec_fund_data_with_names.txt, sec_fund_returns.txt, sec_fund_returns_WITH_EXP.txt, sec_index_returns_names.txt

$ ls -m sec*.txt
sec_fund_data_with_names_short_names.txt, sec_fund_data_with_names.txt, sec_fund_returns.txt, sec_fund_returns_WITH_EXP.txt, sec_index_returns_names.txt

suse_dk · Post by **suse_dk** » Mon Nov 14, 2011 4:04 am

Opposite the ls you do on the Unix box, then you are actually defining two parameters to perform the list on in the XC stage in DS.

ls sec* (on the current directory)
and
ls /bis_data/input/

You need to have you command from DS to look like this:

ls /bis_data/input/sec*

In the command box write: ls
In the parameter box write: /bis_data/input/sec*

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 4:29 am

yes. That worked for me.

I gave like

ls -m

#InputFileDirectory#sec*

PROCESS_HDR_FTR..JobControl (@StartLoop_Activity_1): Loop through list (5 items): iteration 1

But...I am still getting the below error message.

PROCESS_HDR_FTR..JobControl (@Job_Activity_2): Controller problem: Error calling DSSetParam(FileName), code=-4
[ParamValue/Limitvalue is not appropriate]

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 4:47 am

I changed the code as below

Code: Select all

convert(@FM,'', Execute_Command_0.$CommandOutput)

The above said issue disappeared. But i am facing some different issue.

input_file,0: Unable to open /bis_data/input//bis_data/input/sec_fund_data_with_names.txt,/bis_data/input/sec_fund_data_with_names_short_names.txt,/bis_data/input/sec_fund_returns.txt,/bis_data/input/sec_fund_returns_WITH_EXP.txt,/bis_data/input/sec_index_returns_names.txt: No such file or directory.

I can see an empty /bis_data/input/ parameter and then the file name.
i.e /bis_data/input/sec_fund_data_with_names.txt

The sequencer is generating the above output and pass it to the original job.

PROCESS_HDR_FTR..JobControl (@Coordinator): Summary of sequence run
05:42:38: Sequence started
05:42:38: Execute_Command_0 (COMMAND ls -m) started
05:42:38: Execute_Command_0 finished, reply=0
05:42:38: StartLoop_Activity_1 loop iteration 1 started
05:42:38: Job_Activity_2 (JOB AI_00_GET_HDR_FTR) started
05:42:45: Job_Activity_2 (JOB AI_00_GET_HDR_FTR) finished, status=3 [Aborted]
05:42:45: StartLoop_Activity_1 loop iteration 2 started
05:42:45: Job_Activity_2 (JOB AI_00_GET_HDR_FTR) started
05:42:45: Exception raised: @Job_Activity_2, Error calling DSRunJob(AI_00_GET_HDR_FTR), code=-2 [Job is not in the right state (compiled and not running)]
05:42:45: Sequence failed

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 6:06 am

I understood that i am not passing the file name one by name. All the names are going in a single shot.

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 7:11 am

any inputs will be highly appreciated

chulett · Post by **chulett** » Mon Nov 14, 2011 7:53 am

Patience.

Moved to the proper forum.

MarkB · Post by **MarkB** » Mon Nov 14, 2011 9:45 am

Rather than using a Numeric Loop type, couldn't you do a List loop with the command output with the delimiter selected as comma?

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 9:56 am

could you pls explain it more?

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 9:57 am

list loop...ok...
start value = ?
end value = ?

karthi_gana · Post by **karthi_gana** » Mon Nov 14, 2011 9:57 am

chulett wrote:Patience.

Moved to the proper forum.

sure

sathya_anjan · Post by **sathya_anjan** » Mon Nov 14, 2011 10:49 am

Can you please try the below approach

Job Sequence Design:

Exceute Commad Stage --> Start Loop --> Job Activity --> End Loop

Execute Command Stage:

Command Box: ls -m
Parameter Box : #InputFileDirectory#sec*

Start Loop Activity

List Loop with Comma as Delimeter

Derivation: #Execute_Command_0.$CommandOutput#

Job Activity

In the File Name box enter a parameter value say #FILE_NAME#

Assign the value for this #FILE_NAME# in the Job Activity stage using the below derivation

Trim(Convert(@FM,"", StartLoop_Activity_1.$Counter))

This should work for you.

Thanks

Sathya

karthi_gana · Post by **karthi_gana** » Tue Nov 15, 2011 1:30 am

Trim(Convert(@FM,"", StartLoop_Activity_1.$Counter))

It worked for me.

I used

(Convert(@FM,"", Execute_Command.$CommandOutput)

I am still unable to identify the difference between the above two commands.

DSXchange

Sequence Stage problem

Sequence Stage problem

Re: Sequence Stage problem

Re: Sequence Stage problem