SQL Agent Job working but no email
Hi, I’ve created a SSIS package to retrieves data from a SQL table, write to a flat file and then email the flat file. To send the email I used a script task and System.Net.Mail.SmtpClient class. Package is working fine. It creates the flat file and emails it too. I wanted this package to run daily at a schedule time. So I created a SQL job for the package with a proxy account. I see that the job is executing fine. No errors. I see the flat file too is being created by the package. But I’m not receiving the email. I’ve enabled logging. The log does not show any errors. When I manually execute the package (from SQL server management studio integration services), it works perfectly fine. I receive the email too. Please anyone let me know what I'm doing wrong here. Thank you & Regards, -Jinantha
January 21st, 2011 11:36pm

Hello Jinantha, I suspect this is the proxy account issue. Specifically the rights to send email using the Exchange by this account. I had a similar issue and was able to resolve it by first contacting the Exchange support which confirmed that they saw an error in their logs on the email being rejected due to some kind of a rule. Once the rule was tweaked the emails started to work.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2011 3:11pm

Do you see entry in this for failed email? SELECT * FROM msdb.dbo.sysmail_faileditemshttp://sqlworkday.blogspot.com/
January 24th, 2011 3:51pm

Hi Arthur, 1. Are you saying it's something to do with MS Exchange server? If yes, then I don’t have an Exchange server in our network. We use a 3rd party email service outside of our network. 2. Furthermore you are saying it's a Proxy account issue. May I know how you create your proxy account and what setting you have put (if you can remember)? 3. Do I need to configure SQL Email for this first? Thank you & Regards, -Jinantha
Free Windows Admin Tool Kit Click here and download it now
January 24th, 2011 10:36pm

Hi Yogesh, No, there seems to be nothing in the table. Thank you & Regards, -Jinantha
January 24th, 2011 10:38pm

1. Are you saying it's something to do with MS Exchange server? If yes, then I don’t have an Exchange server in our network. We use a 3rd party email service outside of our network. 2. Furthermore you are saying it's a Proxy account issue. May I know how you create your proxy account and what setting you have put (if you can remember)? 3. Do I need to configure SQL Email for this first? Jinantha: To verify all the points, my idea is to try sending your email using the account in question using the command line e.g. with a properly crafted VBScript (or nay command line tool). Check for errors. Get back to us.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 25th, 2011 4:44pm

I don't think you are using DB mail so you can skip the checks on the SQL. Do you have access to the smtp server you are using? In that case, check the outbound and error log folder of the SMTP server. In most cases, (as in Arthur's case) your proxy account will not have permissions to send mails which will be a definite entry in the error log of the SMTP server.Rakesh Parida | MSFT
January 25th, 2011 9:02pm

Hi, We can also use SSIS Send Mail task control for sending emails, we only have to provide SMTP address or IP When the job is executed it will send email Either the proxy account don't have permission for sending emails Thanks, Shobhit
Free Windows Admin Tool Kit Click here and download it now
January 25th, 2011 11:07pm

Hello Jinantha, sure I will help you with the script or the command line to test the account, but first I would like to know what emailing system you interface (if it is SMTP it will be one technique, otherwise another).Arthur My Blog
January 26th, 2011 7:44pm

Hi Shobhit, The thing is SSIS Send Mail task do not support my SMTP server. Thanks you & Regards, -Jinantha
Free Windows Admin Tool Kit Click here and download it now
January 26th, 2011 8:18pm

Hi Rakesh, Yes I do not use DB mail. But sadly I don’t have access to the SMTP server. It's managed by another company. But I wonder, how come a proxy account (SQL Server account I believe) have an effect on SMTP authentication and permission? Thank you & Regards, -Jinantha
January 26th, 2011 8:22pm

Hi Arthur, Can I have more details how I can do this? I have never done something like this before. Thank you & Regards, -Jinantha
Free Windows Admin Tool Kit Click here and download it now
January 26th, 2011 8:24pm

