Returning status of multple jobs within a 'master' job in Data Integration Studio

[mws1985]

Hello all,

I have a question on how best to approach returning the status of multiple jobs, contained within a larger job in SAS DI Studio (4.904).

I have multiple, separate jobs, to be run from a 'controller', or master, job that is scheduled on our enterprise SAS (scheduling on SAS is outside of my control). For example, the following four jobs are run from my master job.

UpdateDepartments

UpdatePersonnel

UpdatePayroll

UpdateOfficeSpaces

While the jobs are run together, each of the jobs is independent -- if UpdateDepartments fails, it does not necessarily mean that UpdatePersonnel or UpdatePayroll will fail.

Upon completion of the master job, I send out an e-mail with information on the jobs that successfully ran. Currently, as part of the e-mail, I am returning (or attempting to return) the status of each job. Right now, if a job fails, the email returns ALL the jobs that were run successfully up until the first failure --  so, for example, if UpdateDepartment and UpdatePersonnel run successfully, and UpdatePayroll fails, the e-mail will return an output table with UpdateDepartment and UpdatePersonnel, but not UpdateOfficeSpaces, even if it successfully ran (because it occurred after the first failure, UpdatePayroll).

I am generating this information based on the job_rc variable. After each sub-job (UpdateDepartment, UpdatePersonnel, etc.) runs, in their post-code, I run a macro (CheckSASJobStatus) that inserts the job's name into a SAS work table (and my e-mail prints out that work table). My understanding is that, after the first failure, job_rc will always be a '5', since it defaults to the highest, which is why I am only getting jobs listed up until the first failure (because my conditional only applies when the job_rc code is equal to 0, the jobs after the first failure never get inserted into the table). 

Is there a paradigm for returning this -- that is, get the return code for individual jobs within the overall job?

Below is the macro I am using (note that I pass a parameter, var_version to it, and append it to the job name, as a way of keeping track of which version of the job I am working on, as we currently don't have git enabled...so you may end up with something like UpdateOfficeSpaces_1.3).

%MACRO CheckSASJobStatus(var_version);
%if (&job_rc eq 0) %then 
%do;
%let job_c=&etls_jobname._;
%let job_tag=&job_c&var_version;
proc sql;
insert into work.completed_sas_steps(sas_job, sas_job_completed) 
values("&job_tag", %sysfunc(datetime()));
quit;
%end;
%MEND CheckSASJobStatus;

[ErikLund_Jensen]

Hi @mws1985 

The problem is that a DI Studio Job job in another DI Studio Job is not treated as an individual job. The contained transformations are included as if they were written in the master job, and the DI Studio "job envelope" where the jobrc macro is defined, and the variable job_rc is initiated to zero, appears only once.

So I think that you will get the desired result if you include a precode in each job where the job_rc macro variable is initiated to zero by calling %rcSet(0). 

The downside is that the master job only fails if the last sub-job fails. If you want the master job to fail in case of any error, it becomes a little more complicated. Besides resetting the job_rc macro variable as mentioned, add a new master_rc macro variable:

1. Include master job precode containing a %rcMasterSet macro definition similar to the %rcset macro definition.
2. Include a call to update master_rc from job_rc in each job's postcodein addition to your existing code.
3. Include master job postcode to update the macro variable syscc from master_rc to get the job to fail if any subjobs are failed.

You cannot do the opposite: leave the job_rc "as is" and just create your own sub_rc to keep track of each sub-job, because job_rc is updated for every individual transformation, and I see no way to create a similar functionality. I am sorry to say that this is not tested, but I think it would work. Pl3ease write if you have any questions or it doesn't work as intended.