When we have multiple Interface, Variable  and other ODI objects inside the Child Package  and when such Child Package Fails we get the Error message ‘The scenario did not end properly’  and we need to look into the operator .

Here is a simple code  and process which can look into the Child Session and get the correct error message and also later we can capture the same in the Error Log .

 

Create a Global Variable called “ GET_ERROR_MSG” and paste the following codes ,  so you can call and use it for multiple package.

SELECT ' The  '||CASE
WHEN SSS.STEP_TYPE='F' THEN 'INTERFACE'
WHEN SSS.STEP_TYPE='VD' THEN 'VARAIBLE DECLARATION'
WHEN SSS.STEP_TYPE='VS' THEN 'SET/INCREMENT VARAIBLE'
WHEN SSS.STEP_TYPE='VE' THEN 'EVALUATE VARIABLE'
WHEN SSS.STEP_TYPE='V' THEN 'REFRESH VARIABLE'
WHEN SSS.STEP_TYPE='T' THEN 'PROCEDURE'
WHEN SSS.STEP_TYPE='OE' THEN 'OS COMMAND'
WHEN SSS.STEP_TYPE='SE' THEN 'ODI TOOL'
WHEN SSS.STEP_TYPE='RM' THEN 'REVERSE ENGINEERING MODEL'
WHEN SSS.STEP_TYPE='CM' THEN 'CHECK MODEL'
WHEN SSS.STEP_TYPE='CS' THEN 'CHECK SUB MODEL'
WHEN SSS.STEP_TYPE='CD' THEN 'CHECK DATA STORE'
WHEN SSS.STEP_TYPE='JM' THEN 'JOURNALIZE MODEL'
WHEN SSS.STEP_TYPE='JD' THEN 'JOURNALIZE DATA STORE'
END||' '||SSS.STEP_NAME||
' , has failed due to - '||
SNET.TXT
FROM <%=odiRef.getObjectName("SNP_SESSION")%> SS,
<%=odiRef.getObjectName("SNP_SESS_STEP")%> SSS,
<%=odiRef.getObjectName("SNP_EXP_TXT")%> SNET
WHERE SSS.SESS_NO=SS.SESS_NO
AND SNET.I_TXT=SS.I_TXT_SESS_MESS
AND SNET.TXT_ORD=0
AND SS.PARENT_SESS_NO ='<%=odiRef.getPrevStepLog("SESS_NO")%>'

 
[ Note :select the Work repository schema in the variable so that getobjectname can calculate accordingly the values, if in case you are using other schema which have select permission on work Repository schema , replace <%=odiRef.getObjectName(“”)%> with the required schema name. ]

In the first step call an OdiOutFile and uncheck Append option so that new File is created for a daily run  with  Header and Date .

Call the Child package and call the Variable   and  join the KO step ( red) to the Variable so that when ever the Child Package fails only then the variable is called else process the next child Package.

In the next step , call the OdiOutFile and mention the path to File mentioned in Step 1 and in the Text mention the variable name and keep repeating the steps for multiple child packages.

This way you can capture all the Failed ODI objects Error Messages into Single Log and send them as an attachment through OdiSendMail.