BookmarkSubscribeRSS Feed
Shantaram
Calcite | Level 5

Want to track start and end time of DI job and send mail to concern person automatically.

 

9 REPLIES 9
LinusH
Tourmaline | Level 20

What version are you using?

If you are on 9.4, logging jobs can be integrated in Environment Manager. Suggest that you talk a look at that before building something on your own.

 

Email can be sent in number of ways.

There is the possibility to add this as an action in the last transformation for a job as a Status handling event. This requires that you enter the mail address directly in the transformation, which is not ideal, especially when the address i s personal (opposed to technical/group mail box).

In status handling you could also call a macro, that does the email sending for you. Such a macro can be sent from a post process (transformation or job) as well. The benefit of this method is that you don't "hard code" mail accesses in individual jobs. Imagine that you need to change email addresses in hundreds of jobs... 😞 

Data never sleeps
Shantaram
Calcite | Level 5

Data integration studio version is 4.9.

In status handling we can enter mail id but when we execute then we get only job complition time not a start time.

LinusH
Tourmaline | Level 20

DI Studio creates a lot of macro variables for you, one is ETLS_STARTDATE (or similar, not at a DI site right now). Try to use that in the mail. Should be accessible from a macro definitely.

Data never sleeps
Shantaram
Calcite | Level 5

data _null_;
OPTIONS EMAILHOST =("Host Name");
file outbox ;
TO=("mail_id");

subject=("My SAS Output");
/* attach=("/sasdata/SAMPLE_REPORT.pdf" "/sasdata/SAMPLE.pdf") */
put "&etls_stepStartTime";
put "&etls_endTime";
put 'Folks,';
put 'Attached is my output from the SAS';
put 'program I ran.';

put 'It worked great!';
run;

 

this is working properly in sas enter prise guide. When i am trying in DI it not working. 

i am trying in post code of job and user written transformation but not working.

LinusH
Tourmaline | Level 20

What do you mean by not working?

Do you get an error message, or will there not be an email sent?

The log would help.

Data never sleeps
Shantaram
Calcite | Level 5

log of the job

 

 

 

 

13 The SAS System 19:03 Thursday, December 3, 2015

354 %let etls_resetRestart = 1;
355
14 The SAS System 19:03 Thursday, December 3, 2015

356 /****************************************************************************
357 * Job: mail_test A5U5SY8D.C1000BO4 *
358 * Description: *
359 * *
360 * Metadata Server: 10.126.239.233 *
361 * Port: 8562 *
362 * Location: /Eminer_Module/jobs *
363 * *
364 * Server: SASApp A5U5SY8D.AT000002 *
365 * *
366 * Source Table: ISSUANCE_MART - BIUO1.ISSUANCE_MART A5U5SY8D.BF000C1C *
367 * *
368 * Generated on: Thursday, December 3, 2015 7:05:11 PM IST *
369 * Generated by: sastest2 *
370 * Version: SAS Data Integration Studio 4.9 *
371 ****************************************************************************/
372
373 /* Generate the process id for job */
374 %put Process ID: &SYSJOBID;
Process ID: 9055
375
376 /* General macro variables */
377 %let jobID = %quote(A5U5SY8D.C1000BO4);
378 %let etls_jobName = %nrquote(mail_test);
379 %let etls_userID = %nrquote(sastest2);
380
381 %global applName;
382 data _null_;
383 applName="SAS Data Integration Studio";
384 call symput('applName',%nrstr(applName));
385 run;

NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:09,345+05:1800| _DISARM| WorkspaceServer| _DISARM| | _DISARM| |
_DISARM| | _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| | _DISARM| | _DISARM| | _DISARM| |
_DISARM| | _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds

386 /* Performance Statistics require ARM_PROC sub-system */
387 %macro etls_startPerformanceStats;
388 %log4sas();
389 %log4sas_logger(Perf.ARM, 'level=info');
390 options armagent=log4sas armsubsys=(ARM_PROC);
391 %global _armexec;
392 %let _armexec = 1;
393 %perfinit(applname="&applName");
394 %global etls_recnt;
395 %let etls_recnt=-1;
396 %mend;
397 %etls_startPerformanceStats;
MPRINT(LOG4SAS): ;
MPRINT(ETLS_STARTPERFORMANCESTATS): ;
MPRINT(ETLS_STARTPERFORMANCESTATS): ;
MPRINT(ETLS_STARTPERFORMANCESTATS): options armagent=log4sas armsubsys=(ARM_PROC);
MPRINT(PERFINIT): options notes nosource nosource2 nosymbolgen nomprint nomlogic
MPRINT(PERFINIT): NOSYMBOLGEN ;
15 The SAS System 19:03 Thursday, December 3, 2015

