Sending email from SQL Server Integration Services

By:   |   Comments (25)   |   Related: 1 | 2 | More > Integration Services Development


Problem

Sending an email is a frequent requirement to notify a user on the occurrence of certain events, especially if an unexpected event happens (for example sending notification on failure that could be either logical or physical). SSIS provides a built-in "Send Mail Task" to send email in these circumstances. The Send Mail Task is quite simple and straight forward in its configuration and use, but it has some inherent limitations for example, first it supports only sending plain text email (doesn't support HTML formatted mail) and second it doesn't support passing username and password while connecting to SMTP server (it only supports Windows authentication i.e. none windows authentication is not allowed) nor does it support specifying a SMTP port number to send emails if your SMTP server does not use the default value.

In part 1 of this tip series, I will first start my discussion on using the built-in Send Mail Task and then in part 2 of this tip series, I will discuss using the "Script Task" to overcome the limitations imposed by Send Mail Task. I will show how you can send HTML formatted mails from SSIS using the Script Task or rather the .Net capabilities from your SSIS package.

Solution

When you drag a Send Mail Task from the toolbox to the control flow, you will notice there are three pages when you right click on the task and select Edit. On each page you will find a few settings which you might need to configure for sending emails.

These pages are:

  • General Page - Here you specify the name and a small description for your Send Mail Task. Though these are not mandatory, but it's a good practice to give a meaningful name and description.
  • Expression Page - You use the Expressions page to edit property expressions and to access the Property Expressions Editor and Property Expression Builder dialog boxes. Property expressions update the values of properties when the package / task are run. The expressions are evaluated and their results are used at runtime instead of the values to which you set the properties when you configured the task. The expressions can include variables and the functions and operators that the expression language provides. For example, you can generate the subject line for the Send Mail task by concatenating the value of a variable that contains the string "Weather forecast for " and the return results of the GETDATE() function to make the string "Weather forecast for 4/5/2009". You can refer to this KB article to learn more on how to use expression in Send Mail Task http://support.microsoft.com/kb/906547.
  • Mail Page - This is a place where you specify most of the configuration for your Send Mail Task as shown in below image:
ssis send mail task

Let me summarize the Mail Page configurations and give you a brief description of the above settings which you would normally do on this page as shown in the below table:

Property

Description

SMTPConnection

Select an SMTP connection manager in the list, or click to create a new connection manager. As discussed below, you have an option to attempt anonymous or Windows authenticated connection as well as enable Secure Socket Layer (SSL) to encrypt the communication.
An SMTP connection manager enables a package to connect to a Simple Mail Transfer Protocol (SMTP) server.

From

Specify the e-mail address of the sender, which may be used by recipient of the mail to reply back.

To

Provide the e-mail addresses of the recipients, multiple recipients emails are separated with semicolons.

Cc

Specify the e-mail addresses, multiple recipients emails are separated with semicolons, of individuals who also receive copies of the message.

Bcc

Specify the e-mail addresses, multiple recipients emails are separated with semicolons, of individuals who receive blind carbon copies (Bcc) copies of the message.

Subject

Provide a subject line for your e-mail message. You may configure it to dynamically change its value using expression as discussed below.

MessageSourceType

Select the source type of the message that could be either Direct Input which allows you to directly type your message in the box provided or File Connection which points to a file containing your message or Variable which allows your message content to come from a SSIS variable.

Priority

Set the priority of the message, it could be either Low, Normal or High.

Attachments

Provide the file names of attachments to the e-mail message, multiple attachments are delimited by the pipe (|) character.

When you create an SMTP connection manager, a dialog box similar as shown below will come up. Here you specify a meaningful name and a small description for this SMTP connection manager and then you specify the SMTP Server. The SMTP connection manager supports only anonymous authentication and Windows authentication. It does not support basic authentication. Check "Enable Secure Socket Layer (SSL)" option if you want to encrypt communication using Secure Sockets Layer (SSL) while sending e-mail messages.

One thing to note here is if you execute your package interactively from BIDS it uses the security context of the currently logged on user, whereas if you schedule it by executing a SQL Server Agent Job then it uses the account under which SQL Server Agent is running to connect to the SMTP host.

ssis smtp connection manager

So far we have learned about all of the settings of the Send Mail Task, now let me execute the package and see the result....so here goes the mail.

ssis send mail task

Note
You can configure and send emails using "Send Mail Task" programmatically as well, more details about how this can be done, can be found here on the MSDN site.

Caution
The maximum allowed length of an expression is 4,000 characters. While using an expression take this limitation into consideration or else you will end up having an error as shown below:

ssis expression builder error

If you are using expressions for the MessageSource property and your expectation is such that your email message source can grow to more than 4,000 characters then in that case instead of using expression use "MessageSourceType = Variable" and assign the value directly using a variable or think about using the Script Task to send emails (this will be discussed in part 2 of this tip series).

Conclusion

  • In the part 1 of this tip series I discussed how you can easily configure and use the built-in Send Mail Task of SSIS to send plain text emails, we also then learned about some of its limitations.
  • In part 2 of this tip series, I will be covering sending emails using the Script Task which overcomes the limitations imposed by Send Mail Task.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Sunday, September 16, 2018 - 8:45:12 PM - siddhartha Back To Top (77603)

Is it possible to use gmail to send email in send email task of SSIS.

As per my understanding gmail does not support window authentication or anonymous logon.  

 

Monday, February 20, 2017 - 12:18:56 PM - paul lee Back To Top (46526)

In BIussiness Intelligent Development of  SQL 2008 R2, there is no way for me to enter credential in the GUI.  will give me below error

 [Send Mail Task] Error: An error occurred with the following error message: "The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM".

 

I was wondering do you have working example to use C# script sample.  I have google internet for below code but still getting authenticate error.   

I understand Office 365 is cloud platform and was born after SQL 2008r2, I am able to configure DB mail using office 365 credential, but can't get C# or send mail task to work.

below is my C# script file

   public void Main()

        {

            // TODO: Add your code here

            MailMessage mail = new MailMessage("[email protected]", "[email protected]", "subject", "body");

            SmtpClient client = new SmtpClient("smtp.office365.com", 587);

            client.EnableSsl = true;

            client.UseDefaultCredentials = false;         

            client.Credentials = new System.Net.NetworkCredential("[email protected]", "Password1");

            client.Send(mail);

            Dts.TaskResult = (int)ScriptResults.Success;

        }

 

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Net.Mail.SmtpException: Mailbox unavailable. The server response was: 5.7.60 SMTP; Client does not have permissions to send as this sender

   at System.Net.Mail.DataStopCommand.CheckResponse(SmtpStatusCode statusCode, String serverResponse)

   at System.Net.Mail.SmtpConnection.OnClose(Object sender, EventArgs args)

   at System.Net.ClosableStream.Close()

   at System.Net.Mail.MailWriter.Close()

   at System.Net.Mail.SmtpClient.Send(MailMessage message)

   at ST_047592bcb03b4e35b4672bda525ea543.csproj.ScriptMain.Main()

   --- End of inner exception stack trace ---

   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)

   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)

   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Monday, February 22, 2016 - 4:17:45 PM - Greg Robidoux Back To Top (40749)

Hi Kris,

According to the link below it is not possible to pass in a UserName and Password.

It looks like connection manager can either do Window Authentication or Anonymous authentication.

https://msdn.microsoft.com/en-us/library/ms178682.aspx

You can take a look at this tip as well that uses a Script Task to send email.

https://www.mssqltips.com/sqlservertip/1753/sending-html-formatted-email-in-sql-server-using-the-ssis-script-task/

-Greg

Monday, February 22, 2016 - 4:05:10 PM - Kris Back To Top (40748)

 How to use UserName and Password for SMTP Connection Manager?

Any ideas?

 

Monday, June 15, 2015 - 2:04:58 PM - Mark Freeman Back To Top (37924)

It's hard to believe that no one had already submitted a Connect Feature Suggestion for this, so I just did. Please upvote https://connect.microsoft.com/SQLServer/feedback/details/1437047/ssis-send-mail-task-needs-additional-parameters.

Tuesday, January 27, 2015 - 2:06:56 AM - Siva Back To Top (36064)

I have written an Stored Prodcedure and it will return the count of gender (like 10 Female, 5 Male) from table. These details should be send to the SQL Administrator in the mail format (not HTML text) daily 1PM IST. I am using SQL Server 2012 Enterprise Edition.  Please let me know what are the steps that I need to follow and how much time it will take to complete this process. 

