SSIS Package execution succeed but no mail sent

Question

I have crated SSIS project which does the following thing

Control Flow :-

enter image description here

Data Flow:-

enter image description here

It first delete data in excel sheet
Create new excel sheet
insert data from database to excel file
Send mail of that excel file

When i execute it by right click on package and say execute it works well(mail sent). But when i schedule the package in Sql Server Agent job to run it shows me "Package execution succeed" but no mail is sent. though it is able to insert data into excel sheet.

Then Why Mail is not being sent by SQL Server Agent Job ?

SQL Job Runs in SQL Service Account so i given "Full Access" permission to my excel file for SQL Server Job Agent User.

No Error[With Warnings] As per SQL Agent Job But No Mail Being Sent

The package execution returned DTSER_SUCCESS (0) but had warnings, with warnings being treated as errors.  Started:  4:16:51 PM  Finished: 4:17:04 PM  Elapsed:  13.119 seconds.  The command line parameters are invalid.  The step failed.

Email Script Code:-

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;
using System.Text.RegularExpressions;

namespace ST_cb3e2bf527bb45c58359315bb058656e.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion    

        public void Main()
        {
            string sSubject = "Monitum : ICICI Cash Balance : "+DateTime.Now.ToShortDateString()+" : "+DateTime.Now.ToShortTimeString();
            string sBody = "";
            int iPriority = 2;

            if (SendMail(sSubject, sBody, iPriority))
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                //Fails the Task
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        public bool SendMail(string sSubject, string sMessage, int iPriority)
        {
            try
            {
                string sEmailServer = Dts.Variables["User::sEmailServer"].Value.ToString();                
                string sEmailPort = Dts.Variables["User::sEmailPort"].Value.ToString();                
                string sEmailUser = Dts.Variables["User::sEmailUser"].Value.ToString();                
                string sEmailPassword = Dts.Variables["User::sEmailPassword"].Value.ToString();                
                string sEmailSendTo = Dts.Variables["User::sEmailSendTo"].Value.ToString();                
                string sEmailSendFrom = Dts.Variables["User::sEmailSendFrom"].Value.ToString();                
                string sEmailSendFromName = Dts.Variables["User::sEmailSendFromName"].Value.ToString();                

                SmtpClient smtpClient = new SmtpClient();
                MailMessage message = new MailMessage();
                Attachment attach = new Attachment("C:\\Users\\Administrator\\Documents\\ICICI Cash Balance.xls");
                attach.Name = "ICICI_Cash_Balance_"+DateTime.Now.ToLongDateString()+"_"+DateTime.Now.ToLongTimeString()+".xls";
                message.Attachments.Add(attach);
                MailAddress fromAddress = new MailAddress(sEmailSendFrom, sEmailSendFromName);
                message.Bcc.Add("[email protected]");
                //You can have multiple emails separated by ;
                string[] sEmailTo = Regex.Split(sEmailSendTo, ";");
                //string[] sEmailCC = Regex.Split(sEmailSendCC, ";");
                int sEmailServerSMTP = int.Parse(sEmailPort);

                smtpClient.Host = sEmailServer;
                smtpClient.Port = sEmailServerSMTP;

                System.Net.NetworkCredential myCredentials =
                   new System.Net.NetworkCredential(sEmailUser, sEmailPassword);
                smtpClient.Credentials = myCredentials;

                message.From = fromAddress;

                if (sEmailTo != null)
                {
                    for (int i = 0; i < sEmailTo.Length; ++i)
                    {
                        if (sEmailTo[i] != null && sEmailTo[i] != "")
                        {
                            message.To.Add(sEmailTo[i]);
                        }
                    }
                }
                switch (iPriority)
                {
                    case 1:
                        message.Priority = MailPriority.High;
                        break;
                    case 3:
                        message.Priority = MailPriority.Low;
                        break;
                    default:
                        message.Priority = MailPriority.Normal;
                        break;
                }

                message.Subject = sSubject;
                message.IsBodyHtml = true;
                message.Body = sMessage;

                smtpClient.Send(message);
                return true;
            }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, "Script Task Example", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);
                return false;
            }
        }
    }
}

What is sending mail? The script task? If so, post the code. — brian
I think you'll have to add a custom handler to catch email errors. See the example at the bottom of msdn.microsoft.com/en-us/library/… on how MS recommends catching smtpClient errors. Also, use the Log Provider and Dts.Events.FireError method in script task error catching. A messagebox.show doesn't leave much to research after you automate the job in the agent. — brian
Where to insert "Log Provider and Dts.Events.FireError method" in Script Task ? — Shaggy
@brian I have modified my Catch block but no error is getting catched. Where to check it though ? — Shaggy

billinkc billinkc · Accepted Answer · 2012-12-18T18:07:14

I'm feeling lucky so I'll post without all the details.

My top N reasons it's not working

The SQL Server Agent account is not allowed to talk to Exchange. Domain\SqlServiceAccount is not a user in Exchange and therefore unable to send mail. Domain\SagarDumbre is a user in Exchange and can send mail which is why it works for you.
The server is not authorized to talk to the Exchange. Your Exchange admin has the ability to authorize IP addresses and even though the account that runs SQL Server can talk to Exchange, the address 192.168.1.101 is not allowed to talk to the mail server. We got bit by this during and Exchange upgrade/maintenance. The admins turned on the feature to prevent spam from being sent from non-authorized addresses and servers weren't on the list.
Firewall and/or virus scanner. Since you didn't specify whether the successful send of email works from your machine or the server in question, I have also seen these products block access to a mail server since the requests weren't coming from Outlook.
Bad code. Something in your mail sending code is failing or it is receiving an error message from the MTA saying it couldn't handle your request and the code isn't listening or who knows what. Fire off some Information events so you can get feedback from the Script Task.

Talk to your Exchange admin and see if they have details in their log of what is happening. They might want to watch their interface as the package fires to capture the appropriate event.

SSIS Package execution succeed but no mail sent

3 Answers