Logging job status to an ODBC table

[Scoindy]

Hi,

I'm new to SAS DI and I'm trying to build a simple ETL framework. I would like be able write audit information whenever a job is executed to an ODBC table. for example, the start and end times, the job name, and the status of the job on completion e.g. SUCCESS or FAILURE. I have done this in other ETL tools by creating a stored procedure at database level and calling them from the ETL tool.

With DI this works perfectly to create the job audit record, but the problem I have is that if a transformation fails, then the NOEXEC option is set which is preventing the proc sql command to update the audit record with end timestamp and job status from running. Hopefully the attached screenshot explains what I'm trying to do.

The logfile for the log job end transformation shows my ODBC stored procedure call not being executed because the error in the SCD 2 transformation has set the NOEXEC option

6125       proc sql;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
6126       
6127          connect to odbc (datasrc='fluxliner_repo');
NOTE: Statement not executed due to NOEXEC option.
6130       
6131          execute(dbo.sp_package_end
6132                    @batch_id     = "&batch_id",
6133                    @package_name = "&etls_JobName",
6134                    @status       = "&JOB_RC") by odbc;
NOTE: Statement not executed due to NOEXEC option.
6135       
6136          disconnect from odbc;
NOTE: Statement not executed due to NOEXEC option.
6137       
6138       quit;
NOTE: The SAS System stopped processing this step because of errors.

I've tried things like adding the EXEC option to the proc sql call, and also RESET NOEXEC, but they don't seem to make any difference. I've also tried adding an erorr status handler to the SCD2 transformation which has an action of None in the hope that this would allow to SQL exec transformation to run but that doesn't help either.I'm not sure if I'm going about using the status handler in the correct way though.

I'm using SAS DI 4.9.04.

Thanks

S.

[Kurt_Bremser]

"the start and end times, the job name, and the status of the job on completion"

All this is recorded by the scheduler. Is there a reason for not using the scheduler's data?

[Patrick · Accepted solution]

@Scoindy 

If you inspect DIS generated code for status handling in case of error you'll see that besides of setting NOSYNTAXCHECK you also need to set OBS=MAX.

Based on DIS generated code here how I'd implement if using a user written transformation:

%macro demo();
  %if (&job_rc ge 5) %then
    %do;
      %local etls_syntaxcheck;
      %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck));

      /* Turn off syntaxcheck option to perform following steps  */
      options nosyntaxcheck;
      %local etls_obs;
      %let etls_obs = %sysfunc(getoption(obs));

      /* Set obs option to max to perform following steps  */
      options obs = max;
    %end;

/** here your custom logic to update the table **/

  %if (&job_rc ge 5) %then
    %do;
      /* Reset obs option to previous setting  */
      options obs = &etls_obs;

      /* Reset syntaxcheck option to previous setting  */
      options &etls_syntaxcheck;
    %end;
%mend demo;

Even though implementing the process to write status information within a job itself is quite common @Kurt_Bremser's question is very valid especially if you don't need any information the scheduler doesn't have (like pre- and post rowcounts).  

[Scoindy]

Thanks both. I will look into what the scheduler can offer, but in the meantime Patrick's answer has allowed me to do what I wanted to.