Hi every one…
In the last few posts we have shown how to display the SQL results in Operator , fetch the failed objects errors message in Package and various other techniques. Today we want to cover another small scripts which can enable you fetch details of the Interface that have error out because of PK , FK ,Not Null constraints etc and goes to the Error table.
This scripts creates a File and dumps all the information by reading from SNP_CHECK_TAB table and finally file can be added to OdiSendMail and send to the Administrator or Developers so that they can know which interface got error records, so they can do the needful and also in daily load we fail to see all these smaller details especially we have hundreds of interfaces.
import string import java.sql as sql import java.lang as lang import re sourceConnection = odiRef.getJDBCConnection("SRC") output_write=open('c:/snp_check_tab.txt','w') sqlstring = sourceConnection.createStatement() #--------------------------------------------------------------------------- output_write.write("The Errored Interface of today's (<%=odiRef.getSysDate( )%>) run are .... n") output_write.write("----------------------------------------------------------- nn") #--------------------------------------------------------------------------- sqlstmt="SELECT 'Errored Interface t- '||SUBSTR(ORIGIN,INSTR(ORIGIN,')')+1, LENGTH(ORIGIN))||'nError Message tt- '||ERR_MESS||'nNo of Errored Records t- '|| ERR_COUNT AS OUTPUT FROM ODI_TEMP.SNP_CHECK_TAB WHERE TRUNC(CHECK_DATE)=TRUNC(SYSDATE)" result=sqlstring.executeQuery(sqlstmt) rs=[] while (result.next()): rs.append(str(result.getString("output")+'t')) res='nn'.join(map(string.strip,rs)) print >> output_write, res sourceConnection.close() output_write.close()
[Note – In the above scripts please change the File path and the Schema(Work Schema ) name according to your respective Environment ]
Provide the Technology and Schema of your work Schema or required schema which can access the SNP_CHECK_TAB and provide the code in Command on Target
and for every run you will get the sample output as shown below.
Attach the File to OdiSendMail and get the daily Error Interface , Message and Records detail .
Download the Codes
Comments are alwasys welcome!
See you soon…
April 5, 2016 at 8:40 PM
While scanning my files on to ODI, I am not able to see what I printed. How do I fix this?
April 19, 2016 at 6:17 PM
Hi Sophia… I couldn’t understand what do you mean…
Can you explain with more details?
June 26, 2011 at 3:18 AM
Hi,
I have an issue in log file name. I’m loading data into Hyperion Financial Management. In the IKM of the SQL to HFM data, we have an option of log file enabled. I made it true and gave the log file name as ‘HFM_dataload.log’. After executing the interface when I navigate in to that log folder and view the log file, that file is blank. Also a new file ‘HFM_dataloadHFM6064992926974374087.log’ is cerated and the log details are displayed in it. Since I have to automate the process of picking up the everyday log file and mail it to the users,
* I need the log details to be displayed in the specified log name i.e. ‘HFM_dataload.log’
* Also I was not able to perform any action(copy that newly generated log file into another or send that file in mail), since I’m not able to predict the numbers generated along with the specified log file name.
Kindly help me to overcome this issue.
Thanks in advance.
June 27, 2011 at 9:00 AM
Please look into the KM how ODI is creating the number in HFM_dataloadHFM6064992926974374087.log and through that same logic u can pickup the Log file or u can add the command to rename to what ever file u wish to create and use that in your email.
Please let us know if you need any other help.
Thanks
Kshitiz Devendra
March 23, 2011 at 7:16 AM
hi ,
i did the same thing as you said in the above one , but the error is showing that table or view does not exist
March 23, 2011 at 7:24 AM
Here SELECT ‘Errored Interface t- ‘||SUBSTR(ORIGIN,INSTR(ORIGIN,’)’)+1, LENGTH(ORIGIN))||’nError Message tt- ‘||ERR_MESS||’nNo of Errored Records t- ‘|| ERR_COUNT AS OUTPUT FROM ODI_TEMP.SNP_CHECK_TAB WHERE TRUNC(CHECK_DATE)=TRUNC(SYSDATE)”
replace the schema name ODI_TEMP with your work schema name where the SNP_CHECK_TAB exist and please re run again .
Thanks