Hi Arthur, Great! Yes it is a SMTP. It's MDaemon Email Server from http://www.altn.com/ Thank you & Regards, -Jinantha
January 26th, 2011 9:06pm

Hi Arthur, Great! Yes it is a SMTP. It's MDaemon Email Server from http://www.altn.com/ Thank you & Regards, -Jinantha
Free Windows Admin Tool Kit Click here and download it now
January 26th, 2011 9:06pm

Awesome, The code is for a text file with extension .vbs here is option 1 if you are on the box where SMTP service is installed: Set objEmail = CreateObject("CDO.Message") objEmail.From = "accountgoeshere@xyz.com" objEmail.To = "Jinantha@xyz.com" objEmail.Subject = "EMAIL TEST - PLEASE IGNORE" objEmail.Textbody = "THIS IS A TEST EMAIL." objEmail.Send Option 2 if the SMTP service is not installed: Set objEmail = CreateObject("CDO.Message") objEmail.From = "accountnamegoeshere@xyz.com" objEmail.To = "Jinantha@xyz.com" objEmail.Subject = "TEST EMAIL - PLEASE IGNORE" objEmail.Textbody = "THIS IS A TEST EMAIL" objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _ "theSMTPProviderGoesHere" objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 objEmail.Configuration.Fields.Update objEmail.Send theSMTPProviderGoesHere - needs to be replaced with the name of your server. Give it a try using a command line and get back to us on what you got. Arthur My Blog
January 26th, 2011 9:59pm

Awesome, The code is for a text file with extension .vbs here is option 1 if you are on the box where SMTP service is installed: Set objEmail = CreateObject("CDO.Message") objEmail.From = "accountgoeshere@xyz.com" objEmail.To = "Jinantha@xyz.com" objEmail.Subject = "EMAIL TEST - PLEASE IGNORE" objEmail.Textbody = "THIS IS A TEST EMAIL." objEmail.Send Option 2 if the SMTP service is not installed: Set objEmail = CreateObject("CDO.Message") objEmail.From = "accountnamegoeshere@xyz.com" objEmail.To = "Jinantha@xyz.com" objEmail.Subject = "TEST EMAIL - PLEASE IGNORE" objEmail.Textbody = "THIS IS A TEST EMAIL" objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _ "theSMTPProviderGoesHere" objEmail.Configuration.Fields.Item _ ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 objEmail.Configuration.Fields.Update objEmail.Send theSMTPProviderGoesHere - needs to be replaced with the name of your server. Give it a try using a command line and get back to us on what you got. Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 26th, 2011 9:59pm

Hi Arthur, I had to add the following lines of code. Without them it didn't work. Do I need to login as a different account? I executed this script in admin login session. Do I need to login as a different user to windows? objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email_login@domain.com" objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "email_password" Thank you & Regards, -Jinantha
January 26th, 2011 11:33pm

Hi Jinantha, based on what you tell me, your SMTP solution is not integrated with the Windows security. Thus, to resolve this situation you may need to revise your package to not to use the Send Mail Task, but rather the just crafted script. Nonetheless, just to confirm my theory you can simply execute this script using the RUNAS command like Runas /user:MY_DOMAIN\jDoe "SendTestEmail.vbs" using the account you are using to run your package under. If that account fails to send the email then you know why.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
January 26th, 2011 11:53pm

Hi Arthur, I was not successful in using the Runas command to execute the vbscript. Anyway I was able to resolve this issue by using the MS SMTP service. I've configured that to my 3rd party SMTP service. And form the SSIS package sending directly to local SMTP service and it routes my emails perfectly. Local SMTP service and SSIS package I have configured the windows integration authentication. May be as you said, it might be a case of permission with the account I've been using. But I was unable to find whether it was that case. My current method is working perfectly fine and I think it is the best as the local SMTP queues and forwards the emails. I wish to thank you and all in the thread who gave there assistance to me in-order to resolve this issue. Thank you & Regards, -Jinantha
January 30th, 2011 10:20pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics