Sending email in HTML format

SSIS and Import/Export
1

Hi

I've been following this guide to send email in a HTML format:

HTML email

When I get to this section:

HTMLforSSIS
HTMLforSSIS767×147 1.62 KB

I get error message:

Expression cannot be evaluated.
Attempt to parse the expression "

Testing

This is a test message.

" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a required element such as a parenthesis.

How do I use HTML in a variable "Value" field?

I am using VS 2010 shell, but imagine that should be able to use HTML perfectly fine.

Thanks

Andrew

2

What is the code you have written? Not much we can help with if we can't see the code or how you defined the variables.

3

The code in the script is as below:

  #Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Net.Mail
Imports System.Net
#End Region


Public Sub Main()

        Dim htmlMessageTo As String
        Dim htmlMessageFrom As String
        Dim htmlMessageSubject As String
        Dim htmlMessageBody As String
        Dim smtpServer As String


        htmlMessageTo = Dts.Variables("HtmlEmailTo").Value.ToString() << Fails here with

dtsruntime error The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

        htmlMessageFrom = Dts.Variables("HtmlEmailFrom").Value.ToString()
        htmlMessageSubject = Dts.Variables("HtmlEmailSubject").Value.ToString()
        htmlMessageBody = Dts.Variables("HtmlEmailBody").Value.ToString()
        smtpServer = Dts.Variables("HtmlEmailServer").Value.ToString()

        SendMailMessage(htmlMessageTo, htmlMessageFrom, htmlMessageSubject, htmlMessageBody, True, smtpServer)

        Dts.TaskResult = ScriptResults.Success
    End Sub


Private Sub SendMailMessage( _
      ByVal SendTo As String, ByVal From As String, _
      ByVal Subject As String, ByVal Body As String, _
      ByVal IsBodyHtml As Boolean, ByVal Server As String)

        Dim htmlMessage As MailMessage
        Dim mySmtpClient As SmtpClient

        htmlMessage = New MailMessage( _
          SendTo, From, Subject, Body)
        htmlMessage.IsBodyHtml = IsBodyHtml

        mySmtpClient = New SmtpClient(Server)
        mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
        mySmtpClient.Send(htmlMessage)

    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

    Private Sub MailMessage(htmlMessageTo As String, htmlMessageFrom As String, htmlMessageSubject As String, htmlMessageBody As String, p5 As Boolean, smtpServer As String)
        Throw New NotImplementedException
    End Sub

I thought it was maybe to do with not defining the variables in the readonly section the script task, but I put those in and still get the error:

User::HtmlEmailBody,User::HTMLEmailFrom,User::HtmlEmailServer,User::HTMLEmailSubject,User::HTMLEmailTo

Tried deleting and adding the script task and redefining the variables one by one and it still fails on that first section above. I've also tried putting in "User:" into "HtmlEmailTo" i.e. "User::HtmlEmailTo" and still fails.

Thanks

Andrew

4

You need to reference the DTS variable as:

htmlMessageTo = Dts.Variables["User::HtmlEmailTo"].Value.ToString();
5

I changed that, as mentioned at end of post, still does the same.

6

Okay - not sure what the problem could be then. How are these variables defined in the package?

7

@jeffw8713 they are defined at top level of the package, all as strings, with relevant values, and as read only variables in the script section. I tried one variable at a time and they all fail with the same runtime error.

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

Got another script very similar and checked all the settings, all exactly the same, and that one runs.

EDIT: i just found the issue, 2 of the variables were spelled HTMLEmailTo & HTMLEmailFrom instead of HtmlEmailFrom & HtmlEmailTo. Just goes to show, double check your spelling!

Once you change your variable names, you need to go back into the script task and reselect them from the list.

8

That would do it - the variable names are case-sensitive.