ODI Alert Mechanism

Today, I have read an article about recommendation about not using red lines (ko) in ODI packages. I just want to explain how I manage alert mechanism in ODI.
This is a sample package that I use in extraction phase.

Generally we all want to use parallel execution (Asynchronous Mode) for extractions. I_EXTR_1, I_EXTR_2 and I_EXTR_3 are all in Asynchronous Mode. ODIWaitForChildSession has the property of 1 for Max. Number of Failed Child Sessions. If any of my scenarios fail, ODIWaitForChildSession will continue on ko (red line).

V_ERROR_DESC is a variable refresh based on a SQL Statement which prepares ODISendMail’s body section. You can find or create your own query based on Repository or using Groovy.

OdiSendMail is tool completely runs on variables.

• Mail Server : #V_MAIL_SERVER_IP (refreshes from a parameter table at the beginning of ETL) is a variable. When IP address of mail server is changed (nothing in Information Technology is static) admin only need to change the IP address in a parameter table.
• From : This is static, since we have a LDAP user authenticated in active directory. Nearly impossible for us to change.
• To : V_ERROR_MAIL_TO (refreshes from a parameter table at the beginning of ETL) is a variable. This variable can contain group mail address or individual mail addreses that are stored in “somebody@host.com;someotherbody@host.com”.
• CC : V_ERROR_MAIL_CC (refreshes from a parameter table at the beginning of ETL) is a variable. This variable can contain group mail address or individual mail addreses that are stored in “somebody@host.com;someotherbody@host.com”.
• BCC : V_ERROR_MAIL_BCC (refreshes from a parameter table at the beginning of ETL) is a variable. This variable can contain group mail address or individual mail addreses that are stored in “somebody@host.com;someotherbody@host.com”.
• Subject : ODI Error – Session : <%=odiRef.getSession(“SESS_NAME”)%>; Agent : <%=odiRef.getSession(“AGENT_NAME”)%>. This option should be set thinking of people can implement in their rules or alerts.
• Attachment : For alert purposes I generally leave this option empty, but for information or DQ purposes I write here a “fix file name + variable name” (create_script1.txt => 1 is coming from variable) to attach a file to the mail. I will write this usage to another post in detail.
• Message Body : #V_ERROR_DESC variable that is refreshed before executing OdiSendMail.

Final step is a variable named RAISE_ERROR, which contains a dump raise error statement to refresh to make you see a red exclamation in Operator to see the error. This is a workaround since OdiSendMail sends mail successfully, you cannot see red ERROR exclamation in Operator. To see it, just put something (that will fire an error) here to receive an error at the end of package.

About these ads