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.
i like it
Comment by jeremy — 07.01.11 @ 03:24 |
Hope it solved some of your doubts or problems.
Comment by gurcanorhan — 12.01.11 @ 16:49 |
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
Comment by Yogesh — 23.03.11 @ 08:20 |
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.
Comment by gurcanorhan — 23.03.11 @ 09:27 |
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.
Comment by janet — 01.09.11 @ 20:22 |
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;
Comment by gurcanorhan — 01.09.11 @ 21:17 |
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
Comment by Ryan — 09.01.12 @ 20:35 |
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.
Comment by gurcanorhan — 10.01.12 @ 09:30 |
Thank you, I am working with Oracle Essbase so I guess I do not have these available. Thanks for the info.
Comment by Ryan — 10.01.12 @ 15:39
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
Comment by gurcanorhan — 11.01.12 @ 12:54
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.
Comment by raju — 28.06.12 @ 14:24 |
You may write a simple ODI interface updating the target table (with Control Append or Incremental Update Knowledge Module) and schedule its scenario daily.
Comment by gurcanorhan — 28.06.12 @ 15:14 |
Thank you very much for the reply……..
Comment by raju — 28.06.12 @ 15:35
Hi Gucan,
Is providing SMTP server IP is enough to send mail or we need any other installation ?
Comment by Anubha — 30.10.12 @ 07:00 |
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.
Comment by gurcanorhan — 30.10.12 @ 11:09 |
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?
Comment by caparrav — 16.11.12 @ 22:51 |
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.
Comment by gurcanorhan — 19.11.12 @ 09:24 |
How do we send email with high importance in ODI ?
Comment by kaykay — 12.12.12 @ 17:03 |
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.
Comment by gurcanorhan — 13.12.12 @ 08:53 |