To demonstrate the various solutions, I'm working with these four SSIS string variables. They contain the subject, body, from- and to address. Add these variables to your package and give them a suitable value. You could also use parameters instead if you're using 2012 project deployment.

Add these four variables to your package

B) Script Task
The Script Task is a little harder to use than the Send Mail Task (if you don't have .Net knowledge), but it doesn't have the same drawbacks as the Send Mail Task. It uses the SmtpClient Class and it has an HTML formatted mail option and a configurable port number.

1) SMTP Connection Manager
This first step is optional. I like to use as much as possible standard connection managers. Right click in the Connection Managers window and add an SMTP Connect manager. Add the SMTP server and change other options if necessary (other options are not used in this example). The alternative is to use an extra SSIS string variable for storing the SMTP Server.

SMTP Connection manager

2) Add Script Task
Add a Script Task to your Control Flow (or one of the event handlers). Give it a suitable name and add the SSIS variables as readonly variables to the Script Task.

Add all SSIS variables as ReadOnly

3) The Script
In the Scipt I'm using the variables and the connection manager to fill the properties of the SMTP client. Copy the contents of the Main method to your method and add the extra import/using on top.

19 comments:

Hi if we want send message to multiple mails that time how to modify this code can any body help me .and i tried between two mails i used ; symbol but its not working . String SendMailTo = Dts.Variables["SendMailTo"].Value.ToString();

Would it kill you to mention what version your developing this with. Seriously not everybody is up to 2012 and for those that aren't it might save them time in not bothering with the tutorial and looking for another solution with their earlier version.

Would it kill you to mention your name when you complain? If you read the instructions carefully you see that you must copy the content of my main method to your main method. Then it will just work fine in SSIS 2008 or even 2005. Hopefully you can now create your own blog to help all those that won’t understand mine.

I am working on a project that requires a Month (direct input in the package) to be passed into the Mail Subject line...I am struggling to make this happen as I have some knowledge of VB but I am new to SSIS. I have used the script option to actually send the mail message (I needed html functionality), and that works perfectly! Now, I just need a variable value (which does already exist) to be passed in, for example "This is a Line", where subject is a replaced variable value.

No dynamic is not possible. You always have to hardcode the variable name in the script. So either set all necessary variables as readonly variables and come up with an if/case-construction in the .net code where you choose the right variable...Or create an expression on a variable that fills it with the right value and use that variable in your Script Task.

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.

It's a very generic error without any clues, but it's probably a typo in a variable name (or the scope of the variable). Otherwise you need to debug your script task to which line is causing the error.