SSIS Replace Email Addresses in Send Mail Task

Lazy Sep! 3 weeks to my holiday!!

Business is changing very quick. Couple months ago, I posted topics such as How to replace images in a SSRS report, and How to replace connection strings in a SSRS report. Now, let me show you how to replace email addresses in the Send Mail Task of the SSIS package.

The scenario is simple, my company's domain changed, so we have to change email's from, to, cc, bcc accounts in quite a lot packages. Within the SSIS package, we have two ways to send an email: by using Send Mail Task, or by calling sp_send_dbmail in Execute SQL task.

The later case is simple, as we usually store email addresses in a helper table. So simply we just need to update email addresses stored in the helper table.

The first case is what I would like to show you, so have a look:

First of the first, we need to add the references into the project. Two specific references we need to add for this task, one is ManagedDTS, and another one is the SendMailTask.

Basically the ManagedDTS gives you a global view of the package, and the SendMailTask gives you specific management capacity for the component. So for example, if you want to replace the file path within the File System Task, you need to add Microsoft.SqlServer.FileSystemTask.

Now in the using section we need to add what we just referenced

After we prepared our project, we can start to 1. load the package, 2. loop the components, 3. do some changes.

One thing needs to notice at step 2, every component can be treated as an Executable in a package scope. But the container component, i.e. For Loop, Foreach Loop, and Sequence containers can contain child executables. Therefore within my code, I need to detect object type of the executable, and then do my change email address task.

To make it simple, I just create a recursive method to loop the executable.

     public void LoopExecutables(Executable e)  
     {  
       if (e is TaskHost)  
       {          
         TaskHost th = (TaskHost)e;  
         if (th.InnerObject is Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailTask)  
         {  
           SendMailTask smt = (SendMailTask)th.InnerObject;  
           smt.FromLine = Regex.Replace(smt.FromLine, "<old email address>", "<new email address>", RegexOptions.IgnoreCase);  
           smt.ToLine = Regex.Replace(smt.ToLine, "<old email address>", "<new email address>", RegexOptions.IgnoreCase);  
           smt.CCLine = Regex.Replace(smt.CCLine, "<old email address>", "<new email address>", RegexOptions.IgnoreCase);  
           smt.BCCLine = Regex.Replace(smt.BCCLine, "<old email address>", "<new email address>", RegexOptions.IgnoreCase);  
         }  
       }  
       else if (e is Sequence)  
       {  
         Sequence seq = (Sequence)e;  
         foreach (Executable e2 in seq.Executables)  
         {  
           LoopExecutables(e2);  
         }  
       }  
       else if (e is ForEachLoop)  
       {  
         ForEachLoop fel = (ForEachLoop)e;  
         foreach (Executable e2 in fel.Executables)  
         {  
           LoopExecutables(e2);  
         }  
       }  
       else  
       {  
         MessageBox.Show(e.GetType().FullName);  
       }  
     }  

Now remaining task becomes straightforward. We load the package, we loop the executables in the package, and finally we save the package. Below is the Main() method:

           public void Main()  
           {  
       Microsoft.SqlServer.Dts.Runtime.Application App = new Microsoft.SqlServer.Dts.Runtime.Application();  
       Package package = new Microsoft.SqlServer.Dts.Runtime.Package();  
       string PackagePath = Dts.Variables["strPackagePath"].Value.ToString();  
       string NewPackagePath = Dts.Variables["strNewPackagePath"].Value.ToString();  
       if (File.Exists(PackagePath))  
       {  
         package = App.LoadPackage(PackagePath, null);  
         try  
         {  
           foreach (var item in package.Executables)  
           {              
             LoopExecutables(item as Executable);  
           }  
           string Folder = Path.GetDirectoryName(NewPackagePath);  
           if (!Directory.Exists(Folder))  
           {  
             Directory.CreateDirectory(Folder);  
           }  
           App.SaveToXml(NewPackagePath, package, null);  
         }  
         catch (Exception ex)  
         {  
           MessageBox.Show(ex.Message);  
         }  
       }  
                Dts.TaskResult = (int)ScriptResults.Success;  
           }  

One last thing to mention, if you want to replace the variable value of the package, remember to check variable's namespace, as usually you do not want to touch system variables, like below

           foreach (var item in package.Variables)  
           {  
             Variable var = (Variable)item;  
             if (var.Namespace == "User")  
             {  
               //do something  
             }  
           }