每日检查SQL Server 2012数据库SSIS运行状态代码,CSharp代码库,德仔网

代码语言

代码分类

【CSharp】每日检查SQL Server 2012数据库SSIS运行状态

作者:pan_yz / 发布于2015/3/23/ 1191

每日早上8点定时检查SQL Server 2012数据库SSIS运行状态，并发送邮件给客户和IT，邮件模版需要另存为oft格式（Outlook模版格式），放在程序工作目录下以供程序使用，内容中的日期可以用{0}代替。

如果数据库暂时无法连通，则程序会隔1分钟再次尝试连接数据库。


	using System;
	using System.ComponentModel;
	using System.IO;
	using System.Windows.Forms;
	
	namespace CheckDatabaseJob
	{
	 public partial class Form1 : Form
	 {
	 public Form1()
	 {
	 InitializeComponent();
	 }
	
	 private readonly string emailTemplatePath = Environment.CurrentDirectory + @"\通知邮件.oft";
	
	 private BackgroundWorker checkSQLServerStatusWorker = new BackgroundWorker();
	
	 private readonly string formOriginalTitle = "每日ERP数据库的SSIS包运行状态检查";
	
	 /// <summary>
	 /// 每日发送邮件的整点
	 /// </summary>
	 private readonly int clock = 8;
	
	 /// <summary>
	 /// 连接字符串
	 /// </summary>
	 private readonly string connectionString = @"Data Source=(local);Integrated Security=True;";
	
	 private string formTitle;
	
	 public bool forceStart = false;
	
	 private void CheckERPJobStatusComplete(object sender, RunWorkerCompletedEventArgs eventArgs)
	 {
	 this.Text = formTitle;
	 }
	
	 /// <summary>
	 /// 检查ERP数据库的SSIS包的运行状态，并在失败或正在运行时发送邮件给客户
	 /// </summary>
	 private void CheckERPJobStatusAndSendEmail(object sender, DoWorkEventArgs eventArgs)
	 {
	 try
	 {
	 SMO smo = new SMO(connectionString);
	 smo.JobName = "ERP_ETL";
	 DateTime dt = smo.ServerJobServerJobLastRunDate;
	 CompletionResult result = smo.ServerJobServerJobLastRunOutcome;
	 if (!dt.Date.Equals(DateTime.Today) || result != CompletionResult.Succeeded)
	 SendNotificationEmail();
	 if (forceStart)
	 forceStart = false;
	 formTitle = formOriginalTitle;
	 }
	 catch (Exception ex)
	 {
	 formTitle = formOriginalTitle + " - " + ex.Message + " - " + DateTime.Now.ToString("yyyy/M/dd hh:mm:ss");
	 forceStart = true;
	 timer2.Interval = 60000;
	 timer2.Enabled = true;
	 }
	 }
	
	 /// <summary>
	 /// 发送通知邮件
	 /// </summary>
	 private void SendNotificationEmail()
	 {
	 var app = new Microsoft.Office.Interop.Outlook.Application();
	 var mail = app.CreateItemFromTemplate(emailTemplatePath);
	 mail.HTMLBody = mail.HTMLBody.ToString().Replace("{0}", DateTime.Today.ToString("yyyy.M.dd"));
	 mail.Send();
	 }
	
	 private void chkERPJobStatusAndSendEmail_CheckedChanged(object sender, EventArgs e)
	 {
	 if (chkERPJobStatusAndSendEmail.Checked)
	 {
	 if (DateTime.Now.Minute == 0)
	 timer1.Enabled = true;
	 else
	 timer2.Interval = ((60 - DateTime.Now.Minute) * 60 - DateTime.Now.Second) * 1000;
	 }
	 else
	 timer1.Enabled = false;
	 timer2.Enabled = chkERPJobStatusAndSendEmail.Checked;
	 }
	
	 private void timer1_Tick(object sender, EventArgs e)
	 {
	 if (DateTime.Now.Hour == clock)
	 checkSQLServerStatusWorker.RunWorkerAsync();
	 }
	
	 private void Form1_Load(object sender, EventArgs e)
	 {
	 if (!File.Exists(emailTemplatePath))
	 {
	 MessageBox.Show(string.Format("发送通知邮件模板（{0}）不存在, 请检查!", emailTemplatePath));
	 Application.Exit();
	 }
	 else
	 {
	 if (DateTime.Now.Minute == 0)
	 timer1.Enabled = true;
	 else
	 {
	 timer2.Interval = ((60 - DateTime.Now.Minute) * 60 - DateTime.Now.Second) * 1000;
	 timer2.Enabled = true;
	 }
	 checkSQLServerStatusWorker.DoWork += CheckERPJobStatusAndSendEmail;
	 checkSQLServerStatusWorker.RunWorkerCompleted += CheckERPJobStatusComplete;
	 }
	 }
	
	 private void timer2_Tick(object sender, EventArgs e)
	 {
	 timer2.Enabled = false;
	 if (chkERPJobStatusAndSendEmail.Checked && (DateTime.Now.Hour == clock || forceStart))
	 checkSQLServerStatusWorker.RunWorkerAsync();
	 if (!forceStart)
	 timer1.Enabled = chkERPJobStatusAndSendEmail.Checked;
	 }
	
	 private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
	 {
	 if (this.WindowState == FormWindowState.Minimized)
	 {
	 this.Show();
	 this.WindowState = FormWindowState.Normal;
	 notifyIcon1.Visible = false;
	 this.ShowInTaskbar = true;
	 }
	 }
	
	 private void Form1_SizeChanged(object sender, EventArgs e)
	 {
	 if (this.WindowState == FormWindowState.Minimized) //判断是否最小化
	 {
	 this.ShowInTaskbar = false; //不显示在系统任务栏
	 notifyIcon1.Visible = true; //托盘图标可见
	 notifyIcon1.ShowBalloonTip(1000);
	 }
	 }
	 }
	}

试试其它关键字

　SSIS　

同语言下

可能有用的

pan_yz贡献的其它代码(5)