MPRINT(PERFINIT): ;
MPRINT(ETLS_STARTPERFORMANCESTATS): ;
398
399 %macro etls_setArmagent;
400 %let armagentLength = %length(%sysfunc(getoption(armagent)));
401 %if (&armagentLength eq 0) %then
402 %do;
403 %log4sas();
404 %log4sas_logger(Perf.ARM, 'level=info');
405 options armagent=log4sas armsubsys=(ARM_PROC);
406 %end;
407 %mend etls_setArmagent;
408
409 %macro etls_setPerfInit;
410 %if "&_perfinit" eq "0" %then
411 %do;
412 %etls_setArmagent;
413 %global _armexec;
414 %let _armexec = 1;
415 %perfinit(applname="&applName");
416 %end;
417 %mend etls_setPerfInit;
418
419 /* Setup to capture return codes */
420 %global job_rc trans_rc sqlrc syscc;
421 %let sysrc=0;
422 %let job_rc = 0;
423 %let trans_rc = 0;
424 %let sqlrc = 0;
425 %let syscc = 0;
426 %global etls_stepStartTime;
427 /* initialize syserr to 0 */
428 data _null_; run;

NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:09,372+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 14852096| _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.000000| _DISARM|
0.002744| _DISARM| 1764768849.370255| _DISARM| 1764768849.372999| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

429
430 %macro rcSet(error);
431 %if (&error gt &trans_rc) %then
432 %let trans_rc = &error;
433 %if (&error gt &job_rc) %then
434 %let job_rc = &error;
435 %mend rcSet;
436
437 %macro rcSetDS(error);
438 if &error gt input(symget('trans_rc'),12.) then
439 call symput('trans_rc',trim(left(put(&error,12.))));
440 if &error gt input(symget('job_rc'),12.) then
441 call symput('job_rc',trim(left(put(&error,12.))));
442 %mend rcSetDS;
443
444 /* Create metadata macro variables */
16 The SAS System 19:03 Thursday, December 3, 2015

445 %let IOMServer = %nrquote(SASApp);
446 %let metaPort = %nrquote(8562);
447 %let metaServer = %nrquote(rliblrsundb01n);
448
449 /* Set metadata options */
450 options metaport = &metaPort
451 metaserver = "&metaServer";
452
453 /* Setup for capturing job status */
454 %let etls_startTime = %sysfunc(datetime(),datetime.);
455 %let etls_recordsBefore = 0;
456 %let etls_recordsAfter = 0;
457 %let etls_lib = 0;
458 %let etls_table = 0;
459
460 %global etls_debug;
461 %macro etls_setDebug;
462 %if %str(&etls_debug) ne 0 %then
463 OPTIONS MPRINT%str(;);
464 %mend;
465 %etls_setDebug;
MPRINT(ETLS_SETDEBUG): OPTIONS MPRINT;
466
467
17 The SAS System 19:03 Thursday, December 3, 2015

468 /*==========================================================================*
469 * Step: Extract A5U5SY8D.C3000KHJ *
470 * Transform: Extract *
471 * Description: *
472 * *
473 * Source Table: ISSUANCE_MART - BIUO1.ISSUANCE_MART A5U5SY8D.BF000C1C *
474 * Target Table: Extract - BIUO1.mail A5U5SY8D.BZ000JCI *
475 *==========================================================================*/
476
477 %let transformID = %quote(A5U5SY8D.C3000KHJ);
478 %let trans_rc = 0;
479 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.);
480
481 /* Access the data for BIUO1 */
482 LIBNAME BIUO1 BASE "/ORASAS1/";
NOTE: Libref BIUO1 was successfully assigned as follows:
Engine: BASE
Physical Name: /ORASAS1
483 %rcSet(&syslibrc);
484
485 %let etls_recCheckExist = 0;
486 %let etls_recnt = 0;
487 %macro etls_recordCheck;
488 %let etls_recCheckExist = %eval(%sysfunc(exist(BIUO1.ISSUANCE_MART, DATA)) or
489 %sysfunc(exist(BIUO1.ISSUANCE_MART, VIEW)));
490
491 %if (&etls_recCheckExist) %then
492 %do;
493 %local etls_syntaxcheck;
494 %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck));
495 /* Turn off syntaxcheck option to perform following steps */
496 options nosyntaxcheck;
497
498 proc contents data = BIUO1.ISSUANCE_MART out = work.etls_contents(keep = nobs) noprint;
499 run;
500
501 data _null_;
502 set work.etls_contents (obs = 1);
503 call symput("etls_recnt", left(put(nobs,32.)));
504 run;
505
506 proc datasets lib = work nolist nowarn memtype = (data view);
507 delete etls_contents;
508 quit;
509
510 /* Reset syntaxcheck option to previous setting */
511 options &etls_syntaxcheck;
512 %end;
513 %mend etls_recordCheck;
514 %etls_recordCheck;
MPRINT(ETLS_RECORDCHECK): options nosyntaxcheck;
MPRINT(ETLS_RECORDCHECK): proc contents data = BIUO1.ISSUANCE_MART out = work.etls_contents(keep = nobs) noprint;
MPRINT(ETLS_RECORDCHECK): run;

