SAP Data Services script to schedule batch jobs conditionally, trigger jobs after successful execution

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’);