SSIS Adding Resultsets in the Email Body

One of the nice features of using dbmail is that it allows you to embed a results set within the message body. Unfortunately SSIS 2005 does not allow for the same. The following is a work around for doing just that. It requires 3 workflow steps. The first is to export the data out to a text file using a data flow task. The second is to use a script task to read the contents to assign to a string variable, while the third is use that variable in your message source of the email task.

STEP 1 Export Resultset to a Text File
I won’t give a diagram here as it’s self explanatory. Use the dataflow task to export the resultset you want in your email to a text file. One bit of caution is use a delimited text file. The reason is when you go to email the body is limited to 4000 characters and in this method whites spaces in a fixed length eat up that space.

STEP 2 Suck up Text File Using Script Task
Once your data is exported into a file, the next workflow task is to suck up this data into a string variable. The example below uses the file object with the ReadAllLines method. Notice in the code below I’m importing the System.IO class, making it easy for referencing


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain


Public Sub Main()

Dim Output() As String
Dim DataHold As String
Dim ln As String
Output = File.ReadAllLines("d:\\ssisdev\testmail.txt")

‘Loop through each line and append to Datahold local variable
For Each ln In Output
DataHold = DataHold & ln
Next

‘Populate Variable
Dts.Variables("TextOutPut").Value = DataHold
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Step 3 Assign Source in Email task to the Variable
In the Send Mail Task enter the Expression. Select the MessageSource property and drag the variable populated in the previous step






This workaround is for small results as there is a 4000 Character limitation with the email task.