Looping Function

shaminmin26 · Post by **shaminmin26** » Wed Mar 11, 2009 1:14 am

I got a problem to move my files from process directory to archive directory.

File = ABC.txt

abc.txt|abc2.txt|abc3.txt

Sequence Job :

Execute Command --> Start Loop Activity (Loop from file, Delimiter = "|") --> Load the data from the file to database --> Execute Command (To move process file to archive folder) --> End Loop Activity.

My sequence job manage to move abc.txt and abc2.txt to archive folder but it did not manage to move abc3.txt to archive due to execute command.

Inside execute command : mv, parameters --> parameters set/#StartLoop.$Counter# parameters set/#StartLoop.$Counter#

Inside the logs, the error i got is as below :
mv process/abc.txt archive/abc.txt
mv process/abc2.txt archive/abc2.txt
mv process/abc3.txt --> did not pass in the archive directory and filename. Any ideas is this a bug or is due to my job design?

Thanks in Advance. :D

ray.wurlod · Post by **ray.wurlod** » Wed Mar 11, 2009 2:25 am

Welcome aboard. Please post the ACTUAL error message.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Mar 11, 2009 3:26 am

Common mistake is using DCount. Use Count instead (if this is the issue).

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Mar 11, 2009 3:27 am

Common mistake is using DCount. Use Count instead (if this is the issue).

shaminmin26 · Post by **shaminmin26** » Thu Mar 12, 2009 12:06 am

abc..JobControl (@Execute_Command_23): Executed: cat C:/temp/testing.txt
Reply=0
Output from command ====>
abc.txt|abc2.txt|abc3.txt

abc..JobControl (@StartLoop): Loop through list (3 items): iteration 1

abc..JobControl (@Move): Executed: mv C:/temp/abc.txt C:/temp/archive/abc.txt
Reply=0
Output from command ====>

abc..JobControl (@StartLoop): Loop through list (3 items): iteration 2

abc..JobControl (@Move): Executed: mv C:/temp/abc2.txt C:/temp/archive/abc2.txt
Reply=0
Output from command ====>

abc..JobControl (@StartLoop): Loop through list (3 items): iteration 3

abc..JobControl (@Move): Executed: mv C:/temp/abc3.txt
Reply= C:/temp/archive/abc3.txt
Output from command ====>

abc..JobControl (@EndLoop): Loop finished

abc..JobControl (@Coordinator): Summary of sequence run
00:55:31: Sequence started
00:55:31: Execute_Command_23 (COMMAND cat) started
00:55:31: Execute_Command_23 finished, reply=0
00:55:31: StartLoop loop iteration 1 started
00:55:31: Move (COMMAND mv) started
00:55:31: Move finished, reply=0
00:55:31: StartLoop loop iteration 2 started
00:55:31: Move (COMMAND mv) started
00:55:31: Move finished, reply=0
00:55:31: StartLoop loop iteration 3 started
00:55:31: Move (COMMAND mv) started
00:55:31: Move finished, reply=-1
00:55:31: EndLoop loop finished
00:55:31: Sequence finished OK

shaminmin26 · Post by **shaminmin26** » Thu Mar 12, 2009 12:13 am

The bug is at the sequential file.

For Example :

abc.txt|abc2.txt|abc3.txt
Newline (but when i view the data in datastage, it will only be view as one row)

When creating a new sequential file in datastage, it will always give me a newline at the end of the whole file, even though i remove all the delimiters, why?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 12, 2009 12:48 am

Load it into an upstream user variable and trim the line terminator from it there.

shaminmin26 · Post by **shaminmin26** » Mon Mar 16, 2009 3:38 am

how can i remove the line terminator? I am passing the values inside the sequential file to do the loop.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Mar 16, 2009 3:47 am

Try

yourField[1:length(yourField) - 1]

laknar · Post by **laknar** » Mon Mar 16, 2009 6:18 am

try this

convert(@FM,'|',Field Name)

shaminmin26 · Post by **shaminmin26** » Mon Mar 16, 2009 9:16 pm

I had this inside the sequence. But inside execute command, i can't indicate this.

my flow is

execute command --> start loop --> execute command --> end loop

is there anyway tat i can amend inside the execute command so that i can make the job function well.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 16, 2009 10:20 pm

Execute Command -> User Variables -> Start Loop -> whatever -> End Loop

Trim the final field mark from the command's output and convert the other field marks to a printable delimiter in the user variable, and use a reference to the user variable in your Start Loop activity as the "list of things" through which to iterate.

It works, I've done it lots of times.

shaminmin26 · Post by **shaminmin26** » Tue Mar 17, 2009 12:46 am

thanx thanx, it works prefect well.

But i got another question. I had tested my jobs both in windows and unix. Unix works fine. But when i am trying to compile the jobs in windows. It doesn't work. Does it means that window will have another method to solve this problem?

The error that i get when i am trying the same job in windows environment is listed as below :

Activity EndLoop : Referenced StartLoop cannot be found on an input link.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 17, 2009 3:17 am

No, it means you have renamed your StartLoop activity and not re-linked the EndLoop activity to it.

shaminmin26 · Post by **shaminmin26** » Tue Mar 17, 2009 4:11 am

thanx a lot..