ODI SendMail

Here is a brief description how you can handle ODI Sendmail command. This command is quite useful to send mails on errors or success in flow.
First of all ODISendMail can be used in ODI packages by drag and drop under in Toolbox. Here is a short explanation;

REFRESH CUBE : A simple procedure that starts the cube refreshment job.
P_CHECK_CUBES : A scenario that checks whether cube is refreshed or not. Succeeds when cube refresh process finishes successfully.
V_CUBE_MAIL_BODY : Refreshes a variable to be used in mail’s body section. Includes a select statement for descriptive usage.
OdiSendMail 25 : This is the pain point. Sends the prepared mail. Properties are below;

Mail Server : IP address of your SMTP mail server.
From : A valid username that can send mail from previously entered SMTP Mail Server.
To : A variable or direct mail address that has been written in TO section of mail. I personally prefer using variables, because it is much more easy to change a record in a table instead of changing lots of OdiSendMail steps. If more than 1 mail address is needed simply seperate each mail address with semicolumn (;) (i.e.: name1@domain.com;name2@domain.com)
CC : A variable or direct mail address that has been written in CC section of mail. Same as above.
BCc : A variable or direct mail address that has been written in BCc section of mail. Same as above.
Subject : Subject of mail to be sent. ODI Reference Functions and/or ODI Variables can be used.
Attachment : If there is any file to attach into mail, it needs to be written here. Attachment should be in ODI Agent’s operating system or a place that agent can access.
Message Body : Body of mail. Can be text including ODI Variable and/or ODI reference functions as well.

Hope this will help.
Do please contact me if you have any further questions.

Advertisements

