Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Send Multiple Query Result Sets in HTML Tabular Format in a Single Email with SQL Server Integration Services


By:   |   Read Comments (6)   |   Related Tips: More > Integration Services Email

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

Often times it is required to send SQL Server query results in tabular format embedded in an HTML email. The complexity increases if the SQL Server query changes from time to time with a various number of columns. This in turn would add time to already arduous task of changing static code. In this tip, we will see how we can send a single or multiple query result sets in HTML table format in a single email using SSIS.

Solution

Using the SQL Server Integration Services (SSIS) Execute SQL Task and Script task coupled with a few variables I will address the above problem with the following:

  1. SQL Server query result set with varied columns – The package is set up such a way that it is not constrained with the number of columns. Any number of columns from the query result set can be sent via email.
  2. Dynamically sending multiple query result sets in one email – Multiple query results can be sent in one HTML email.

The SSIS Package is as below:

SSIS Send Email

Requirements

  • I am using the SQL Server 2012 with the SQL Server Integration Services installed. This example should work in SQL Server 2008 as well.
  • In this package I will use the TempDB database.

Getting Started

In this example, we are going to create two SQL Server query result sets and assign an object variable to each of the tasks. By using a script task to read the object variable we will create an HTML tabular message and send it to the recipients.

1. Let's start with the SQL Server Data Tools (SSDT) in SQL Server 2012:

Start a New Project in the SQL Server Data Tools

2. Under SSIS packages, rename the “Package.dtsx” to “Multiple Result Sets.dtsx”.

3. Open the “Multiple Result Sets” package and right click on Connection Managers.

SQL Server Integration Services Connection Objects

4. Click on “New OLE DB Connection” and Click “New” under “Configure OLE DB Connection Manager”.

5. Enter server name and in the database drop down select TempDB and click OK and OK.

Connection Manager Properties

6. Change the newly create OLE DB connection name to “Target”.

7. Drag two Execute SQL Tasks into the Control Flow and connect them.

8. Rename them as “SQL Query Result” and “SQL Query Result 1” respectively.

SQL Query Result Sets in Integration Services

9. Open “SQL Query Result” Execute SQL Task and change the connection to “Target” and Click OK.

SQL Server Integration Services Execute  SQL Task Editor

10. Perform the above operation for “SQL Query Result 1”.

11. Open “SQL Query Result” and paste the following SQL Query in the “SQLStatement” and change the “ResultSet” Property to “Full result set”

SQL Query1

CREATE TABLE #Temp1 
(
ID    INT IDENTITY PRIMARY KEY,
Name  VARCHAR(10) NULL, 
State  VARCHAR(2) NULL
)



INSERT INTO #Temp1 (Name,State)
SELECT 'Jack','MN'



INSERT INTO #Temp1 (Name,State)
SELECT 'Jake','TX'



CREATE TABLE #Temp2 
(
ID    INT IDENTITY PRIMARY KEY,
Name  VARCHAR(10) NULL, 
State  VARCHAR(2) NULL
)



INSERT INTO #Temp2 (Name,State)
SELECT 'Jill','CA'



INSERT INTO #Temp2 (Name,State)
SELECT 'John','AZ'



SELECT Name, State  FROM #Temp1
UNION
SELECT Name, State  FROM #Temp2



DROP TABLE #Temp1
DROP TABLE #Temp2

SQL Server Integration Services Execute  SQL Task Editor with SQL Statement

12. Open “SQL Query Result 1” and paste the following SQL Query in the “SQLStatement” and change the “ResultSet” Property to “Full result set”.

SQL Query2

CREATE TABLE #T1 
(
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL, 
Occupation VARCHAR(50) NULL
)



INSERT INTO #T1 (FirstName,LastName,Occupation)
SELECT 'Jack','Horn','Plumber'



INSERT INTO #T1 (FirstName,LastName,Occupation)
SELECT 'Jill','Log','IT Consultant'



SELECT FirstName,LastName,Occupation FROM #T1



DROP TABLE #T1

SQL Server Integration Services Execute  SQL Task Editor with SQL Statement

13. Upon completion of the above steps the package will be presented as follows:

SQL Server Integration Services SQL Query Results

14. Before we proceed further we will create few variables which will be useful when we add a Script task.

15. Create the following variables in the package. While creating the variable, make sure that the scope of the variables is at the package level, since the variables will be used throughout the package.

