Post reply

Dynamic message boby generation in Script Task

  • August 12, 2008 at 11:42 pm

    #127096

    Hi,

    Here I need to change the body of the email sent to result sets of the SQL query I am running. Can you please anyone help me? Thanks. By the way, I am using below script in my SSIS.

    Public Sub Main()

    Dim myHtmlMessage As MailMessage

    Dim mySmtpClient As SmtpClient

    Dim oCnn As New Data.SqlClient.SqlConnection

    Dim oCmd As SqlCommand = New SqlCommand

    oCnn.ConnectionString = "Data Source=Server-1;Initial Catalog=db_WORK;Integrated Security=SSPI;"

    oCmd.CommandText = "Select Top 100 Field1 From Table1"

    oCmd.Connection = oCnn

    oCnn.Open()

    oCmd.ExecuteReader()

    'Dim myHTMLBody As SqlDataReader = oCmd.ExecuteReader()

    myHtmlMessage = New MailMessage("Mohan.Mariyappa@mailserver", "Mohan.Mariyappa@mailserver", "SampleSubject", "Body")

    mySmtpClient = New SmtpClient("MailServer")

    mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials

    mySmtpClient.Send(myHtmlMessage)

    'oCnn.Close()

    Dts.TaskResult = Dts.Results.Success

    End Sub

  • August 13, 2008 at 5:20 am

    #857295

    i think if you are doing it this way you will need to use the datareader to loop through the recordset and populate a text variable with the rows (using string.concatenate not the & operand).

    an alternative might be to use a dataflow task to send the recordset to a flat file and then use a send mail task to send the email with the flat file as an attachment, or even just a link to the file, if the file size is over a certain threshold (you can control this using expressions in the send mail task).

    tom

    Life: it twists and turns like a twisty turny thing

  • August 13, 2008 at 4:31 pm

    #857773

    Thanks. Is there any code sample or any article which can help me develop this? Because I am new to .Net programming. Thanks

  • August 14, 2008 at 2:49 am

    #857893

    this gives a basic example of how to use the datareader, plus has links to its properties

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

    Life: it twists and turns like a twisty turny thing

  • August 14, 2008 at 4:55 pm

    #858496

    Thanks a lot for your help. Much appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply