qbjgqbjg
asked on
ssis Send Mail Task
I have a package with 4 Execute sql tasks. I clicked eah one individually, then clicked event handlers and setup a Send Mail Task (for each one).using the OnError event handler. To test it, I renamed one of the tables so it would get an error. It got the error, but did not send the email. sendMail.docx
the error is in SEND MAIL TASK not execute sql task.
I think ssis can not connect to smtp connection. could you re-check it ?
I think ssis can not connect to smtp connection. could you re-check it ?
ASKER
I am checking with the person in IT that manages the servers. He gave me the Smtp connection.
could you connect to smtp server with outlook ?
Dear qbjgqbjg:
the normal SSIS send mail task is useless, it doesnt allow you to specify the credintials of the sender and this way the user that is running the SSIS package should always has a valid email and authenticated and authorised to send, and when you are running the ssis package using a sql job, the agent has to be run by an authorised domain user.
so that is why you need to use a SSIS script task As mentioned by MohammedU and below is a simple code
Public Sub Main()
Using reader As New StreamReader(response.GetR esponseStr eam())
Dim myAddress = "emailaddress@yourdomain.c om"
Dim myEmail As New MailMessage(myAddress, myAddress)
myEmail.Subject = "From SSIS using HTML"
myEmail.IsBodyHtml = True ' this value should be true
myEmail.Body = reader.ReadToEnd()
Dim smtp As New SmtpClient("smtp.yoursmtp. com", port) ' you need to mention the smtp port
smtp.Credentials = New NetworkCredential(myAddres s, "yourpassword")
smtp.EnableSsl = True
smtp.Send(myEmail)
End Using
Dts.TaskResult = ScriptResults.Success
End Sub
check the following article for more details
http://www.mssqltips.com/tip.asp?tip=1753
the normal SSIS send mail task is useless, it doesnt allow you to specify the credintials of the sender and this way the user that is running the SSIS package should always has a valid email and authenticated and authorised to send, and when you are running the ssis package using a sql job, the agent has to be run by an authorised domain user.
so that is why you need to use a SSIS script task As mentioned by MohammedU and below is a simple code
Public Sub Main()
Using reader As New StreamReader(response.GetR
Dim myAddress = "emailaddress@yourdomain.c
Dim myEmail As New MailMessage(myAddress, myAddress)
myEmail.Subject = "From SSIS using HTML"
myEmail.IsBodyHtml = True ' this value should be true
myEmail.Body = reader.ReadToEnd()
Dim smtp As New SmtpClient("smtp.yoursmtp.
smtp.Credentials = New NetworkCredential(myAddres
smtp.EnableSsl = True
smtp.Send(myEmail)
End Using
Dts.TaskResult = ScriptResults.Success
End Sub
check the following article for more details
http://www.mssqltips.com/tip.asp?tip=1753
@AmmarR:
Send Mail Task is not useless,
you can set sender configuration there
also you can set To or CC or BCC in configuration there.
but this does not means that this is static configuration.
you can simply use expression and there are ToLine, CCLine , BCCLine properties there which allows you to send mail to any address dynamically.
Am I understand your post correctly? If you mean something else explain it more.
Send Mail Task is not useless,
you can set sender configuration there
also you can set To or CC or BCC in configuration there.
but this does not means that this is static configuration.
you can simply use expression and there are ToLine, CCLine , BCCLine properties there which allows you to send mail to any address dynamically.
Am I understand your post correctly? If you mean something else explain it more.
ASKER
I have never used VB at all. I still am waiting for our server guy to get back to me. I think the problem is with the authentication. We use Windows authenication on a lot of things but there is a firewall that may be affecting it. I might have to learn enough VB to use the script.
See if your mail admin will allow relaying from that server without SMTP authentication, if you want to avoid code. It's not that most secure solution, but it is an alternative.
you can simply check the connection with outlook,
try to connect to server with outlook and send a dummy mail with credential provided
try to connect to server with outlook and send a dummy mail with credential provided
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all of the suggestions.
ASKER
This is the error.