NOTE: The data set WORK.ETLS_CONTENTS has 86 observations and 1 variables.
NOTE: PROCEDURE CONTENTS used (Total process time):
real time 0.09 seconds
cpu time 0.00 seconds
18 The SAS System 19:03 Thursday, December 3, 2015


NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:09,662+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 14852096| _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.000000| _DISARM|
0.092780| _DISARM| 1764768849.570053| _DISARM| 1764768849.662833| _DISARM| 0.000000| _DISARM| | _ENDDISARM

MPRINT(ETLS_RECORDCHECK): data _null_;
MPRINT(ETLS_RECORDCHECK): set work.etls_contents (obs = 1);
MPRINT(ETLS_RECORDCHECK): call symput("etls_recnt", left(put(nobs,32.)));
MPRINT(ETLS_RECORDCHECK): run;

NOTE: There were 1 observations read from the data set WORK.ETLS_CONTENTS.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:09,670+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 14852096| _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.000000| _DISARM|
0.004522| _DISARM| 1764768849.665819| _DISARM| 1764768849.670341| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MPRINT(ETLS_RECORDCHECK): proc datasets lib = work nolist nowarn memtype = (data view);
MPRINT(ETLS_RECORDCHECK): delete etls_contents;
MPRINT(ETLS_RECORDCHECK): quit;

NOTE: Deleting WORK.ETLS_CONTENTS (memtype=DATA).
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:09,675+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 14852096| _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.010000| _DISARM|
0.002909| _DISARM| 1764768849.672564| _DISARM| 1764768849.675473| _DISARM| 0.010000| _DISARM| | _ENDDISARM
NOTE: PROCEDURE DATASETS used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

MPRINT(ETLS_RECORDCHECK): options SYNTAXCHECK;
515
516 %let SYSLAST = %nrquote(BIUO1.ISSUANCE_MART);
517
518 /* Runtime statistics macros */
519 %etls_setPerfInit;
520 %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|Extract), metrNam6=_DISROWCNT, metrDef6=Count32) ;
MPRINT(PERFSTRT): options notes nosource nosource2 nosymbolgen nomprint nomlogic
MPRINT(PERFSTRT): NOSYMBOLGEN ;
MPRINT(PERFSTRT): ;
MPRINT(PERFSTRT): ;
521
522 %global etls_sql_pushDown;
523 %let etls_sql_pushDown = -1;
524 option DBIDIRECTEXEC;
525
526 /*---- Map the columns ----*/
527 proc datasets lib = BIUO1 nolist nowarn memtype = (data view);
528 delete mail;
529 quit;

NOTE: Deleting BIUO1.MAIL (memtype=DATA).
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:09,704+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 14852096| _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.000000| _DISARM|
0.016624| _DISARM| 1764768849.687881| _DISARM| 1764768849.704505| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: PROCEDURE DATASETS used (Total process time):
19 The SAS System 19:03 Thursday, December 3, 2015

real time 0.01 seconds
cpu time 0.00 seconds

530
531 %put %str(NOTE: Mapping columns ...);
NOTE: Mapping columns ...
532 proc sql;
533 create table BIUO1.mail as
534 select
535 X_POLICY_NO
536 from &SYSLAST
537 ;
NOTE: Table BIUO1.MAIL created, with 1219227 rows and 1 columns.

538 quit;
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:15,790+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 15376384| _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 5.280000| _DISARM|
6.082799| _DISARM| 1764768849.708115| _DISARM| 1764768855.790914| _DISARM| 4.400000| _DISARM| | _ENDDISARM
NOTE: PROCEDURE SQL used (Total process time):
real time 6.08 seconds
cpu time 5.28 seconds

