SSIS - how to send an Email as a Destination in a dataflow?

Question

I need to send a daily report to my boss with analyzed data. the data flow works just fine, but instead of the flat file destination, i want to send him an email with the data in the body.

I searched for it all over, and found how to send it through "for each loop" but it sends the output as a single row through multiple emails.

I need the WHOLE data in one mail.

any suggestions?

Answer 1

There is no native SSIS componentry to help you with this. However, you can build it out of the existing pieces.

You can use a Script Component as a destination and then accumulate all the values there and then in the PostExecute portion (which would indicate you have received all the rows) use .NET to send the email.

If you're doing no transformation logic in your data flow, investigate whether (assuming SQL Server) the DBAs have configured [sp_send_dbmail][1] You can provide a query as a parameter to that procedures. You can also specify whether you want the results in line or as an attachment.

If you're going the attachment route, write the results to a text file and then use the native Send Mail Task and simply include it.

Answer 2

This is one way to do it:

1. Create a string variable called MyMessageBody
2. In your data flow, create a transform script component and add MyMessageBody variable to the ReadWriteVariables list.

3. In your script component have the following script (notice that the idea would be to concatenate the data from each row to the MyMessageBody variable as the rows are going through the script component).

   [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
   public class ScriptMain : UserComponent
   {
   
   
       private String _emailBody = "";
   
   
       public override void PostExecute()
       {
           base.PostExecute();
           Variables.MyMessageBody = _emailBody;
   
       }
   
       public override void Input0_ProcessInputRow(Input0Buffer Row)
       {
   
              // define all the columns you need to return in your email
              // I am assuming that you are returning two columns here
               var Var1 = Row.Var1_IsNull ? "NULL": Row.Var1;
               var Var2 = Row.Var2_IsNull ? "NULL" : Row.Var2;
   
             // concatenate data rows as they are going through the component
               _emailBody += String.Format(@"Var1: {0}
                            Var2: {1}",
                   Var1, Var2
                   );
   
       }
   
   }
   

4. Finally, add a "Send Mail Task" and use an Expression for the MessageSource reading the variable that you just constructed in the data flow (MyMessageBody).