Databases
StackOverflow2013
Postsdbo
POSSIS Package execution succeed but no mail sent
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSSIS Package execution succeed but no mail sent
    primarykey
    data
    text
    <p>I have crated SSIS project which does the following thing</p> <p>Control Flow :-</p> <p><img src="https://i.stack.imgur.com/0B2d7.jpg" alt="enter image description here"></p> <p>Data Flow:- </p> <p><img src="https://i.stack.imgur.com/IL3rK.jpg" alt="enter image description here"></p> <ul> <li>It first delete data in excel sheet </li> <li>Create new excel sheet</li> <li>insert data from database to excel file</li> <li>Send mail of that excel file </li> </ul> <p>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.</p> <p><strong>Then Why Mail is not being sent by SQL Server Agent Job ?</strong></p> <p>SQL Job Runs in SQL Service Account so i given "Full Access" permission to my excel file for SQL Server Job Agent User.</p> <p><strong>No Error[With Warnings] As per SQL Agent Job But No Mail Being Sent</strong></p> <pre><code>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. </code></pre> <p>Email Script Code:-</p> <pre><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("sagar.dumbre@agsindia.com"); //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 &lt; sEmailTo.Length; ++i) { if (sEmailTo[i] != null &amp;&amp; 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; } } } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USShaggy
    1. USShaggy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSSIS Package execution succeed but no mail sent
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSSIS Package execution succeed but no mail sent
      1. USShaggy
      1. VTBountyStart
    3. VO
      singulars
      1. POSSIS Package execution succeed but no mail sent
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat is sending mail? The script task? If so, post the code.
      singulars
      1. POSSIS Package execution succeed but no mail sent
      1. USbrian
    2. COYes its a Script task
      singulars
      1. POSSIS Package execution succeed but no mail sent
      1. USShaggy
    3. COI think you'll have to add a custom handler to catch email errors. See the example at the bottom of http://msdn.microsoft.com/en-us/library/system.net.mail.smtpclient.aspx 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.
      singulars
      1. POSSIS Package execution succeed but no mail sent
      1. USbrian
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload