IT Blog

  • Blog
  • Technology
    • Technology
    • Architecture
    • CMS
    • CRM
    • Web
    • DotNET
    • Python
    • Database
    • BI
    • Program Language
  • Users
    • Login
    • Register
    • Forgot Password?
  • ENEN
    • 中文中文
    • ENEN
Experience IT
In a World of Technology, People Make the Difference.
  1. Home
  2. Technology
  3. Database
  4. Sending mail from SSIS

Sending mail from SSIS

2016-11-19 1217 Views 0 Like 0 Comments

SSIS toolbox provides Send Mail Task for sending emails from integration service. It allows only sending text mail with SMTP Connection Manager.

SMTP Connection Manager supports only anonymous and windows authentication. If you use mail server outside of the organization boundary or need to send html format mail this task is useless.

Here's the screenshots to describe this limitation.

Send Mail Task

 

SMTP Connection Manager

The alternative way, we could use Script Task to send mail directly or use Execute Process Task to trigger another process to send mails.

1. Send Mail using Script Task

a) First we create a few variables to pass mail information to the task.

Note that some ISPs block port 25, in this case you should use alternative port instead.

b) Pass variables to the task.

c) Scripts to be executed is as following. The namespace System.Net.Mail is needed in order to send mail.

        using System.Net.Mail;

        public void Main()
        {
            var smtpCredential = Dts.Variables["smtpCredential"].Value.ToString();
            var mailServer = Dts.Variables["mailServer"].Value.ToString();
            var mailPort = Dts.Variables["mailPort"].Value;
            var mailSender = Dts.Variables["mailSender"].Value.ToString();
            var mailRecepients = Dts.Variables["mailRecepients"].Value.ToString();
            var mailSubject = Dts.Variables["mailSubject"].Value.ToString();
            var mailBody = Dts.Variables["mailBody"].Value.ToString();
           
            var credential = smtpCredential.Split(',');
            var receipients = mailRecepients.Split(',');
            var user = credential[0];
            var pwd = credential[1];
            bool isHtml = true;
            MailMessage mail = new MailMessage();
            mail.IsBodyHtml = isHtml;
            mail.Subject = mailSubject;
            mail.Body = mailBody;
            mail.From = new MailAddress(mailSender);
            foreach (var m in receipients)
            {
                mail.To.Add(m);
            }
            SmtpClient smtp = new SmtpClient(mailServer, (int)mailPort);
            smtp.UseDefaultCredentials = false;
            smtp.Credentials = new System.Net.NetworkCredential(user, pwd);
            smtp.EnableSsl = false;
            smtp.Send(mail);
           
            Dts.TaskResult = (int)ScriptResults.Success;

        }

 
2. Send Mail using Execute Process Task
    
    By specifying the executable location, and optionally pass in some arguments, the task will trigger the other process to send mails. This especially useful in Service-Oriented Architecture.

Conclusion, there are many approaches for sending any kind of mails from integration service (SSIS) other than Send Mail Task.

 2,817 total views,  4 views today

error
fb-share-icon
Tweet
fb-share-icon
IT Team
Author: IT Team

Tags: Blog
Last updated:2016-11-19

IT Team

This person is lazy and left nothing

Like
< Previous
Next >
Chinese (Simplified) Chinese (Simplified) Chinese (Traditional) Chinese (Traditional) English English French French German German Japanese Japanese Korean Korean Russian Russian
Newest Hotspots Random
Newest Hotspots Random
All Unicode Chars How to keep and display contact form 7 data Common Regular Expressions How to block bad bots in Appache server Add ALT attribute to Social Media Icon Change Event Calendar Title
All Unicode Chars
Power BI First Taste github failed to push to the remote repository. see the output window for more details Crawling 51job in Python Add activity log for WordPress Duplicator - backup & restore Restrict WordPress user access some contents
Categories
  • Architecture
  • BI
  • C#
  • CSS
  • Database
  • DotNET
  • Hosting
  • HTML
  • JavaScript
  • PHP
  • Program Language
  • Python
  • Security
  • SEO
  • Technology
  • Web
  • Wordpress

COPYRIGHT © 2021 Hostlike IT Blog. All rights reserved.

This site is supported by Hostlike.com