May 3, 2011 at 2:34 pm
i am trying to send email from script task using ssis sql configuration table.
i have created sql table with configuration detail.
i have 4 variable created at the package level
mailto, mailfrom,smtpserver,mailmessage - all are string
in the script task properites i have made mailto,mailfrom,smtpserver as readonly
mailmessage as readwrite
Dim varHTMLMail As MailMessage
Dim varSMTPClient As SmtpClient
Dim varMailBody As string
Dim varAddresses As String
varMailBody = Dts.Variables("MailMessage").Value.ToString
varAddresses = Dts.Variables("MailTo").Value.ToString
varHTMLMail = New MailMessage(Dts.Variables("MailFrom").Value.ToString, varAddresses, "Daily Order Summary", CStr(varMailBody))
varSMTPClient = New SmtpClient(Dts.Variables("SmtpServer").Value.ToString)
varSMTPClient.Credentials = New NetworkCredential("ID", "pwd", "Domain")
varSMTPClient.Send(varHTMLMail)
All the variables comes ok from the table and the task shows success (green) but no email is generated
May 3, 2011 at 2:50 pm
Have you verified that the smtp server is accepting mail from your server? It might be as simple as checking the smtp server configuration and values in your variables.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 4, 2011 at 8:18 am
I checked all the variables value and it comes correct and without variable it works fine.
Not sure what is causing this
June 5, 2011 at 4:30 pm
I had a problem with this a while back.
Only thing I can suggest trying is seeing if your email server allow relaying or not. SSIS seemed to tell fibs occasionally saying an email send was successful when it strictly wasn't true. Alternatively, the email could have been created and sent but blocked internally as somewhere down the line it was considered as spam.
June 6, 2011 at 7:46 am
This works for me:
Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net
Public Sub Main()
Dim myHtmlMessage As New MailMessage()
Dim mySmtpClient As SmtpClient
Dim theMessage As String
theMessage = "Hello, it is now " & Now().ToString & "."
myHtmlMessage.From = New MailAddress("fl@yourschool.edu", "First Last")
myHtmlMessage.To.Add(New MailAddress("fl@yourschool.edu", "First Last"))
myHtmlMessage.Subject = "Tell you the time..."
myHtmlMessage.Body = theMessage
mySmtpClient = New SmtpClient("mail.yourschool.edu")
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
Try
mySmtpClient.Send(myHtmlMessage)
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply