email Notifications in ODI

In order to send email notifications on load failure/success, follow below mentioned steps.

For Failure Notification

1.Create Global Variables.

Variable 1: EMAIL_SUB

SELECT 'ATTENTION REQUIRED...Load Failed at Session : '|| (SELECT sess_name FROM (SELECT sess_name FROM snp_session WHERE sess_status = 'E' ORDER BY sess_beg DESC) WHERE ROWNUM < 2)||'('|| (SELECT sess_no FROM (SELECT sess_no FROM snp_session WHERE sess_status = 'E' ORDER BY sess_beg DESC) WHERE ROWNUM < 2)|| ')'FROM DUAL

Variable 2: EMAIL_BODY

SELECT DBMS_LOB.SUBSTR(error_message,1000,1)

FROM (SELECT error_message FROM snp_session WHERE sess_status = 'E' ORDER BY sess_beg DESC)

WHERE ROWNUM < 2

2. Create a package with above created variables along with “OdiSendMail” utility

add above created variables and then OdiSendMail utility to the package as shown in below order

EMAIL_SUB à EMAIL_BODY à OdiSendMail

3.Provide SMTP server details in “OdiSendMail”

4.Generate scenario for the above package

5.Use this package scenario in Generated load plan under Exceptions so that it will be called whenever exception occurs.

For Success Notification

Add an "OdiSendMail" utility at the end of loadplan (i.e last scenario in loadplan)

Provide necessary details in the utility options.

Email Subject: Load Completed

Email Body: Load Executed Successfully

Note: You can also create separate variables ( for SUB and BODY) to add any dynamic information like load execution time,Environment name etc.

This step will be executed as part of last session in the loadplan.