SSIS email task or script task to send email

Question

Hello community, I have a question to see if someone did something similar.
I have an ETL in SSIS that at the end sends an email to different people with the box to send emails, however many times it fails due to server time out. My idea is to change either to a task script or something that allows me at least a number of forwardings if it fails, for example if it fails, try again to send the emails and so let's say about 4 times.
Anyone know a way? other than placing another task and if this fails, go to the other task.
Thank you!

Hola comunidad, tengo una duda para ver si alguien realizo algo similar.
Tengo un ETL en SSIS que al finalizar envía un correo a distintas personas con la cajita de enviar correos, sin embargo muchas veces falla por time out del servidor. Mi idea es cambiar bien sea por un task script o algo que me permita al menos un numero de reenvíos si es que falla, por ejemplo si falla intente de nuevo enviar los correos y asi digamos unas 4 veces.
Alguien conoce una manera ? que no sea colocar otro task y si este falla pasar al otro task.
Muchas gracias!

Answer

Hi @luis santaella ,

SSIS built-in Send Mail Task was created long time ago for SSIS 2005, was never updated since, and has many limitations:

Doesn't allow an email in HTML format.
Doesn't allow to specify port.
Doesn't support username/password for authentication.
Etc.

Please find below c# code that sends an email in HTML format. You can easily adjust it to add a retry functionality.

SSIS Script Task

void Main()  
{  
   try  
   {  
      string smtpAddress = ...  
      int portNumber = ...  
      bool enableSSL = ...  
      bool UseDefaultCredentials = ...  
  
      string emailFrom = ...  
      string password = ...  
      string emailTo = ...  
      string subject = ...  
      string body = ...  
  
      XElement xhtml = XElement.Parse(@"  
           
              
              
           
           
              
               Dear *eName*, *lname*  
               Congratulations on your purchase of a new Home. We hope you have moved in and are beginning to enjoy all the luxury and style distinctive to a Home.  We know you will be pleased with our continued commitment to your satisfaction.  
               Home professional construction staff built your home using quality products designed to offer you value now and in the years to come.  We are so confident in the quality of your home that we provide you with a two-year Workmanship Warranty â€“ double the industry standard.  
               In cases where you do have to use our warranty service, our goal is to address your request in a timely and   professional manner.  
               In order to best serve you, we ask that all requests for warranty service be submitted in writing to your customer service team.  Requests can be submitted one of two ways:  
               -Please visit.  
               -By mail at .  
               Once we receive your warranty request, you can expect:  
               -A meeting with a member of our professional construction staff within 5 days to review your request.  
               -Resolution of your request in a professional and timely manner.  
               All warranty work will be scheduled at a time when you can be at home during business hours (Monday through Friday 8:00am to 5:00pm).  
               Homes not only built your home using quality products, we also installed quality mechanical systems and appliances in your new home.  If you have a problem with an appliance or mechanical system, including air conditioning, electrical or plumbing, we ask that you first call the construction staff during normal business hours, as well as submit a request through our website.  
               Please be sure to register your appliances at www.geappliances.com and your AC unit at www.carrier.com. You will have 90 days from closing to register the AC unit.  
               Thank you again for choosing to purchase a new .  We sincerely appreciate the trust you have placed in us, and we look forward to continuing to serve you.  
               Sincerely,  
              
           
      ");  
  
      body = xhtml.ToString();  
        
      using (MailMessage mail = new MailMessage())  
      {  
         mail.From = new MailAddress(emailFrom);  
         mail.To.Add(emailTo);  
         mail.Subject = subject;  
         mail.Body = body;  
         mail.IsBodyHtml = true;  
         mail.Priority = MailPriority.High;  
  
         // mail.Attachments.Add(new Attachment(@"C:\SomeFile.txt"));  
         // mail.Attachments.Add(new Attachment(@"C:\SomeZip.zip"));  
  
         using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))  
         {  
            if (UseDefaultCredentials)  
            {  
               smtp.UseDefaultCredentials = true;  
               //smtp.Credentials = CredentialCache.DefaultNetworkCredentials;  
            }  
            else  
            {  
               smtp.Credentials = new NetworkCredential(emailFrom, password);  
            }  
  
            smtp.EnableSsl = enableSSL;  
            smtp.Send(mail);  
         }  
      }  
  
      Console.WriteLine("An e-mail has been successfully sent to: {0}.", emailTo);  
   }  
   catch (Exception ex)  
   {  
      Console.WriteLine(ex.ToString());  
   }  
}

Answer

Hi @luis santaella ,

If you want to trigger your mail task when any error occurs, consider the "Event Handlers".

You may check this blog for details ssis-event-handlers-basics.

To use script task to send email, you may refer script-task-in-ssis which provides information step by step.

Hope it could be helpful.

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

SSIS email task or script task to send email

2 answers