Property

Description

EmailBcc

Store one or many Email addresses

EmailCc

Store one or many Email addresses

EmailFrom

Store Sender Email address

EmailID

Store Sender Email address (If not using Windows Authentication)

EmailMessage

Store Body of Email

EmailPswd

Store password for EmailID

EmailSubject

Store Subject of the Email

EmailTo

Store Recipient Email address

FilePath

Store Directory/Folder information

QueryResult

Object data type to store first query result

QueryResult1

Object data type to store second query result

ScriptTaskErrorMsg

Capture Error Message


SQL Server Integration Services Variables

16. After the creation of the above variables, open the “SQL Query Result” task and click on Result Set and change the “Result Name” to “0” and in the “Variable Name” drop down select “QueryResult” and click OK.

SQL Server Integration Services Execute SQL Task Editor Result Set Mapping

17. After the creation of the above variables, open “SQL Query Result 1” task and click on Result Set and change the “Result Name” to “0” and in the “Variable Name” drop down select “QueryResult1” and click OK.

SQL Server Integration Services Execute SQL Task Editor Result Set Mapping

18. Drag a Script task onto the Control Flow and add it to “SQL Query Result 1”. Rename the Script task to “Email”.

SQL Server Integration Services Control Flow

19. Double click to open the email Script task. Drop down the “ScriptLanguage” and select “Microsoft Visual Basic 2010”.

SQL Server Integration Services Script Task Editor

20. In the “ReadOnlyVariables” add the previous created variables as seen below.

SQL Server Integration Services Script Task Editor Read Only Variables

21. Click on Edit Script.

22. Add the following Code:

Script: VB.Net Code for Script Task for SQL Server 2012

#Region "Help:  Introduction to the script task"
'The Script Task allows you to perform virtually any operation that can be accomplished in
'a .Net application within the context of an Integration Services control flow. 



'Expand the other regions which have "Help" prefixes for examples of specific ways to use
'Integration Services features within this script task.
#End Region



#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.SqlClient
Imports System.Net.Mail
Imports System.Net
Imports System.Collections.Generic
Imports System.Data.OleDb
Imports System.Text
Imports System.Text.RegularExpressions
Imports System.IO



#End Region



'ScriptMain is the entry point class of the script.  Do not change the name, attributes,
'or parent of this class.
<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase



#Region "Help:  Using Integration Services variables and parameters in a script"
    'To use a variable in this script, first ensure that the variable has been added to 
    'either the list contained in the ReadOnlyVariables property or the list contained in 
    'the ReadWriteVariables property of this script task, according to whether or not your
    'code needs to write to the variable.  To add the variable, save this script, close this instance of
    'Visual Studio, and update the ReadOnlyVariables and 
    'ReadWriteVariables properties in the Script Transformation Editor window.
    'To use a parameter in this script, follow the same steps. Parameters are always read-only.



    'Example of reading from a variable:
    ' startTime = Dts.Variables("System::StartTime").Value



    'Example of writing to a variable:
    ' Dts.Variables("User::myStringVariable").Value = "new value"



    'Example of reading from a package parameter:
    ' batchId = Dts.Variables("$Package::batchId").Value



    'Example of reading from a project parameter:
    ' batchId = Dts.Variables("$Project::batchId").Value



    'Example of reading from a sensitive project parameter:
    ' batchId = Dts.Variables("$Project::batchId").GetSensitiveValue()
#End Region



#Region "Help:  Firing Integration Services events from a script"
    'This script task can fire events for logging purposes.



    'Example of firing an error event:
    ' Dts.Events.FireError(18, "Process Values", "Bad value", "", 0)



    'Example of firing an information event:
    ' Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, fireAgain)



    'Example of firing a warning event:
    ' Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0)
#End Region



#Region "Help:  Using Integration Services connection managers in a script"
    'Some types of connection managers can be used in this script task.  See the topic 
    '"Working with Connection Managers Programatically" for details.



    'Example of using an ADO.Net connection manager:
    ' Dim rawConnection As Object = Dts.Connections("Sales DB").AcquireConnection(Dts.Transaction)
    ' Dim myADONETConnection As SqlConnection = CType(rawConnection, SqlConnection)
    ' <Use the connection in some code here, then release the connection>
    ' Dts.Connections("Sales DB").ReleaseConnection(rawConnection)



    'Example of using a File connection manager
    ' Dim rawConnection As Object = Dts.Connections("Prices.zip").AcquireConnection(Dts.Transaction)
    ' Dim filePath As String = CType(rawConnection, String)
    ' <Use the connection in some code here, then release the connection>
    ' Dts.Connections("Prices.zip").ReleaseConnection(rawConnection)
