8 Replies Latest reply on Jul 12, 2018 4:12 AM by Nico Heinze

    Informatica Stored procedure

    Ankit Gohil New Member

      Hello All,

       

      I am using connected stored procedure in my mapping.This stored procedure sends a mail after It has loaded the datamart tables.The code for sending the email is written in db procedure only.There is no email fired from Informatica for stored proc.Now the issue is, whenever this mapping is run It sends 2 email messages.Basically this procedure takes 10 mins to complete.but it sends 1 mail at 6th min and one at 10th minute when the proc status is completed in Informatica. Now I don't understand why it is sending 2 email message which has exactly the same email body.I have gone through entire mapping but couldn't understand the issue.The procedure is called only one.Its not called again in pre post session.

       

      Can someone please suggest,where I am going wrong.

       

      Regards,

      Ankit

        • 1. Re: Informatica Stored procedure
          Srini Veeravalli Guru

          I would suggest to go with Java transformation for emailing.

          • 2. Re: Informatica Stored procedure
            Srini Veeravalli Guru

            You just need add few lines and rest is default code from Java Transformation.

             

            try

                {

                // Get system properties

                Properties props = System.getProperties();

             

                // Setup mail server

                props.put("mail.smtp.host", SMTPSERVER);

             

                // Get session

                Session session = Session.getDefaultInstance(props, null);

             

                // Define message

                MimeMessage message = new MimeMessage(session);

             

                // Set the from address

                message.setFrom(new InternetAddress(FROM));

             

                // Set the to address

                message.addRecipient(Message.RecipientType.TO,

                  new InternetAddress(TO));

             

                // Set the subject

                message.setSubject(SUBJECT);

            BodyPart messageBodyPart = new MimeBodyPart();

             

             

            messageBodyPart.setText("If the attached file has any IsoCodes listed, those currencies were not updated in Sales Force. Note: If the file contains NO records, all applicable currencies are updated.");

            Multipart multipart = new MimeMultipart();

            multipart.addBodyPart(messageBodyPart);

             

            messageBodyPart = new MimeBodyPart();

            DataSource source = new FileDataSource(FILE);

            messageBodyPart.setDataHandler(new DataHandler(source));

             

             

            messageBodyPart.setFileName(FILE);

            multipart.addBodyPart(messageBodyPart);

             

             

            message.setContent(multipart);

                // Set the content

               // message.setText(Body);

             

                // Send message

                Transport.send(message);

                        STATUS= "True";

                } catch(Exception e)

                {

                logError(e.getMessage());

                        STATUS= "False";

                }

            • 3. Re: Informatica Stored procedure
              Ankit Gohil New Member

              Hi Srini,

               

              Thanks for your reply.However sending an email is done by procedure itself and as per the procedure the email will be fired only when the loading is completed.But I am getting an email twice.

               

              Regards,

              Ankit

              • 4. Re: Informatica Stored procedure
                Srini Veeravalli Guru

                Looks like issue is with sp I would suggest to test that outside of Informatica first.

                 

                I dont want to use SP when I'm using specifically Informatica! Its great tool just need to know how to use it. if any other ETL tool then I may based on case. This is my opinion.

                • 5. Re: Informatica Stored procedure
                  Nico Heinze Guru

                  Ankit, are you absolutely sure that:

                  1. the SP is called nowhere else?

                  2. there is no email task, no pre- or post-session email, or whatever executed which may send the same mail twice?

                   

                  In any case I would work with your network administrators; they should check the email configuration of the email server over which this email is distributed.

                   

                  Regards,

                  Nico

                  • 6. Re: Informatica Stored procedure
                    Ankit Gohil New Member

                    Hi Srini,

                     

                    When I execute the procedure directly from the database,the mail gets fired only once.So I believe,the procedure has no issue.

                     

                     

                    Regards,

                    Ankit

                    • 7. Re: Informatica Stored procedure
                      Ankit Gohil New Member

                      Hi Nico,

                       

                      The SP is called no where else.When I execute the procedure directly from the database,the mail gets fired only once.So I believe,the procedure has no issue.

                       

                      Also we have other mappings where we have used email tasks.There the mails gets fired only once.There are no multiple mails.So I think email configuration on the server is correct.

                       

                      Regards,

                      Ankit

                      • 8. Re: Informatica Stored procedure
                        Nico Heinze Guru

                        But it might well be (depending on the configuration of the mail server) that this Stored Procedure when invoked via PowerCenter Stored Procedure transformation has some different properties for sending emails than when invoked in the DB.

                         

                        Believe it or not, I have a similar problem with some antivirus vendor. For months now I get emails every now and then from them (after having re-registered for our company-wide anti-virus software). No problems. For two weeks now I see each email being sent to me twice. Without any apparent reason.

                         

                        Email servers have bugs, too. So this might be an email server bug.

                         

                        Again, I can only suggest that you ask your network administration (in particular those guys responsible for the email server) to double-check the email configuration and to run debug logs so that they may be able to verify whether it's a server setup or whatever.

                         

                        Regards,

                        Nico