Integration Services Mail Task 255 character limitation

or: How to use a variable as Recipient in a SSIS Mail Task

Microsoft Integration Services (SSIS) offer a really simple mail task.

Enter a recipient, a subject and a text, configure the SMTP connection and send.

BUT! What about using a dynamic recipient list, or a To line with more than 255 characters?

Solution 1: Using Variables in Recipient list

First of all a Script Task or a SQL Task to populate your variables

Dts.Variables["User::mailTo"].Value = "user1@example.org, user2@example.org, user3@example.org";
Dts.Variables["User::mailCC"].Value = "user4@example.org, user5@example.org";

NOTE: Use comma to separate mail addresses instead of semicolons!

Set Properties Expression for Mail Task

Select the Mail Task, go to the properties and add expressions for your variables.

Solution 2: Do it all in a Script Task in C#

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;

namespace ST_1cc4564fdaba43baa9f85c0d6b327744
{

 [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  {
        String mailContent;
        String mailFrom;
        String mailTo;
        String mailSubject;
        String mailCC;
        String mailBCC;

      public void Main()
      {

            mailContent = @"
                            Hello World,
                            This ist the mail content!
                ";
            mailFrom = "me@example.org";
            mailTo = "test1@example.org, test2@example.org";
            mailCC = "test3@example.org, test4@example.org";
            mailSubject = "Mail Subject";
            mailBCC = "me@example.org";

            MailMessage mail = new MailMessage(mailFrom, mailTo, mailSubject, mailContent);
            mail.CC.Add( mailCC );
            mail.Bcc.Add( mailBCC );

            SmtpClient mySmtpClient = new SmtpClient("your.mailserver.com");
            mySmtpClient.Send(mail);

            Dts.TaskResult = (int)ScriptResults.Success;
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };

   }
}