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…

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 Cancel reply