ODI SendMail

gurcanorhan Oracle Data Integrator, ,

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.

67 thoughts on “ODI SendMail”

  2. 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

    Reply

    • 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.

      Reply

  3. 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.

    Reply

    • 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;

      Reply

  4. 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

    Reply

    • 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.

      Reply

      • 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

  5. 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.

    Reply

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

      Reply

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

    Reply

    • 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.

      Reply

  7. 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?

    Reply

    • 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.

      Reply

    • 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.

      Reply

  9. 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

    Reply

  10. 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

    Reply

    • 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.

      Reply

  11. 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

    Reply

  12. 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

    Reply

    • 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.

      Reply

  13. 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

    Reply

  15. 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 😦

    Reply

    • 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.

      Reply

  16. 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

    Reply

    • 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.

      Reply

      • 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

  18. 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

    Reply

    • 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.

      Reply

      • 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.

  19. 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

    Reply

  21. 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.

    Reply

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

      Reply

      • 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?

  22. 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..:-)

    Reply

    • 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?

      Reply

      • 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.

  23. 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.

    Reply

    • 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.

      Reply

  24. Hi Gurcan,
    Very informative article.
    I have a question I want send mail to more than one person like 100 but using variable.
    Is it possible ?

    Thanks

    Reply

    • Yes, you can use variables in TO, CC or BCC field. You may also loop SendMail to send many mails by refreshing variables.
      Unfortunately, I didn’t send a mail consisting of 100 different mail addresses at one, used mail groups instead.
      What I’ve done os that, stored all the related mail addresses in a table (by adding some additional columns to filter) and concatenate by putting a “;” and wrote the variable in TO field.

      Hope this helps.

      Reply

      • Thanks Gurcan, Have you prepared any doc ? how to send mail to multiple users at same time without looping.

      • No, because it is so simple to use variables in the address fields. All you have to do is to refresh variable with format “address1@mail.com, address2@mail.com, etc…” (depending on your mail server “,” can be represented as “;”) by concatenating data with Oracle analytical functions and write the variable in TO, CC or BCC field (i.e. #TO_ADDRESSES).
        You may use below sample to concatenate rows into one.

        SELECT LISTAGG(to_mail_address, ‘,’) WITHIN GROUP (ORDER BY mail_group_to)
        FROM YOUR_MAIL_ADDRESS_TABLE
        GROUP BY mail_sending_id;

      • Thank you very much “(depending on your mail server “,” can be represented as “;”) “, here I was doing mistak.

    • Hi Priya,
      I did all my mailings from a single Exchange Server, unfortunately didn’t tried that. But logically it should work because ODI passes values of the variables during execution.

      Why don’t you give it a try (server’s IP address for instance) and write down the results over here?

      Best —Gurcan.

      Reply

  26. Hello Gurcan,

    I have been using ODI send mail for quite a while, by providing the ip address of MAIL server everything seems to go quite well.

    When I’m trying to use ODI ReadMail which actually fetches attachments and saves them in a location specified, I get prompted with this error:

    Caused by: javax.mail.MessagingException: Connect failed;
    nested exception is:
    java.net.ConnectException: Connection refused: connect

    As parameters I gave the same IP which I have been using to send mails.

    Mail User: email address with which I log in into my email

    Password: Email password with which I log in into my email

    Storage folder: \\10.246.3.49\c$\Users\bb\desktop\attachments

    I don’t know whether this is a miss typo/ syntax related problem or there is somekind of “permission” problem within ODI and mail server. It is weird because it is letting me send mails using that IP address.

    Reply

    • Hi Lorik,
      I haven’t tried using Outlook as a source. I believe that you need to work closely with Exchange Server admins, because it looks like that there is a access issue.
      Try to access from your client initially, than from the OS that ODI Agent is installed, then on Exchange Server OS.
      Your next move maybe, first read the attachments, then save them to a directory that ODI Agent can read, afterwards load them to DB. That looks like a compromising workaround.

      Let me know how it went.
      Cheers — Gurcan.

      Reply

  27. Hi guys , How to write all the rows of table in mail body in ODI 12c.(Not attachment).

    for eg:-

    output of table :-
    id,name,salary
    1,abc,1000

    so ODISENDMAIL body will be shown the above output in tabular format.

    Reply

  28. Hi

    Is there any option to embed images in header and footer of odi mail

    Reply

Leave a reply to julio Cesar Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.