SSIS – Writing SQL results to a string variable

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…

Introduction to Internet Marketing; Search Engine Optimization, Adword Marketing, Email Promotion, and Affiliate Programs

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:-

  • I created a .NET connection to SQL Server in my SSIS package and used the connection string from that, however you could create the string using ConnectionStringBuilder class.
  • Rather than populate a SSIS variable in the code above I’ve just called a message box to demonstrate how the results might look.
  • I’ve queried INFORMATION_SCHEMA as all 2K5 servers have this. Naturally this won’t work if you’re querying something else
  • Finally, if you wanted to place these results in an email, you could use the SSIS variable that you write to in this script task, in an expression on the send mail task, or alternatively use the script in the previous post to format the results into HTML and send using system.net.mail class
  • Happy days

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    This site uses Akismet to reduce spam. Learn how your comment data is processed.