May 6, 2008 at 8:51 am
I want to add a send mail task to the end of a package I have running on a remote IIS6 based webserver to let me know if it has failed or not.
When I add a Send Main task I get the following error :
Error: 0xC002F304 at Send Mail Task, Send Mail Task: An error occurred with the following error message: "Mailbox unavailable. The server response was: 5.7.1 Unable to relay for xxx@domain.com".
Task failed: Send Mail Task
I presume it's to do with local SMTP server settings but can't work out what to change. Also don't want to leave it open for spammers etc.
Many Thanks.
Chris.
May 7, 2008 at 12:49 am
Hi,
we can send out a mail using the built in SP for SQL 2005. The syntax for the same is as follows
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile',
@recipients = 'Sender email Id',
@query = 'Query to retreive errors records from table',
@subject = 'Subject Line' ;
We need to create profile, which will have the necessary information of SMTP and other things.
If you are scheduling your jobs, create one more steps and put this code on error.
I hope this works for you.
Regards,
Naveen Kumar M
May 23, 2008 at 12:00 am
If you have database mail configured, this would mean that the SMTP is running fine right?
So in order for you to use the send mail task in SSIS, you would need a SMTP connection.
Your SMTP connection you could set in a package configuration, which would point to your mail connection's connectionstring.
My connectionstring:
SmtpServer={SMTPServerName};UseWindowsAuthentication=True;EnableSsl=False;
If you right click on the database mail in SSMS, have a look at what the SMTP server name is.
That should solve your problems
May 25, 2008 at 9:23 pm
Hi,
I am also having a problem regarding smtp_sendmail. I am using trigger to send mails to inform administrator of a new record added into database. However, I only managed to send mails to my own domain.
I found out from my administrator that we are using external SMTP server. However, the stored procedure xp_smtp_sendmail does not contain any parameter which allows me to include my external SMTP server address. It seems like the parameter @server is only workable with my own domain IP address.
My questions are:
1. Will using SMTP Mail Task be suitable because I require authentication as well (can I put my external SMTP server address in the backup server area)?
2. Is this SMTP Mail Task reliable? Because in my research, I see a lot of complaints about this SMTP Mail Task not working.
May 26, 2008 at 12:35 am
Within the send mail task, there is a Windows Authentication, or a SQL Server Authentication.
Does your SQL server account have access to sending mails on the external SMTP server?
~PD
May 26, 2008 at 3:09 am
Hi,
Thanks for your reply. 🙂
Yup. The smtp server is provided for us to send smtp mails. But they require us to have: external server's IP address, the sender's IP Address (to verify with our record), and authentication fields (user id and password).
I am very interested to know if SMTP Mail Task allows me to define that there is my domain IP address (current server the sql is running on) and an external smtp server IP address to route to. Is it the backup server feature that support such case?
May 26, 2008 at 5:04 am
I am sure it is possible, but I havent really worked with external SMTP credentials in SSIS (4th week and getting more confident as time goes on...).
I would configure as following:
a) Set up a SMTP connection in my connection manager. In here, I would specify the SMTP server (the one that is sending the mail). For arguments sake, lets call it LocalSMTP1
I would select Windows Authentication
b) On my control flow task, I would create a send mail task.
On the Mail section, I would select the SMTP connection (LocalSMTP1) as my SMTP connection
On the From, I would specify the mail address of the mailbox that was created on the server (in our case, a resource mailbox was created)
The rest is pretty self explanatory (fill in the To section, Subject section possibly Message source and Attachments)
c) I would add a variable which would hold my SMTP connection string. While testing, I would set this to my local SMTP connection string.
On LocalSMTP1, I would add an expression to the connection string property, and then set this equal to my variable
d) I would add a variable which would hold my from email address. While testing, I would set this to my email address.
On LocalSMTP1, I would add an expression to the From property, and then set this equal to my variable
e) I would add a package configuration for each of the variables (c and d)
f) After deploying the package to the target server, I would change the variable values to point to the server SMTP mailbox as well as From address
The one and only requirement is that your SQL server agent account should have rights to send mail from this mailbox, and this is best tested by configuring database mail on your target server (What I did is to create a job which gaurantees failure upon which it mailed an error mail to me, and until this job does not mail you messages, you know that there is some kind of problem with your SMTP configuration, simple enough test, practical enough to configure your environment).
I hope that this helps you getting send mail tasks going.
~PD
May 27, 2008 at 2:14 am
Hey,
I found a posting on Conchango explaining when the mail task simply wouldnt suffice, with a valid alternative.
Hope this helps
~PD
May 28, 2008 at 3:44 am
Hi.
Thanks for your effort in trying to help me. But the link you gave me seems a little irrelevant. I am using sql server's trigger to send email. So... I am not very sure about the link you showed me.
Do you happen to know if I can just use the external server's IP address for the @server field?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply