让SSIS的错误邮件机制达到统一（存储过程方式）

我在工作当中接触到SSIS的时间也越来越长，问题也越来越多，多亏了很多朋友的帮助才能不间断的学习，在此表示感谢。

我们在每个包的设计时候常常会担心的一点就是在程序做ETL操作的时候报错。及时的知道ETL程序在什么时候发生错误发生了什么错误是我们这些设计人员比较关注的问题，这几天我也在考虑了这个问题，我总结了两种方式它们同时能达到同样的效果，也算是萝卜白菜各有所爱，我比较喜欢第二种，通过和网友“狐狸”的共同交流确定了如下解决方案。

 

目的有3点：

 

1. 捕获错误信息的邮件机制达到统一。

2. 配置邮件机制达到方便快速。

3. 邮件内容达到随时的变更。

 

一、前期准备

 

开通SQL SERVER的邮件机制功能（我用的是SQL SERVER2008 R2）

 

第一步：

 

1. 开通机制

 

use 你的数据库名称

exec sp_configure 'show advanced options',1  

reconfigure

exec sp_configure 'Database mail XPs',1 ;

reconfigure 

 

2. 建立邮件发送帐号

 

exec msdb..sysmail_add_account_sp @account_name = 'SQL邮件账户名称' 

,@email_address = '28136750@qq.com' -- 发件人邮件地址 

,@display_name = null -- 发件人姓名 

,@replyto_address = null 

,@description = null 

,@mailserver_name = 'smtp.qq.com' -- 邮件服务器地址 

,@mailserver_type = 'SMTP' -- 邮件协议

,@port = 25 -- 邮件服务器端口 

,@username = '28136750@qq.com'' -- 用户名 

,@password = '不能说！' -- 密码 

,@use_default_credentials = 0 

,@enable_ssl = 0 

,@account_id = null

 

3. 建立邮件profile文件

 

exec msdb..sysmail_add_profile_sp @profile_name = 'profile名称'  

,@description = '描述' 

,@profile_id = null

 

4. 账户与profile关联

 

exec msdb..sysmail_add_profileaccount_sp @profile_name = 'profile名称' 

,@account_name = 'account 名称'

,@sequence_number = 1 -- account 在 profile 中顺序

 

 

    恭喜，准备工作完成！

 

二、存储过程的建立

 

在要创建存储过程的数据库

 

 

存储过程代码如下：

 

USE [数据库名称]

GO

 

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

-- =============================================

-- Author:xxxxx

-- Create date: 2013-01-05

-- Description:整合SSIS数据抓取报错统一发送邮件入口

-- =============================================

CREATE PROCEDURE [dbo].[Pro_SendEmail]

@pErrorCode nvarchar(50), --错误号

@pErrorDescription nvarchar(max), --错误描述

@pMachineName nvarchar(100), --服务器名称

@pPackageName nvarchar(120), --包名称

@pSourceName nvarchar(120), --源名称

@pEventHandlerStartTime nvarchar(50), --时间处理开始时间

@pUserName nvarchar(50), --用户

@pCreatorComputerName nvarchar(50), --创建者电脑名称

@pCreatorName nvarchar(50) --创建者

AS

BEGIN

DECLARE

@title nvarchar(200),

@content nvarchar(max),

@recipients nvarchar(200);

SET NOCOUNT ON;

 

select @recipients = 28136750@qq.com'; --设置收件人

select @title = '服务器：' + @pMachineName + ' 于'+@pEventHandlerStartTime+' 发生错误：'+@pErrorCode; --设置收件人

 

select @content = '<table style="font-size=13px;">

<tr>

<td>服务器: '+@pMachineName+'</td>

<tr/>

<tr>

<td>用户: '+@pUserName+'</td>

<tr/>

<tr>

<td>创建者电脑名称: '+@pCreatorComputerName+'</td>

<tr/>

<tr>

<td>创建者: '+@pCreatorName+'</td>

<tr/>

<tr>

<td>发生时间: '+@pEventHandlerStartTime+'</td>

<tr/>

<tr>

<td>包名称: '+@pPackageName+'</td>

<tr/>

<tr>

<td>源名称: '+@pSourceName+'</td>

<tr/>

<tr>

<td>错误号: '+@pErrorCode+'</td>

<tr/>

<tr>

<td>错误描述: '+@pErrorDescription+'</td>

<tr/>

  </table>';

 

 

exec msdb..sp_send_dbmail @profile_name = 'profile名称'

,@recipients = @recipients 

,@subject = @title

,@body = @content

,@importance = 'High'

,@body_format = 'HTML'; 

 

SET NOCOUNT OFF;

END

 

GO

 

 

 

 

三、SSIS配置实例

 

在这一部就可以配置一个邮件发送的SQL脚本任务

 

选择对应包的时间处理程序，并拖入一个SQL脚本任务控件：

 

配置控件属性：

 

参数设置：

 

 

 

 

大功告成！

 

测试。。。。。。。。。。。。。。。。。。。。。。。

 

 

 我让他报错了。。。

 

但是这个邮件是我们要看到的！

 

 

 

 

如果要查看邮件发送的情况，语句如下：

 

use msdb

select * from sysmail_allitems 

select * from sysmail_mailitems 

select * from sysmail_event_log

 

 

 

本文章结束，希望对朋友们有帮助。