SSIS 包执行成功但未发送邮件 [英] SSIS Package execution succeed but no mail sent
问题描述
我已经创建了 SSIS 项目，它执行以下操作
I have crated SSIS project which does the following thing
控制流:-
数据流:-
- 首先删除excel表中的数据
- 创建新的 Excel 表格
- 将数据从数据库插入到excel文件
- 发送那个excel文件的邮件
当我通过右键单击包执行它并说执行它时效果很好(已发送邮件).但是当我在 Sql Server 代理作业中安排包运行时，它显示包执行成功"但没有发送邮件.虽然它能够将数据插入到excel表中.
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.
那么为什么 SQL Server 代理作业没有发送邮件?
SQL 作业在 SQL 服务帐户中运行，因此我为 SQL Server 作业代理用户授予了对我的 excel 文件的完全访问"权限.
SQL Job Runs in SQL Service Account so i given "Full Access" permission to my excel file for SQL Server Job Agent User.
没有错误[有警告]根据 SQL 代理作业但没有发送邮件
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.
电子邮件脚本代码:-
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 < 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;
}
}
}
}
推荐答案
我很幸运，所以我会在没有所有细节的情况下发帖.
I'm feeling lucky so I'll post without all the details.
它不起作用的前 N 个原因
My top N reasons it's not working
不允许 SQL Server 代理帐户与 Exchange 通信.Domain\SqlServiceAccount 不是 Exchange 中的用户，因此无法发送邮件.Domain\SagarDumbre 是 Exchange 中的用户，可以发送邮件，这就是它对您有用的原因.
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.
服务器无权与 Exchange 对话.您的 Exchange 管理员可以授权 IP 地址，即使运行 SQL Server 的帐户可以与 Exchange 通信，也不允许地址 192.168.1.101 与邮件服务器通信.我们在 Exchange 升级/维护期间受到了影响.管理员启用了该功能以防止从未经授权的地址发送垃圾邮件，而服务器不在列表中.
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.
防火墙和/或病毒扫描程序.由于您没有指定成功发送电子邮件是从您的机器还是相关服务器，我还看到这些产品阻止了对邮件服务器的访问，因为请求不是来自 Outlook.
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.
错误的代码.您的邮件发送代码中的某些内容出现故障，或者它从 MTA 收到一条错误消息，指出它无法处理您的请求并且代码未在侦听或谁知道是什么.触发一些信息事件，以便您可以从脚本任务中获得反馈.
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.
与您的 Exchange 管理员交谈，看看他们的日志中是否有关于正在发生的事情的详细信息.他们可能希望在程序包触发时观察他们的界面以捕获适当的事件.
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 包执行成功但未发送邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