#End Region



    'This method is called when this script task executes in the control flow.
    'Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    'To open Help, press F1.
    Dim message As String



    Public Sub Main()
        Try
            '' Create a new dataset to store data tables
            Dim ds As New DataSet
            Dim oleDA As New OleDbDataAdapter
            '' Create as many data tables as needed
            Dim dt1 As New DataTable
            Dim dt2 As New DataTable
            Dim dt3 As New DataTable
            '' Fill the data tables with query result sets information
            oleDA.Fill(dt1, Dts.Variables("QueryResult").Value)
            oleDA.Fill(dt2, Dts.Variables("QueryResult1").Value)
            'oleDA.Fill(dt3, Dts.Variables("QueryResult2").Value)



            '' If data tables have rows then add it to the data set
            If dt1 IsNot Nothing AndAlso dt1.Rows.Count > 0 Then
                ds.Tables.Add(dt1)
            End If
            If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                ds.Tables.Add(dt2)
            End If
            'If dt3 IsNot Nothing AndAlso dt3.Rows.Count > 0 Then
            '    ds.Tables.Add(dt3)
            'End If



            '' Count the number of tables in the data set
            Dim dsCount As Integer = ds.Tables.Count
            Dim str As String = String.Empty
            Dim result As String = Dts.Variables("EmailMessage").Value.ToString()
            result = result & ControlChars.NewLine
            '' strBody and strBodyEnd adds the HTML tag information. 
            Dim strBody As String = "<html><body>" & result.ToString & "<br><br>"
            Dim strBodyEnd As String = "</table></body></html>"



            '' The following code captures the results of datatables and buils an html string to dispose as an email
            For x As Integer = 0 To dsCount - 1
                If ds.Tables(x) IsNot Nothing AndAlso ds.Tables(x).Rows.Count > 0 Then
                    Dim strText As String = DataTableToHTMLTable(ds.Tables(x))
                    If x = 0 Then
                        str += strText.ToString()
                    Else
                        If x > 0 Then
                            '' The result sets start with 0 to n-1 (0,1,2,3...Etc.)
                            '' The below statment replaces the captionname and adds a suffix, which is number of result set
                            '' For example, for the 2nd result, the caption would become "CaptionName1"
                            str += "<br>" & Replace(strText.ToString(), "Query Result Set", "Query Result Set" & x)
                        End If
                    End If
                Else
                    str += vbEmpty.ToString
                End If
            Next x



            Dim msg As String = str.ToString
            message = strBody & msg & strBodyEnd
            Mail()   'only email if not empty recordset
            Dts.TaskResult = ScriptResults.Success
        Catch e As Exception
            Dim LockedVariable As Variables = Nothing
            Dts.VariableDispenser.LockOneForWrite("User::ScriptTaskErrorMsg", LockedVariable)
            LockedVariable("User::ScriptTaskErrorMsg").Value = "Error Description: " + e.Message.ToString()
            LockedVariable.Unlock()
            Dts.Events.FireError(0, "Script Task", "Error", String.Empty, 0)
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub
    '' The following functions convert a SQL Result which is in the form of Datatable to HTMLTable
    '' Kindly modify the DataTableToHTMLTable background properties to suit the background and color formatting of the HTML Table
    '' Kindly modify the Caption information to reflect the correct caption neccessary in case of multiple query results
    Public Function DataTableToHTMLTable(ByVal inTable As DataTable) As String
        Dim dString As New StringBuilder
        dString.Append("<style scoped>.myTable { background-color:#FFFFE0;}.myTable th { background-color:#BDB76B;color:black; }.myTable td, .myTable th { padding:5px;border:1px solid #BDB76B; }</style>")
        dString.Append("<caption>Query Result Set</caption>")
        dString.Append("<table class=myTable> ")
        dString.Append(GetHeader(inTable))
        dString.Append(GetBody(inTable))
        dString.Append("</table>")
        Return dString.ToString
    End Function



    Private Function GetHeader(ByVal dTable As DataTable) As String
        Dim dString As New StringBuilder
        dString.Append("<thead><tr>")
        For Each dColumn As DataColumn In dTable.Columns
            dString.AppendFormat("<th>{0}</th>", dColumn.ColumnName)
        Next
        dString.Append("</tr></thead>")
        Return dString.ToString
    End Function



    Private Function GetBody(ByVal dTable As DataTable) As String
        Dim dString As New StringBuilder
        dString.Append("<tbody>")
        For Each dRow As DataRow In dTable.Rows
            dString.Append("<tr>")
            For dCount As Integer = 0 To dTable.Columns.Count - 1
                dString.AppendFormat("<td>{0}</td>", dRow(dCount))
            Next
            dString.Append("</tr>")
        Next
        dString.Append("</tbody>")
        Return dString.ToString()
    End Function
    Public Sub Mail()
        ''Variable Declaration
        Dim Mail As MailMessage
        Dim Client As SmtpClient
        Dim EmailFrom As String = Dts.Variables("EmailFrom").Value.ToString()
        Dim EmailTo As String = Dts.Variables("EmailTo").Value.ToString()
        Dim EmailSubject As String = Dts.Variables("EmailSubject").Value.ToString()
        Dim EmailCc As String = Dts.Variables("EmailCc").Value.ToString()
        Dim EmailBcc As String = Dts.Variables("EmailBcc").Value.ToString()
        Dim strPath As String = Dts.Variables("FilePath").Value.ToString()
        Dim EmailID As String = Dts.Variables("EmailID").Value.ToString()
        Dim Pswd As String = Dts.Variables("EmailPswd").Value.ToString()
        Dim From As New MailAddress(EmailFrom.ToString)
        Dim EmailPriority As MailPriority
        Dim FilesAttach As Attachment



        Mail = New MailMessage(From.ToString, EmailTo.ToString, EmailSubject.ToString, message)



        ''SMTP Client connection information
        Client = New SmtpClient("smtp.live.com")



        If EmailID = "" Then
            ''If an windows authentication is required then use DefaultNetworkCredentials (uncomment the below code)
            Client.Credentials = CredentialCache.DefaultNetworkCredentials
        Else
            ''If the username and password is requried then use NetworkCredentials 
            Client.Credentials = New System.Net.NetworkCredential(EmailID, Pswd)
            ''Provide Smtp Client Port Number 
            Client.Port = 587
            ''Enable SSL for Smtp Client 
            Client.EnableSsl = True
        End If



        ''The email is sent an an HTML.
        Mail.IsBodyHtml = True



        ''If CC and BCC is needed then uncomment the below
        'Mail.CC.Add(EmailCc)
        'Mail.CC.Add(EmailBcc)



        ''Set Priority of Email with the below (High, Low, Normal)
        'EmailPriority = Mail.Priority
        'Mail.Priority = MailPriority.High



        ''Attach all files under a single directory path
        'For Each PathDir As String In Directory.GetFiles(strPath)
        '    Dim Attach As New Net.Mail.Attachment(PathDir)
        '    Mail.Attachments.Add(Attach)
        'Next



        Client.Send(Mail)
    End Sub
