You can create a SAP Data Services script to schedule batch jobs conditionally. The script is enclosed in a separate job with no sources, transformations or targets. When a job finishes successfully a following job gets triggered. If job fails, next jobs does not get triggered. All jobs are listed in a data base table in their execution order and get triggered one by one in a succession until they are all done or a job fails with an error.

Prerequisites:

  • – Create a DB table (List_of_jobs) with two columns and list the batch jobs you want to execute:
    • Job_ID ( primary key; starting from 1; incrementing by 1)
    • Job_name ( must be the same as in Designer or Management console)

Import the table into the repository.

  • – Export all jobs’ execution commands (.bat files) from DS Management console à Administrator ( in this example ‘C:\ProgramData\SAP BusinessObjects\Data Services\log)
  • – Import ALVW_HISTORY table into Repository
  • – Create variables:
    • $Count_of_jobs
    • $Job_number
    • $Job_ID
    • $Job_name
    • $End_time
    • $Status
  • – Create script:

# Initiate first job

#

$Count_of_jobs= total_rows(data store name.dbo.List_of_jobs);

print(‘Count of listed jobs is’ || ‘ ‘|| ‘[$Count_of_jobs]’);

$Job_number=1;

$Job_ID= sql(‘ data store name ‘,’select Job_ID from List_of_jobs where Job_ID=[$Job_number]’);

$Job_name= sql(‘ data store name ‘,’select Job_name from List_of_jobs where Job_ID=[$Job_ID]’);

print(‘Job’||’ ‘||'[$Job_name]’ || ‘ ‘|| ‘will start executing’);

exec(‘C:\ProgramData\SAP BusinessObjects\Data Services\log\[$Job_name].bat’,’ ‘, 8);

# Check status

#

$End_time=sql(‘ data store name ‘,’select max(END_TIME) from ALVW_HISTORY where SERVICE= \'[$Job_name]\”);

$Status= sql(‘ data store name ‘, ‘select STATUS from ALVW_HISTORY where SERVICE= \'[$Job_name]\’ and END_TIME= {$End_time}’);

print(‘Status is’ ||’ ‘|| ‘{$Status}’);

# Check status of job and if successful trigger next job.

# End script if job with error or there are no more listed jobs

while ( ($Status= ‘D’) and ($Job_number< $Count_of_jobs))

begin

print( ‘Job’||’ ‘||'[$Job_name]’||’ ‘||’executed successfully’);

$Job_number= ($Job_number+ 1);

print(‘Next job number is’ ||’ ‘|| ‘{$Job_number}’);        

$Job_ID= sql(‘ data store name ‘,’select Job_ID from List_of_jobs where Job_ID=[$Job_number]’);

$Job_name= sql(‘ data store name ‘,’select Job_name from List_of_jobs where Job_ID=[$Job_ID]’);

print(‘Job’||’ ‘||'[$Job_name]’ || ‘ ‘|| ‘will start executing’);

exec(‘C:\ProgramData\SAP BusinessObjects\Data Services\log\[$Job_name].bat’,’ ‘, 8);

$End_time=sql(‘ data store name ‘,’select max(END_TIME) from ALVW_HISTORY where SERVICE= \'[$Job_name]\”);

$Status= sql(‘ data store name ‘, ‘select STATUS from ALVW_HISTORY where SERVICE= \'[$Job_name]\’ and END_TIME= {$End_time}’);        

print(‘Status of current job is’ ||’ ‘|| ‘{$Status} ‘);

end

IF($Status= ‘E’)

begin

print( ‘Job’||’ ‘||'[$Job_name]’||’ ‘||’was unsuccessful’);            

end 

else if( $Status not in (‘E’, ‘D’))

begin

print(‘Jobs were interrupted by unknown error, please refer to logs’);

end

else

print( ‘There are no more listed jobs’);

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Akash Banerjee

    Hello Goran,

    This is indeed a very useful document although I have only one question for the below code.

    # Check status

    #

    $End_time=sql(‘ data store name ‘,’select max(END_TIME) from ALVW_HISTORY where SERVICE= \'[$Job_name]\”);

    how do we ensure that this step does not get executed till the job triggered has finished running. As per my understanding there should be some loop in between which should check the status of the jobs and once the status changes to complete it can come out of the loop to execute this system.

    Let me know your thoughts.

    (0) 
    1. goran deliyski Post author

      Hello Akash,

      I understand what you mean.

      The flow is as follows:

      first the ‘exec’ function is executed and as soon it finishes the script proceeds to the next line.

      I have tried it and it works properly all the time. Please try it yourself and see if it behaves as expected

      (0) 
  2. Akash Banerjee

    Hey Goran,

    You are correct the control of the execution waits on the line where exec function is written. The possible explanation is that the exec functions waits for a carriage return which it gets only when the batch file it calls gets completed and returns a value.

    But it was a good learning experience.

    Thanks

    (0) 

Leave a Reply