This blog will cover how to configure Email Notification on Load Plan (ETL) Failure with ODI.

This is most frequent requirement of client to configure email notification on ETL Failure. If you don’t have any email marketing expert  then  Konnectogrow can help you for the same. Konnectogrow provides you the best digital marketing services in Pune and Noida, you can get the expert solution for your email marketing strategy.
If you are working with Oracle Data Integrator (ODI) you can create custom package to sent email notification on ETL Failure.
If you are working with Oracle Business Intelligence Application (OBIA) 11.1.1.x version we have two option to configure email notification :
  1. You can create a custom Package to sent email notification on ETL Failure.
  2. It can be configure through BIACM.
I will cover the first option in this blog and will cover second one in another blog.
We need to fellow below steps to configure mail in ODI:

Step 1 : Create 2 Global variable

img1

Step 2 : MESSAGE_SUBJECT :
SELECT ‘Error occurred on 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
image2

Step 3 : MESSAGE_BODY :

SELECT CAST (error_message AS VARCHAR2 (3000)) FROM (SELECT error_message FROM snp_session WHERE sess_status = ‘E’ ORDER BY sess_beg DESC) WHERE ROWNUM < 2

img3

Step 4 : Select the schema “ODI_BIAPPS11G” in the Refreshing Section of both variables.
Step 5 : Create a new package
img5

Step  6 : In the overview section provide the name of the package “PKG_EMAIL_NOTIFICATION”

img6

Step 7 : Go to diagram tab of package and drag and drop MESSAGE_SUBJECT and MESSAGE_BODY global variable from Global Objects and OdiSendMail object from Internet section.

img7

Step 8 : Set below parameters on the general section of the properties tab.

  • Mail Server : IP address of the SMTP server
  • From : Address of the sender of the message
  • To : List of email addresses of the recipients, separated by commas.
  • Cc : List of e-mail addresses of the CC-end recipients separated by commas.
  • BCc : List of email-addresses of the BCC-end recipients, separated by commas.
  • Subject : #MESSAGE_SUBJECT
  • Attachment : Attach the files
  • Message Body : #MESSAGE_BODY

img8

Step 9 : Save the package.

Step 10 : Right click on the package and select Generate Scenario

img10

Step 11 : Then New Scenario Window will appear specify the name of scenario and Version.

Scenario: PKG_EMAIL_NOTIFICATION

Version: 001

img11

Step 12 : Go to the Load Plans and Scenarios -> Generated Load Plans

img12

Step 13 : Then open the load plan which is currently used for in your case its “UAE UAT FULL LOAD – 10_145_20160428_033624”
img13
Step 14 : Go to exceptions tab.
img14
Step 15 : Select “Serial” and click on and select “Run Scenario Step”
img16
Step 16 : Add Run Scenario Step window will open
img17
Step 17 : Enter Scenario Name, Version and Step Name as show below
Scenario Name: PKG_EMAIL_NOTIFICATION
Version: 001
Step Name: PKG_EMAIL_NOTIFICATION
img18
Step 18 : Use ‘Finish’ to save.
Step 19 : Go to Steps tab.
Step 20 : Select the step and select the root step in our case its “Start Load Plan”.
img20
Step 21 : Set below parameters on the Exception Handling section of the step.
Restart Type : Restart from new session
Timeout : set the timeout value
Exception step : Select the name of the exception in our its “Global – Export Logs and notification”
Exception Behavior : Run Exception and Raise
img22
Step 22 : When load plan is executed and if any error occurs on the load plan step the exception will be raised and “PKG_EMAIL_NOTIFICATION” package will be executed.
If you don’t have any email marketing expert  then  Konnectogrow can help you for the same. Konnectogrow provides you the best digital marketing services in Pune and Noida, you can get the expert solution for your email marketing strategy.