Use ETL / Integrator for e-mail notifications

    This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

    • Use ETL / Integrator for e-mail notifications

      Hello!

      I was wondering whether I could use the Integrator and groovy to accomplish the following:

      1. connect to a MS SQL Database (ok, I managed that)
      2. extract a table containing part numbers with erroneous accounting details (accomplished through SQL)
      3. a)in case the extract has values send an email with those values (here I am stuck)
      b)in case the extract returns nothing: do nothing

      I know this is not what the System is designed for, but I like how it handles the extracts and transforms and it would be easy to schedule the task.

      My idea was to include the mailing function into the groovy script of a field transform. But it does not seem to work. I can send emails if I create a Job that executes a groovy script, but I think I can't get to the extracted data that way. And including the same mail script into the goovy function of a field transform does nothing.

      Does anyone have an idea how I could accomplish the sending of data from a Field Transform?

      I hope I managed to explain what I would like to do. If not, please let me know and I will give it another try ;)

      Thanks!

    • Thanks laloune,

      that pushed me into the right direction. I studied the thread you mentioned and the example files and figured it out.

      Here is how I got it to work:

      Source Code

      1. source = API.initSource("E_MyExtract");
      2. int count=0;
      3. nachricht='FEHLERHAFTE TEILE: \n\n';
      5. while (source.nextRow() && count<100) {
      6. Firma = source.getColumnString("TLFIRM");
      7. Teilenummer = source.getColumnString("TLIDNR");
      8. Teilegruppe = source.getColumnString("TLTLGR");
      9. Bezeichnung = source.getColumnString("TLBEZG");
      10. LOG.info("FEHLERHAFTES TEIL: "+Teilenummer+" - Firma: "+Firma+" - Teilegruppe: "+Teilegruppe+" - Bezeichnung: "+Bezeichnung);
      11. nachricht = nachricht + Teilenummer+" - Firma: "+Firma+" - Teilegruppe: "+Teilegruppe+" - Bezeichnung: "+Bezeichnung+'\n';
      12. //LOG.info(nachricht);
      13. count=count+1;
      14. };
      16. if(count>0){
      17. mailer = API.getMailer();
      18. mailer.setServer();
      19. mailer.addRecipient("recipient1@email.de");
      20. mailer.addRecipient("recipient2@email.de");
      21. mailer.setSubject("Oxaion Fehler: Fehlerhaftes Teil mit Kontengruppe 999");
      22. mailer.setMessage("Guten Tag, \n\n es befinden sich "+count+" Teil(e) mit Kontengruppe 999 im Oxaion Teilestamm.\n\n"+nachricht);
      23. mailer.send();
      24. };
      Display All

    • Brilliant! Thank you Laloune.

      I now have a different problem where I am unable to send the mail out because of gmail settings.. trying to fix that part. Surprisingly, the notifications from the task scheduler are working fine. It is only the groovy job that us unable to overcome the gmail security.

      Any ideas?

      Kiran

      The post was edited 1 time, last by kiran.gs ().

    • Here is the error I am getting:

      Unable to send mail: 534-5.7.14 <https://accounts.google.com/ContinueSignIn?sarp=1&s.... please log in via your web browser and then try again. Learn more at 534 5.7.14 https://support.google.com/mail/answer/78754 a62sm43237789pfj.40 - gsmtp

      Note that the settings are working when I put in notifications on Task Scheduler. The error is coming only when I run the groovy job

      Please help.

      Kiran