54 thoughts on “ODI SendMail

  1. Hello,
    I need a help regarding ODI.
    I am trying to convert EBCDIC file to ASCII using ODI. My file have only one record and I have to parse the file and read 4 bytes, which will have information how long 1st record is. So there is no record separator present. Please help me suggesting approach and how to do it using ODI.

    Thank you
    Yogesh

    • I don’t know the size of the source file but since it has only one column I assume it shouldn’t be a large file. You don’t need a record seperator if you are importing only one column by pass all seperator fields in ODI definition.
      If I were you I would load file to a database (preferably Oracle via SQL Loader) and make transformations then output to file to ASCII again.
      Hope this helps you,
      Gurcan.

  2. Hi, im new to ODI but how to get yesterdays date to be posted on the subject line? i use for todays date but i wanted yesterdays date and i am not sure how to get it.

    • I would suggest you to use a text variable that gets the (sysdate – 1) value (written below) from database upon refresh and put the value in the subject line. I didn’t search it out yet, but there might be other ways to do in ODI with odiref functions.

      select to_char(sysdate – 1, ‘dd.mm.yyyy’) from dual;

  3. Hi and thanks for the info. Where can I find the procedures you are referencing here, REFRESH_CUBE and P_CHECK_CUBES, as well as the select statement for V_CUBE_MAIL_BODY.

    I am on 10.1.3.5

    • Hi Ryan,
      REFRESH_CUBE is the procedure that executes a MSSQL Server job which refreshes MSAS cubes, P_CHECK_CUBES is a ODI variable that selects MSSQL Server job-logs (sysjobhistory) and V_CUBE_MAIL_BODY is a variable that creates body of the mail including how much time the cube refresh process cost.

      • Thank you, I am working with Oracle Essbase so I guess I do not have these available. Thanks for the info.

      • These procedures and/or methods are not a part of ODI. You need to create them to your needs and environment. Let me give you some clue about them;
        – REFRESH_CUBE : Works in MSSQL topology with appropiate MSSQL Server connection, has only 1 step;
        exec msdb.dbo.sp_start_job ‘REFRESH_BI_JOBS’;

        – P_CHECK_CUBES is a package that refreshes V_CUBE_COUNT variable and evaluates if value = 0 (true goes to OdiSleep1 then refresh variable)

        – V_CUBE_COUNT is a variable that selects whether the job executed finished or not in MSSQL Server.
        select count(*) from mdsb.dbo.sysjobhistory where name = ‘REFRESH_BI_JOBS’ and run_date > sysdate and step_id = 0;

        -V_CUBE_MAIL_BODY is a variable that refreshes the mail body.
        select ‘Job is finished with ‘ + run_status + ‘ status at time of : ‘ + run_date from msdb.dbo.sysjobhistory where name = ‘REFRESH_BI_JOBS’ and run_date > sysdate and step_id = 0

  4. Hi,

    I have a requirement using ODI. I have to develop a application in which the data in the source dataserver has to update in the target dataserver every day. Can anyone please give me the solution for this requirement.

    Thank you.

    • You may write a simple ODI interface updating the target table (with Control Append or Incremental Update Knowledge Module) and schedule its scenario daily.

  5. Hi Gucan,
    Is providing SMTP server IP is enough to send mail or we need any other installation ?

    • Typing SMTP Server’s ip address would be enough for sending mail. You don’t need to install anything either in your client or the server that agent is running.

  6. Hi, I want to know if posible using some funtion get the IP Address from the Machine where the user execute the Package? and if is possible to get the information from the execution like if you have some error then get in some variable and insert in some table?

    • I don’t know an ODIRef method like that, but as a workaround, you may use OS commands to retrieve ip address and pass to a variable for future use.

      Cheers,
      Gurcan.

    • Actually, I did not face a requirement like that and there is no option for setting importance. But you may use database sendmail commands/scripts to set high importance.

  7. Hi ,
    We have an ODI interface for File to Oracle table i.e. we read a flat file and map data to one of the oracle tables .

    Our requirement is to send mail to some users with these flat files .
    Can you please suggest some way to attach the same file in email which was picked up in the interface .

    Regards,
    Neeraj

  8. Hi , I need to send mail the resylt of a query as the body. it returns a tabular resultset and i also want to write some text in the start od msg body.

    how can i do that in ODI?

    thanks

    • Hi,
      You can add your custom text in the mail body and attach the output of the query as a file (preferably a CSV file). Since variables are text based and have a maximum size, it could be not enough to load all the output as a tabular result set.

  9. hello,

    we need to set email notification for load plans status success or failure
    please provide how to email notifications in odi 11g

    Thanks,
    Sravan

  10. Hello,
    I have the following problem:javax.mail.MessagingException: Could not connect to SMTP host: smtp.gmail.com, port: 25;

    do not know how to fix it

    • I didn’t use GMail as a mail server up to now. Only used Exchange provider and it didn’t give any error up to now. GMail probably don’t let you to connect to smtp server on port 25.

  11. Hi Gurcan,
    There is the possibility to execute a process every certain amount of time, meaning that you can run every 2 hours

    Regards,

    Julio Cesar

  12. Hi Sir,

    I am trying to use odisendmail,
    mail server: smtp,gmail.com
    from: mygmail.com
    to: yourgmail.com
    body:test
    attachement: path of attachement

    after executing the package I am getting error as
    com.sun.mail.smtp.SMTPSendFailedException: 530 5.7.0 Must issue a STARTTLS command first. ux2sm6525024pac.46 – gsmtp

    please help me 😦

    • Hi Santhosh,
      As far as I know, GMail (or let me rephrase as IMAP mail servers) are not supported, but need to check and be sure. You’d better either use an Exchange Server (I used it without any problem) or search for GMail documentation for sending mails from ODI.
      It says SMTP Send failure where it means that you cannot login to SMTP server and you must start with a “STARTTLS” (This maybe a command for logging in). As said, you better check the GMail documentation.

      Best — Gurcan.

  13. Hi Gurcan,
    about odisendmail, it’s possible to insert in the message boby html code?
    I’d like to add in the message body of an email at the end of a job, an html table to visualize some data about the run of the job just finished.
    Thank’s
    Claudio

    • Hi Cladio,
      I haven’t tried it yet, but you can. You need to make some modifications to your Exchange so that the user that is logging on to Exchange Server (the user you defined in ODISendMail job) can send HTML formatted mails by default.
      Cheers — Gurcan.

      • Hi Gurcan,

        Even I would like to know how we can enhance the message body in OdiSendMail. I know that our Exchange Server can send emails with better visualisations. Is there any way in which we can integrate OdiSendMail with HTML?

        Thanks,
        Awaise Ahmed

  14. Hi Gurcan,

    In Odisendmail, can we do text formatting of the message body?
    I want to see a heading inside my message body, with a bigger font or a better appearance.
    Can we do something like that in ODI?

    Thanks
    Awaise

    • I haven’t tried that, but if you are using Exchange Server, it will support html codes that you can use in your body. Try sending mail with below example.

      My Example

      Cheers — Gurcan.

      • Hi Gurcan,

        I didnt quite understand how you did that.

        If I write html script in the message body of OdiSendMail, the whole script is recieved in the mail, along with the html tags,

        Thanks,
        Awaise

      • There must be a way that Outlook client understand and formats mail with html codes. I didn’t had any problem when I wrote html tags in body of the mail, you need to get in touch with your Exchange admin and he needs to do some configuration so that sender (what you typed in ODISendMail) can send html based mail. It seems your mails are sending in plain text.

        Cheers — Gurcan.

  15. Hi Gurcan,

    I’m trying to write a generic email notification package that could be used by multiple load plans. Do you know if there’s an ODIref that will return the load plan name (on which I could then base load plan specific processing)?

    The nearest I’ve come across is ‘odiRef.getLoadPlanStepInstance’ mentioned elsewhere in your blog that returns the STEP_NAME but I can’t find any documentation for it and whether it or another ODIref will give me anything relating to the load plan under which the package/scenario is being executed,

    Thanks in advance,

    Andy

  16. Hi Gurcan,

    I tried using OdiSendMail Tool using Gmail configuration. Mail was not successfull.

    Kindly let me know, OdiSendMail will associate with Mail Server which doesn’t require password authentication.

    If No, Please let me know how to achieve it with some screenshots or video that I could understand it clear.

    If Authentication required, Is there any external procedure needs to be created in ODI? Kindly help to achieve the same.

    • Hi, it is always suggested to use Exchange mail, instead of GMail. I think that the reason is about IMAP and related authentication settings.

      • Hi Gurcan,

        Thanks for spending your valuable time to reply me. I tried with Exchange mail server as you suggested. I got error message,

        Error:

        ODI-1217: Session PKG_MAIL (37) fails with return code 7000.
        ODI-1226: Step OdiSendMail 1 fails after 1 attempt(s).
        ODI-1241: Oracle Data Integrator tool execution fails.
        Caused By: com.sun.mail.smtp.SMTPSendFailedException: 530 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM

        at com.sun.mail.smtp.SMTPTransport.issueSendCommand(SMTPTransport.java:2203)
        at com.sun.mail.smtp.SMTPTransport.mailFrom(SMTPTransport.java:1694)
        at com.sun.mail.smtp.SMTPTransport.sendMessage(SMTPTransport.java:1194)
        at com.sunopsis.dwg.tools.SendMail.actionExecute(SendMail.java:207)
        at com.sunopsis.dwg.function.SnpsFunctionBaseRepositoryConnecte……..

        I dragged OdiSendMail in package and provided the parameters of mail server, port, user name etc.. While trying to execute it, I got above error.

        In another scenario, I got ReadTimeOut error also.

        Is approach correct? or Do I need to choose any other way?

  17. Hi Gurcan,

    Issue got fixed, I tried in ODI12C. It was issue with network security and Configuration file in ODI.

    We have to enable the firewalls in ODI Server and Mail Server.

    AddVMOption -Dmail.smtp.starttls.enable=true needs to be added in product.conf file.

    That’s it. EMail flies..:-)

    • Hi,

      We are able to send mail using odisendmail. It works all good with local agent but fails with the same error with javaagent on web logic. Any solution?

      • I recommend you to check authorisation between Web Logic Server and EMail Server. If the command sent to the EMail server are same (I believe it is), WLS is sending the same command instead of local agent. So, there should be something wrong with the IP Addresses (maybe port is blocked or firewall, etc.).

        Best — Gurcan.

  18. Hi Gurcan,

    By using your block as reference i have used Odisendmail tool in ODI 12c, its working fine.

    But i have a requirement that I dont want to use Odisendmail tool to get mail notification rather i want to configure mail notification through weblogic Mail Sessions and i want to use it in the package.

    Can we do this way???

    Please do needful.

    Thanks & Regards,
    Shiva Kumar.

    • It can be done and all you need to do is to use some selects from ODI repository (I don’t know whether weblogic supports this) and select the outputs of these selects via Weblogic Mail Sessions.
      But, definitely cannot use these mails within ODI’s Load Plans or Packages. 🙂

      Cheers — Gurcan.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s