SSIS Package execution succeed but no mail sent

Question

I have crated SSIS project which does the following thing

Control Flow :-

Data Flow:-

• 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;
            }
        }
    }
}

Answer 1

Based on this:

Sir, I executed my package on Server itself it works well . mail is being sent but when i configure package (.dtsx) in sql server job then only mail sent fails, all the remaining three steps succeed.

I can't comment at this time because StackOverflow doesn't allow me, so don't take this as an answer, so much as a verification based on your comment above this. Have you configured the values when you select SSIS package to match how you're running on your server? I run into trouble when I move from the server to the job agent if I don't configure it exactly as is (especially, when I use a config file or set the connection strings differently). Note that the trouble here will still run the package with no error, yet not do what it's supposed to do.

Outside of the above paragraph, your code and steps look good and if it were bad, your package would be breaking on the server itself; so that eliminates some of the possible issues.

Answer 2

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

My top N reasons it's not working

1. 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.

2. 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.

3. 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.

4. 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.