Thanks in Advance.

Sunday, October 26, 2014 - 8:41:31 AM - saurabh Back To Top (35066)

Hi Arshad,

 

you have used your gmail id for email configuration and also using windows authenticataion.

Could you please guide me how to set up my windows account as gmail.????

 

 

 

Wednesday, October 1, 2014 - 1:57:30 PM - Greg Robidoux Back To Top (34804)

Tubi Le,

Here is the second part: http://www.mssqltips.com/sqlservertip/1753/sending-html-formatted-email-in-sql-server-using-the-ssis-script-task/

-Greg

Wednesday, October 1, 2014 - 1:08:03 PM - Tubi Le Back To Top (34801)

Hi Again,

I have successfully sent email using the Send Mail Task in SSIS.

My package is to upload data to a table.

I am looking for the way to add to the body of the email the number of records of the table before and after the upload process.

I hope you have the instruction in your part 2.

Wednesday, October 1, 2014 - 1:04:47 PM - Tubi Le Back To Top (34800)

Thank you for your instruction.  Where is your part 2?

Tubi

Wednesday, May 22, 2013 - 2:44:44 AM - mallesweara Back To Top (24071)

Using basic credentials you cannot send mail using this task.need to use script task

Wednesday, January 9, 2013 - 9:08:36 AM - Akash Rajput Back To Top (21342)

My exchange do not accept anonymous request and works only with basic Authenticaiton.

Do you have any idea about doing the same with SSIS with basic authenticaiton or some other application which can do this.

Tuesday, December 4, 2012 - 5:31:26 PM - Arlene Back To Top (20744)

Would anyone know why occasionally some emails with an attachment are not received when sent using this method?  Out of 300 possible recipients, I have a few that tell me that they will receive it one day and then not the next.  This is an SSIS package running as a scheduled job. 

Monday, August 27, 2012 - 7:32:09 AM - Mahesh Back To Top (19234)

Hi,

when executing the ssis package(Send mail Task), after sending0 20 mails or after every 10 min's ,the package fails and we need to manually trigger the  Package .....

can any one suggest a solution for this issue???

Thursday, July 5, 2012 - 12:32:40 PM - Chris Back To Top (18360)

Folks, you need to specify your own SMTP server name i.e. whatever SMTP server your company uses to send out emails.   You can talk with the admin person who manages your email servers to get these details.   Just putting in what the author put as fake data will not help you.   Also, as Jim enountered, your SMTP server might have certain authentication requirements.  Again, you will have to work with the person that manages your email servers to sort this out.   Once that is properly configured, it really is irrelevant what email address you are sending to, gmail, hotmail, corporate email or otherwise.   

Wednesday, July 4, 2012 - 3:13:47 AM - Jit Back To Top (18339)

 

I also follow all instructions but it didnt work at all

It is giving some error

[Send Mail Task] Error: An error occurred with the following error message: "Failure sending mail.  System.Net.WebException: The remote name could not be resolved: 'smptserver'".

Monday, April 2, 2012 - 4:02:58 AM - Diana Back To Top (16729)

hi,

