SAS Deployed Jobs report

[jkotek]

I have been tasked with moveing our current DI Job Schedule into a better one.  The corporate solution is CA-7.  What I need to do is find out for each DI Job, the Source tables and Target tables.  Is there an easy way to report on that without having to look in each job?

Thanks,

Jeff

[LarryHero]

Heres some code that I wrote I while back to query the metadata and bring back information on each DI job and Scheduled Flow.

Make sure have the right permissions to access the metadata first by setting the options below :

options  metarepository= 

         metaport=

         metaserver=

         metaprotocol=

   metauser=

   metapass=  ;

DI Job info:

data x;

  * Set up attributes for all the varibales ;
  attrib uriJob length=$64;
  attrib uriAct length=$64;
  attrib uriTranT length=$64;
  attrib uriStep length=$64;
  attrib uriTran length=$64;
  attrib uriClas length=$64;
  attrib JobId length=$64;
  attrib JobName length=$64;
  attrib SourceTargetObject length=$64;
  attrib SourceTargetType length=$64;
  attrib SourceOrTarget length=$64;
  attrib rc length=3;
  attrib rcJob length=3;
  attrib rcAct length=3;
  attrib rcTranT length=3;
  attrib rcStep length=3;
  attrib rcTran length=3;
  attrib rcClas length=3;

  attrib i length=3;
  attrib j length=3;
  attrib k length=3;
  attrib l length=3;

  * Initialize all the variables, dropping intermediate one;
  uriJob='';
    drop uriJob;
  uriAct='';
    drop uriAct;
  uriTranT='';
    drop uriTranT;
  uriStep='';
    drop uriStep;
  uriTran='';
    drop uriTran;
  uriClas='';
    drop uriClas;

  JobId='';
  JobName='';
  SourceTargetObject='';
  SourceTargetType='';
  SourceOrTarget='';

  rc=0;
    drop rc;
  rcJob=0;
    drop rcJob;
  rcAct=1;
    drop rcAct;
  rcTranT=0;
    drop rcTranT;
  rcStep=1;
    drop rcStep;
  rcTran=0;
    drop rcTran;
  rcClas=1;
    drop rcClas;

  * Job Iterater ;
  i=1;
    drop i;

  * Target Iterater ;
  j=1;
    drop j;

  * Steps Iterater ;
  k=1;
    drop k;

  * Source Iterater ;
  l=1;
    drop l;

  * Grab the URI for the first DI Job ;
  rcJob=metadata_getnobj("omsobj:Job?@id contains '.'",i,uriJob);

  * If DI Job found, enter do loop ;
  do while (rcJob>0);

    * Grab Information on the Job ;
    rc = metadata_getattr(uriJob,"Id",JobId);
    rc = metadata_getattr(uriJob,"Name",JobName);

* Grab URI for the Jobs Activities ;
    rcAct = metadata_getnasn(uriJob,"JobActivities",1,uriAct);

j=1;

    * Grab URI for the Jobs Targets ;
rcTranT = metadata_getnasn(uriAct,"TransformationTargets",j,uriTranT);

* If no Targets found, set variables missing, ...  ;
if rcTranT in (-4,-3) then
do;
   SourceTargetObject='';
   SourceTargetType='';
   SourceOrTarget='';
   output;
end;

*  ...Else, ... ;
else
do while (rcTranT>0);

   * Grab Targets Information ;
   rc = metadata_getattr(uriTranT,"Name",SourceTargetObject);
   SourceTargetType=substr(uriTranT,8,13);
      SourceOrTarget="Target";
   output;

   * Iterate to the next Target ;
      j+1;
   rcTranT = metadata_getnasn(uriAct,"TransformationTargets",j,uriTranT);
end;

k=1;

* Grab URI for the Jobs Steps ;
rcStep = metadata_getnasn(uriAct,"Steps",k,uriStep);

* If no Steps found, set variables missing, ...   ;
if rcStep in (-4,-3) then
do;
   SourceTargetObject='';
   SourceTargetType='';
   SourceOrTarget='';
   output;
end;

*  ...Else, ... ;
else
    do while (rcStep>0);
     
      * Grab URI for the Jobs Transformations ;
   rcTran = metadata_getnasn(uriStep,"Transformations",1,uriTran);

   l=1;

      * Grab URI for the Transformations Classifiers ;
   rcClas = metadata_getnasn(uriTran,"ClassifierSources",l,uriClas);

   * If no Transformations Classifiers found, set values to missing, ...  ;
   if rcClas in (-4,-3) then
   do;
     SourceTargetObject='';
     SourceTargetType='';
     SourceOrTarget='';
     output;
   end;

   *  ...Else, ... ;
   else
   do while (rcClas>0);

     * Grab Transformations Classifiers Information ;
     rc = metadata_getattr(uriClas,"Name",SourceTargetObject);
  SourceTargetType = substr(uriClas,8,13);
        SourceOrTarget="Source";
        output;

        * Iterate to the next Transformations Classifier ;
        l+1;
     rcClas = metadata_getnasn(uriTran,"ClassifierSources",l,uriClas);
 
   end;

   * Iterate to the next Step ;
      k+1;
      rcStep = metadata_getnasn(uriAct,"Steps",k,uriStep);
end;

  * Iterate to the next Job ;
    i+1;
    rcJob=metadata_getnobj("omsobj:Job?@id contains '.'",i,uriJob);

    * Stop command used for testing purpose ;
    if i > 20 then stop;
  end;

  * If no Job was found, print the number of iterations ;
  if (rcjob<0) then
  do;
    put "NOTE: Iteration =" i;
    put "NOTE: No more metadata objects";
  end;

run;

* Remove unwanted records ;

data x2;
  set x;
  if substr(SourceTargetType,1,4)="Work" then delete;
  if SourceTargetType="ExternalTable" then SourceTargetType="ExternalFile";
run;
 
* Sort and dedup ;

proc sort data=x2 out=JobInfo nodupkey;
  by JobName JobId SourceTargetObject SourceTargetType;
run;

This is only the information for each DI Job, if you would the code for the schedules flows to, let me know.

Hope this is of help.

run;