In an earlier post, I demonstrated how to send HTML formatted mail using the script task. Another frequently requested use of the mail task is to be able to send query results within the email message body. Here’s how…
Public Sub Main()
'Connection
Dim oConn As New Data.SqlClient.SqlConnection
oConn.ConnectionString = Dts.Connections("MyConnectionManager").ConnectionString
'Specify query to be run
Dim queryString As String = _
"SELECT TOP 10 TABLE_NAME, TABLE_SCHEMA, TABLE_TYPE FROM MyDatabase.INFORMATION_SCHEMA.TABLES"
'Create command to be issued
Dim oCommand As New SqlCommand(queryString, oConn)
'Open the connection
oConn.Open()
'Issue the command and return to a reader
Dim reader As SqlDataReader = oCommand.ExecuteReader()
'Variable to hold my text results
Dim MyText As String
'While there is stuff to read keep writing it to my variable
While reader.Read()
MyText = String.Concat(MyText, String.Format("{0},{1},{2}", reader(0), reader(1), reader(2)) & vbLf)
End While
MsgBox(MyText, MsgBoxStyle.Information, "My Results are:")
'Dts.Variables("myText").Value = MyText
'Close reader when done
reader.Close()
'Close connection
oConn.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
Some points about the code above:-
system.net.mail
classHappy days