By: Sandeep Nallabelli | Updated: 2016-06-01 | Comments (10) | Related: More > Integration Services Development
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:
- 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.
- 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:
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:
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.
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.
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.
9. Open “SQL Query Result” Execute SQL Task and change the connection to “Target” and Click OK.
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
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
13. Upon completion of the above steps the package will be presented as follows:
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 |
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.
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.
18. Drag a Script task onto the Control Flow and add it to “SQL Query Result 1”. Rename the Script task to “Email”.
19. Double click to open the email Script task. Drop down the “ScriptLanguage” and select “Microsoft Visual Basic 2010”.
20. In the “ReadOnlyVariables” add the previous created variables as seen below.
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.
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
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.
Single Query Result Set
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. [email protected]) 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
- Review Arshad Ali's tip - Sending HTML formatted email in SQL Server using the SSIS Script Task to learn how to send HTML formatted emails from SSIS using the .Net capabilities of an SSIS package.
- Learn more about the SSIS Script Task.
- Check out all of the SSIS tips on MSSQLTips.com.
About the author
Sandeep Nallabelli is a Business Intelligence Consultant with Microsoft Certified Solutions Associate (MCSA) and Microsoft Certified Solutions Expert (MCSE) 2012 BI certifications. This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-06-01
