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.
Leave a reply →