SQL Server BI Step by Step SSIS 5 －－- 通过Email发送查询结果

核心提示：忙了一段，终于有时间来完成这一系列了，SQL Server BI Step by Step SSIS 5 －－- 通过Email发送查询结果，sql server 2008正式版已经发布了，接下来的系列都将基于sql server 2008+vs.net 2008开发，步骤里面设置执行我们刚才的SSIS包，再把计划设置

忙了一段，终于有时间来完成这一系列了。sql server 2008正式版已经发布了，接下来的系列都将基于sql server 2008+vs.net 2008开发。

引言

在一个B2B项目中遇到这样的一个情况,每天老板都想看到所有的新的订单信息，而这个老板很懒，不想登录系统后台，而是想通过查看邮件的方式。当然实现方式很多，这里我们介绍一个怎么使用SSIS包来实现这样的一个功能。使用SQL　 XML Query查询出XML结果数据，然后使用Xsl模板将xml数据转换成html，再将html内容发送邮件。

创建SSIS包

1.和vs.net 20005中一样的方式，创建一个Integration Service项目，并且包的名称修改为SendMailPackage

2.在你的本地硬盘上新建一个目录,比如F:SSIS_ExampleSendMail　 用来存放查询语句和Xsl文件.

3.在刚才的目录下新建一个QueryOrder.sql文件，填充如下内容，在连接管理器中新建一个文件连接，选择新建的sql文件:

隐藏行号 复制代码 ？ QueryOrder.sql

/*　
　　实际使用时将@v_CurrentDate换成当时日期　
*/　


declare　　@v_CurrentDate　　datetime　
set　　@v_CurrentDate　=　　'2003-07-17'　
　/*set　　@v_CurrentDate　=　GETDATE()*/　


if　　exists　　(select　1　
　　　　from　　Sales.SalesOrderHeader　
　　　　where　　OrderDate　=　　@v_CurrentDate)　


begin　


　　select　　top　10　oh.OrderDate,　
　　　　(select　　round(sum(TotalDue),　2)　
　　　　from　　Sales.SalesOrderHeader　
　　　　where　　OrderDate　=　　@v_CurrentDate)　as　DayTotal,　
　　　　p.ProductID,　p.Name,　
　　　　round(sum(oh.TotalDue),　2)　as　ProductSubtotal　
　　from　　AdventureWorks.Sales.SalesOrderHeader　　oh　
　　join　　AdventureWorks.Sales.SalesOrderDetail　　od　
　　on　　od.SalesOrderID　=　　oh.SalesOrderID　
　　join　　AdventureWorks.Production.Product　　p　
　　on　　p.ProductID　=　　　　od.ProductID　
　　where　　oh.OrderDate　=　　@v_CurrentDate　　　
　　group　
　　by　　oh.OrderDate,　p.ProductID,　p.Name　
　　order　　　
　　by　　5　desc,　p.ProductID　asc　
　　for　xml　auto,　elements,　type,　root('Order')　


end　


else　　select　cast('<NoRecords>No　sales　records　available　for　this　date.</NoRecords>'　as　xml)　


注:　 在这里我们使用一个固定的日期，实际中你可以使用系统方法GETDATE()或者DATEDIFF()来查询你需要的日期。

4.同样在目录下面新建一个Orders.xsl文件，由于内容较多，请直接下载附件中文件，这里也不在详细介绍xsl的具体内容。 同样也新建一个文件连接。

5.在目录下新建一个Orders.htm文件，用来存放生成的html内容，同时也新建一个文件连接。

6.新建一个Ado.net 连接你的AdventureWorks数据库。现在你的连接管理器有如下连接:

7.创建如下几个变量，作用域为当前包SendMailPackage.

名称	数据类型	值
varSalesSummaryXML	String
varSalesSummaryHTML	String
varMailTo	String	你的接收Email地址

8.在你的控制流中添加一个执行SQL任务,并设置sql类型为文件的方式，并选择我们的文件连接:

9.切换到结果集，点击添加，结果果名称为0,变量选择varSalesSummaryXML

10.在控制流中添加一个XML任务，将刚才的SQL任务的绿箭头指向XML任务，设置属性如下:

为了测试结果，上面我们将输入结果设置为保存文件的方式，先执行一下包，测试一下采用xml+xslt的方式生成的html的内容是否正确:

　

注意，现在我们再把输出结果改为变量的方式，设置DestinationType属性为变量的方式，并选择varSalesSummaryHTML

11. 在XML任务下面添加一个脚本任务来执行发送邮件，之所以不直接采用SSIS的发送邮件任务，是因为发送邮件任务目前不支持html内容.进行脚本任务的属性设置:

　

可以看到，脚本任务的设置相对于2005的版本发生比较大的变化，脚本语言终于开始支持了C#，原来的PreCompileScriptIntoBinaryCode属性不存在了，所有的脚本都要进行预编译，另外ReadOnlyVariables和ReadWriteVariables变量的设置都可以通过后面的选择对话框直接选择。这些改进方便了许多，设置完成后，我们点击编译脚本就可以起用用我们熟悉的C#来完成发送邮件的操作了.

注意我们需要引入System.Net.Mail，然后通过SmtpClient来通过指定的邮件服务器发送邮件，可以指定邮件帐号和密码，而SSIS的发送邮件任务是不支持非windows论证的。

隐藏行号 复制代码 ？ QueryOrder.sql

public　void　Main()　
{　
　　　SmtpClient　smtpClient　=　new　SmtpClient("邮件服务器地址",25);　
　　　string　body　=　Dts.Variables["varSalesSummaryHTML"].Value.ToString();　
　　　string　address　=　Dts.Variables["varMailTo"].Value.ToString();　
　　　MailMessage　mm　=　new　MailMessage("发送地址",address,　"Order　Detail",body);　
　　　mm.IsBodyHtml　=　true;　


　　　smtpClient.Credentials　=　new　System.Net.NetworkCredential("帐号","密码");　
　　　smtpClient.Send(mm);　


　　Dts.TaskResult　=　(int)ScriptResults.Success;　
}　
　　

12. 到现在为止已经完成了功能，直接执行包，就可以直接收到订单列表了，但是要想让这个包定期的执行，需要部署这个SSIS包，然后在数据库job中定义执行此包.右击当前的项目，设置CreateDeploymentUtility属性为True，这样能够自动生成部署文件.

重新编译此项目，在bin目录下面Deployment文件夹，双击下面的后缀名为SSISDeploymentManifest文件，启动包安装向导，在这里我们不详细介绍，直接下一步，直到完成.

13. 打开你的SSMS，确认已经启动Sql Server代理，新建作业，步骤里面设置执行我们刚才的SSIS包，再把计划设置为你想要的时间即可，这里就不再详细介绍。

参考

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62678/

出处：http://lonely7345.cnblogs.com/