#Region "ScriptResults declaration"
    'This enum provides a convenient shorthand within the scope of this class for setting the
    'result of the script.



    'This code was generated automatically.
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum



#End Region



End Class

23. Click Ok and Ok to close the Script task.

Explanation for major components of VB.Net Code

Convert Data Table to HTML Table

The following code is used to convert any SQL Server query result to HTML tabular format. The code is used from DataTableToHTMLTable and is formatted to fit the needs of this package.  We can provide background color, font and padding information in this code.

SQL Server Integration Services VB.Net Code

Main Component

In the main component, we first create a dataset to store any number of data tables. Each datatable is denoted as dt1, dt2…etc. We fill the Datatable (dt1) with the result set captured in the object variable query result. We can create multiple query result sets and store them in multiple object variables, which in turn can be stored in multiple data tables. The variable dsCount checks for the available number of tables. Variables result, strBody and strBodyEnd are used to store the start and end of the HTML body structure. The for loop code loops over the number of data tables present, checks to see there is any data in the data tables, if data exists, it calls the DataTableToHTMLTable function, constructs HTML string and assigns the value to strText variable.

For x As Integer = 0 To dsCount - 1
If ds.Tables(x) IsNot Nothing AndAlso ds.Tables(x).Rows.Count > 0 Then
Dim strText As String = DataTableToHTMLTable(ds.Tables(x))

