Post reply

Unable to programmatically set the value for Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailMessageSourceType

  • April 29, 2011 at 2:11 pm

    #249490

    I am writing a VB script for Script Task that will find out if there is any bad/unrecognized data. If found, it will programmatically concatenate those values into in one string and put it on the e-mail content and send out the e-mail alert. Everything looks find in the code until I did the following line in the code...

    set .Properties("MessageSourceType").SetValue(thSendMailTask, "Variable")

    Then, it gives me the error below:

    Object of type 'System.String' cannot be converted to type 'Microsoft.SqlServer.Dts.Tasks.SendMailTask.SendMailMessageSourceType'.

    at System.RuntimeType.CheckValue(Object value, Binder binder, CultureInfo culture, BindingFlags invokeAttr)...

    UnrecognizedOrgNames is the variable that contains string value to be put in the e-mail content. I am basically trying to programmatically set MessageSourceType to "Variable" that takes the value from UnrecognizedOrgNames variable. There seems to be data type conflict, but I don’t know what to do with it.

    Here is the entire script per your review. What am I doing wrong here? Please help. Thank you!

    Imports System

    Imports System.Data

    Imports System.Math

    Imports System.Xml

    Imports System.Data.OleDb

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports Microsoft.SqlServer.Dts.Tasks.SendMailTask

    Public Class ScriptMain

    Public Sub Main()

    Dim oleDA As New OleDb.OleDbDataAdapter

    Dim dt As New DataTable

    Dim col As DataColumn

    Dim row As DataRow

    Dim sMsg As String

    Dim sMsg2 As String

    Dim pkg As New Package

    Dim smtpCM As ConnectionManager

    smtpCM = pkg.Connections.Add("SMTP")

    smtpCM.Name = "AHSS email"

    smtpCM.ConnectionString = "smtp.ahss.org"

    Dim exe As Executable = pkg.Executables.Add("STOCK:SendMailTask")

    Dim thSendMailTask As TaskHost = CType(exe, TaskHost)

    oleDA.Fill(dt, Dts.Variables("UnrecognizedOrg").Value)

    sMsg = ""

    sMsg2 = ""

    For Each row In dt.Rows

    For Each col In dt.Columns

    sMsg = sMsg & col.ColumnName & _

    row(col.Ordinal).ToString & vbCrLf

    Next

    sMsg2 = sMsg & ", " & sMsg2

    Dts.Variables("UnrecognizedOrgNames").Value = sMsg2

    sMsg = ""

    Next

    Dts.TaskResult = Dts.Results.Success

    With thSendMailTask

    .Properties("SmtpConnection").SetValue(thSendMailTask, "AHSS email")

    .Properties("ToLine").SetValue(thSendMailTask, "monthep.hongsyok@ahss.org")

    .Properties("FromLine").SetValue(thSendMailTask, "sqlservice@ahss.org")

    .Properties("Subject").SetValue(thSendMailTask, "Unrecognized hospital name(s) found in HPM file")

    .Properties("MessageSourceType").SetValue(thSendMailTask, "Variable")

    .Properties("MessageSource").SetValue(thSendMailTask, Dts.Variables("UnrecognizedOrgNames").Value)

    End With

    Dim valResults As DTSExecResult = pkg.Validate(pkg.Connections, pkg.Variables, Nothing, Nothing)

    If sMsg2 <> "" Then

    pkg.Execute()

    End If

    End Sub

    End Class

  • May 6, 2011 at 5:43 am

    #1320922

    You are not doing it correctly. Review the sample here and specifically how MessageSourceType property is being set.

    This code will require including reference to microsoft.sqlserver.sendmailtask.dll assembly.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Viewing 2 posts - 1 through 2 (of 2 total)

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