Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Sending email from SQL Server Integration Services (SSIS)

MSSQLTips author Arshad Ali By:   |   Read Comments (16)   |   Related Tips: More > Integration Services Email

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:

 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 <New connection...> 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.


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.


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:

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



Last Update: 4/16/2009


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

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, August 13, 2009 - 11:46:08 PM - ramprasad Read The Tip

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


Wednesday, October 07, 2009 - 10:08:40 AM - arshad0384 Read The Tip

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.


Tuesday, September 14, 2010 - 10:30:21 PM - Jim Read The Tip


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='theemail@server.net',

@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, February 08, 2012 - 8:36:36 AM - jpSQLDude Read The Tip
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.

Sunday, February 19, 2012 - 1:23:33 AM - vinay Read The Tip
I cannot make it work for Gmail for the smtp.gmail.com

Sunday, February 19, 2012 - 8:01:40 AM - Greg Robidoux Read The Tip
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 26, 2012 - 10:07:01 PM - Vinay Read The Tip

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.


Friday, March 02, 2012 - 4:32:54 PM - Cody Read The Tip

 

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'test@email.com',

@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 ='';

 

Monday, April 02, 2012 - 4:02:58 AM - Diana Read The Tip

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 


Wednesday, July 04, 2012 - 3:13:47 AM - Jit Read The Tip

 

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'".


Thursday, July 05, 2012 - 12:32:40 PM - Chris Read The Tip

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.   


Monday, August 27, 2012 - 7:32:09 AM - Mahesh Read The Tip

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???


Tuesday, December 04, 2012 - 5:31:26 PM - Arlene Read The Tip

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. 


Wednesday, January 09, 2013 - 9:08:36 AM - Akash Rajput Read The Tip

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.


Wednesday, February 13, 2013 - 8:08:27 AM - K.Sharmila Read The Tip

 

Hi friend,

 

As, i want to send birthday wishes image to mail.

 

This is my coding in sql mail sending query,

 

 

EXEC msdb.dbo.sp_send_dbmail

@recipients='projectjamesnagar@gmail.com',

@subject ='Birthday Wishes',

@file_attachments = 'c:\MyFolder\image.gif',

@body=N'<p>Image Test</p><img src="image.gif" /><p>See image there?</p>',

@body_format = 'HTML',

@profile_name ='SQlAlert';

its working good but, it will be display image as attachment in mail.

My requriement is if i send birthday wishes image to mail using sql mail sending query. Once i login my mail click inbox it will be display image in message body automatically.

Please any one solve this problem for me.


Wednesday, May 22, 2013 - 2:44:44 AM - mallesweara Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.