The following code adds headings to each data table. In this context for data table one the heading is “Query Result Set”. For data table two the heading would be “Query Result Set1” and so on.

If x = 0 Then
str += strText.ToString()
Else
If x > 0 Then
'' The result sets start with 0 to n-1 (0,1,2,3...Etc.)
'' The below statement replaces the caption name and adds a suffix,
'' which is number of result set
'' For example, for the 2nd result, the caption would become "CaptionName1"
str += "<br>" & Replace(strText.ToString(), "Query Result Set", "Query Result Set" & x)
End If
End If

The PublicSubEmail component sends email to its recipients by using the values provided in the variables. By using EmailPriority we can set up if the email to be sent with High, Low or Normal priority. The code also provides a feature to attach all existing files from a single folder/directory location.

This marks the completion of the package. Provide values for the necessary variables such as EmailTo, EmailFrom and if Windows authentication is not used, to provide the email address and password to EmailID and EmailPswd respectively.

Multiple Query Result Set

Multiple Query Result Set Generated from SQL Server Integration Services

In order to get a single query result as output in email, comment out the extra data tables and fill only the desired data table. In the following screenshot example, all other data tables except dt1 have been commented. This way we will be able to achieve a single result set as an output.

ssis send email

Single Query Result Set

Single Query Result Set Generated from SQL Server Integration Services

Notes

  • The code has not been tested in previous versions (2008 and 2005).
  • The attachments section will attach all files in a specific folder regardless of extension (*.pdf, *.txt, *.doc, *.xls, *.xlsx, etc.).
  • If Windows authentication is not used, do provide EmailID (e.g. xyz@abc.com) and EmailPswd i.e. password for the email id.
  • Make sure to change the SMTP server information.
  • If Windows authentication is not used, make sure to change the SMTP port information and SMPT enable SSL information.
  • Comments in VB.Net code are marked by two single quotation marks.

References

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Sandeep Nallabelli Sandeep Nallabelli is a Business Intelligence Consultant with Microsoft Certified Solutions Associate (MCSA) and Microsoft Certified Solutions Expert (MCSE) 2012 BI certifications.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 15, 2017 - 9:50:31 AM - RJ Back To Top

 Hi Sandeep,

 

Thanks for sharing, between i'm encountered some error while trying to send the email.

So far i only full copied your code and paste into the script task, after that i enter the SMTP client, authentication and related information.

 

Your help is highly appreciated! 

 

This is the error message i got..

Error: A deadlock was detected while trying to lock variables "User::ScriptTaskErrorMsg" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out.

 

Thank you,

Best regards,

RJ

 

 

 

 


Friday, June 09, 2017 - 12:50:29 PM - Amber Back To Top

 

 Hello, I am havign some issue using your code. 

  Catch e As Exception
            Dim LockedVariable As Variables = Nothing
            Dts.VariableDispenser.LockOneForWrite("User::ScriptTaskErrorMsg", LockedVariable)
            LockedVariable("User::ScriptTaskErrorMsg").Value = "Error Description: " + e.Message.ToString()
            LockedVariable.Unlock()
            Dts.Events.FireError(0, "Script Task", "Error", String.Empty, 0)
            Dts.TaskResult = ScriptResults.Failure 

        End Try

The pacakage gives me an error that states deadlock was detected while trying to lock variables can you please help me to resolve this issue. 

 


Monday, March 13, 2017 - 5:25:35 AM - Mohammed Aala Back To Top

 Hi Sandeep,

nice article to implement such feature in SSIS. i've a small doubt as I want to display respective table name in the Query Result Caption. Please let me know how to display the table name for easy resultset.

 

For example;

Query Result Set: Temp1

 

Query Result Set1: Temp2

 


Tuesday, August 30, 2016 - 6:31:58 AM - Meari Gureisu Back To Top

 

 Hello, i tried your code however i encountered error on the script task, its failing. :(

 


Tuesday, June 07, 2016 - 8:34:01 PM - Pradeep Govindu Back To Top

 

 Nice tip Sandeep


Wednesday, June 01, 2016 - 12:13:19 PM - Jeremy Kadlec Back To Top

Sandeep,

Congrats on your first tip!  Welcome to the team!

Thank you,
Jeremy Kadlec
Community Co-Leader


Learn more about SQL Server tools