539
540 %let SYSLAST = BIUO1.mail;
541
542 %global etls_sql_pushDown;
543 %let etls_sql_pushDown = &sys_sql_ip_all;
544
545 %rcSet(&sqlrc);
546
547 %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1)));
MPRINT(PERFSTOP): options notes nosource nosource2 nosymbolgen nomprint nomlogic
NOTE: _DISARM|A5U5SY8D.C3000KHJ|rliblrsundb01n|Extract| _DISARM| STOP| _DISARM| 2015-12-03T19:04:15,797+05:1800| _DISARM|
SAS_Data_Integration_Studio| _DISARM| SAS| _DISARM| 1219227| _DISARM| 15376384| _DISARM| 11952128| _DISARM| 11| _DISARM| 11|
_DISARM| 0| _DISARM| 0| _DISARM| 5.280000| _DISARM| 6.114255| _DISARM| 1764768849.683739| _DISARM| 1764768855.797994|
_DISARM| 4.400000| _DISARM| | _ENDDISARM
MPRINT(PERFSTOP): NOSYMBOLGEN ;
MPRINT(PERFSTOP): ;
MPRINT(PERFSTOP): ;
548 %let etls_recnt=-1;
549
550
551
552 /** Step end Extract **/
553
554
20 The SAS System 19:03 Thursday, December 3, 2015

555 /*---- Start of Post-Process Code ----*/
556
557 data _null_;
558 OPTIONS EMAILHOST =("RCLDAKGAPP3.RELIANCECAPITAL.COM");
559 file outbox ;
560 TO=("bibekananda.biswasb@relianceada.com");
561
562 subject=("My SAS Output");
563 /* attach=("/sasdata/SAMPLE_REPORT.pdf" "/sasdata/SAMPLE.pdf") */
564 put "&etls_stepStartTime";
565 put "&etls_endTime";
566 put 'Folks,';
567 put 'Attached is my output from the SAS';
568 put 'program I ran.';
569 put 'It worked great!';
570 run;

NOTE: The file OUTBOX is:
Filename=/SAS/sasconf_Lev2/Lev2/SASApp/outbox.dat,
Owner Name=sassrv,Group Name=sas,
Access Permission=-rw-r--r--,
Last Modified=03Dec2015:19:04:16

NOTE: 6 records were written to the file OUTBOX.
The minimum record length was 6.
The maximum record length was 34.
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:16,130+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 15376384| _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.010000| _DISARM|
0.037735| _DISARM| 1764768856.092683| _DISARM| 1764768856.130418| _DISARM| 0.000000| _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds

571 /*---- End of Post-Process Code ----*/
572
573 %rcSet(&syserr);
574 %rcSet(&sqlrc);
575
576 %let etls_endTime = %sysfunc(datetime(),datetime.);
577
578 /* Turn off performance statistics collection */
579 data _null_;
580 if "&_perfinit" eq "1" then
581 call execute('%perfend;');
582
583 run;

MPRINT(PERFEND): options notes nosource nosource2 nosymbolgen nomprint nomlogic ;
MPRINT(PERFEND): ;
MPRINT(PERFEND): ;
MPRINT(PERFEND): options NOTES SOURCE NOSOURCE2 MPRINT NOMLOGIC NOSYMBOLGEN ;
MPRINT(PERFEND): ;
MPRINT(PERFEND): ;
NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2015-12-03T19:04:16,141+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM|
| _DISARM| 15376384| _DISARM| 11952128| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 0| _DISARM| 0.010000| _DISARM|
0.005713| _DISARM| | _DISARM| 1764768856.141829| _DISARM| 0.010000| _DISARM| | _ENDDISARM
NOTE: DATA statement used (Total process time):
21 The SAS System 19:03 Thursday, December 3, 2015

real time 0.00 seconds
cpu time 0.01 seconds

NOTE: CALL EXECUTE generated line.
1 + options notes nosource nosource2 nosymbolgen nomprint nomlogic ;;; options NOTES SOURCE NOSOURCE2 MPRINT NOMLOGIC
NOSYMBOLGEN ;;;;
584

 

 

 

 

job execute successfully but mail not send.

LinusH
Tourmaline | Level 20

Ok, so it's an email sending problem now, not a DI one.

You need to figure out what differs between your DI Sessions and EG/SAS DMS ones.

Running PROC OPTIONS in each tool can be one way.

OS authorizations, are you authenticating differently, or even using different user id?

Data never sleeps
Patrick
Opal | Level 21

@Shantaram

You've got already all the support for making this work directly out of  DIS. 

 

Just to throw in another option:

I personally believe such a tasks shouldn't get implemented in DIS directly but should be handled by the scheduler (LSF or whatever you're using).

 

Shantaram
Calcite | Level 5

Hi Patrick,

It's working in EG perfectly. But some technical issue is going on in DIS that's why this is not working.
Some network related issue is there. Otherwise code is working properly.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 9 replies
  • 3239 views
  • 0 likes
  • 3 in conversation