Hi I want to track the start and end time of DI job. Which is deployed...

Shantaram · Posted 12-03-2015 02:51 AM

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

LinusH · Posted 12-03-2015 03:34 AM

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 · Posted 12-03-2015 03:59 AM

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 · Posted 12-03-2015 04:30 AM

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 · Posted 12-03-2015 07:02 AM

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 · Posted 12-03-2015 07:19 AM

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 · Posted 12-03-2015 08:37 AM

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;

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;

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

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;

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

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;

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.

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

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;

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 · Posted 12-04-2015 03:05 AM

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 · Posted 12-04-2015 06:27 PM

@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 · Posted 12-05-2015 06:18 AM

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.

Hi I want to track the start and end time of DI job. Which is deployed on server. please help me.

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Re: Hi I want to track the start and end time of DI job. Which is deployed on server. please help me

Register Today!