Really nice tutorial ! but it seems it doesn't work for me, I already follow your instructions step by step and it can run successfully but I'm not receive any gmail email. I wonder why. Is there someone can help me ? Really need solution .

 

Thanks,

Diana 

Friday, March 2, 2012 - 4:32:54 PM - Cody Back To Top (16239)

 

Is there a way to call the function that will zip the file before emailing it, currently the file I have is too big to email?  Or perhaps calling an ftp function to ftp the file ?  Here is my code:

 

EXEC msdb.dbo.sp_send_dbmail

@subject = N'report',

@recipients=N'[email protected]',

@body = N'Daily Report',

@query = N'V40N05PROD.dbo.CSP_BILLS_STATUS_Export',

@body_format = 'HTML',

@attach_query_result_as_file=1,

@query_result_no_padding =1,

@query_attachment_filename='report.csv',

@query_result_separator ='';

 

Sunday, February 26, 2012 - 10:07:01 PM - Vinay Back To Top (16167)

Thanks for the link Greg. It is helpful for Db mail, I still dont understand how the author of this article was able to configure ssis for gmail.

Sunday, February 19, 2012 - 8:01:40 AM - Greg Robidoux Back To Top (16076)
See if this tip helps with setting up Gmail: http://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/

Sunday, February 19, 2012 - 1:23:33 AM - vinay Back To Top (16073)
I cannot make it work for Gmail for the smtp.gmail.com

Wednesday, February 8, 2012 - 8:36:36 AM - jpSQLDude Back To Top (15937)
Another option is to look in your SSIS toolbox under Maintenance Plan Tasks. You have a Notify Operator Task [which you already set up and are using in your Maintenance Plans, right? :) ], so you can just use this to easily send emails as well.

Tuesday, September 14, 2010 - 10:30:21 PM - Jim Back To Top (10159)


Hello all,

I created a package to extract data from the database, put the data in an Excel file, zip it, and then email the file.  I ran into a roadblock when my stmp server required authentication on a secure port (not port 25).

I found a work around to send email in an SSIS package if your smtp server requires authentication.  You can also send an HTML email.

You must have Database Mail already set up with the proper authentication.  If you don’t, there are many articles that show you how to set up database mail.  I will tell you that if you are using port 465 on your stmp server, for some reason, you need to specify port 587 in database mail.

In your SSIS package, simply add an “Execute SQL Task” step to your package.

In the SQLStatement entry box under the General Tab type:

EXEC msdb.dbo.sp_send_dbmail

@recipients='[email protected]',

@profile_name = 'Your Database Mail Profile Name’,

@subject = 'Your Subject',

@body = 'your body,

@file_attachments='U:\yourfilename.zip;

Database mail only allows a 1MB attachment as a default but this can be changed in “View or change system parameters”, “Maximum File Size (Bytes)”.

I hope this helps.

Jim

 

Wednesday, October 7, 2009 - 10:08:40 AM - arshad0384 Back To Top (4150)

Hi Prasad,

Thanks for your compliments.

This tip uses Send Mail Task to send plain text mail and no coding is required, only setting different properties of the Send Mail Task. It seems you are talking about the another tip Sending HTML formatted email in SQL Server using the SSIS Script Task which uses Script Task. For this, I have provided code for both the versions (2005 and 2008), kindly use the code for appropriate version and also check the notes at the end of the tip. If you still find problems, send me the exact errors you are getting or if possible also send snapshot image. I will try to help you out.

Thursday, August 13, 2009 - 11:46:08 PM - ramprasad Back To Top (3877)

Hi,

  It is an excellent tip for sending email from sql sis.I have tried the code given by you.I am using sql server 2005 and followed the steps mentioned in the tip.But when i tried to execute the package it is saying that "script task cannot run on this edition of integration services. it requires a higher level edition" Can u give any idea on this please.

 

 

Thanks,

Prasad














get free sql tips
agree to terms