当前位置:   article > 正文

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开发。

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

创建SSIS包

    1.和vs.net 20005中一样的方式,创建一个Integration Service项目,并且包的名称修改为SendMailPackage
    2.在你的本地硬盘上新建一个目录,比如F:\SSIS_Example\SendMail  用来存放查询语句和Xsl文件.
    3.在刚才的目录下新建一个QueryOrder.sql文件,填充如下内容,在连接管理器中新建一个文件连接,选择新建的sql文件:

隐藏行号 复制代码 QueryOrder.sql
  1. /*
        
  2.    实际使用时将@v_CurrentDate换成当时日期
        
  3. */
        
  4. 
        
  5. declare    @v_CurrentDate    datetime
        
  6. set    @v_CurrentDate =    '2003-07-17'
        
  7.  /*set    @v_CurrentDate  = GETDATE()*/
        
  8. 
        
  9. if    exists    (select 1
        
  10.         from    Sales.SalesOrderHeader
        
  11.         where    OrderDate =    @v_CurrentDate)
        
  12. 
        
  13. begin
        
  14. 
        
  15.     select    top 10 oh.OrderDate, 
        
  16.         (select    round(sum(TotalDue), 2)
        
  17.         from    Sales.SalesOrderHeader
        
  18.         where    OrderDate =    @v_CurrentDate) as DayTotal,
        
  19.         p.ProductID, p.Name, 
        
  20.         round(sum(oh.TotalDue), 2) as ProductSubtotal
        
  21.     from    AdventureWorks.Sales.SalesOrderHeader    oh
        
  22.     join    AdventureWorks.Sales.SalesOrderDetail    od
        
  23.     on    od.SalesOrderID =    oh.SalesOrderID
        
  24.     join    AdventureWorks.Production.Product    p
        
  25.     on    p.ProductID =        od.ProductID
        
  26.     where    oh.OrderDate =    @v_CurrentDate    
        
  27.     group 
        
  28.     by    oh.OrderDate, p.ProductID, p.Name
        
  29.     order    
        
  30.     by    5 desc, p.ProductID asc 
        
  31.     for xml auto, elements, type, root('Order')
        
  32. 
        
  33. end
        
  34. 
        
  35. else    select cast('<NoRecords>No sales records available for this date.</NoRecords>' as xml)
        
  36. 
        
  37. 
        
  38. 
        
  39.     
        
  40. 
        

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

   4.同样在目录下面新建一个Orders.xsl文件,由于内容较多,请直接下载附件中文件,这里也不在详细介绍xsl的具体内容。 同样也新建一个文件连接。
   5.在目录下新建一个Orders.htm文件,用来存放生成的html内容,同时也新建一个文件连接。
   6.新建一个Ado.net 连接你的AdventureWorks数据库。现在你的连接管理器有如下连接:

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

名称数据类型
varSalesSummaryXMLString 
varSalesSummaryHTMLString 
varMailToString你的接收Email地址

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

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

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

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

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

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

 

 

 

 

 

      可以看到,脚本任务的设置相对于2005的版本发生比较大的变化,脚本语言终于开始支持了C#,原来的PreCompileScriptIntoBinaryCode属性不存在了,所有的脚本都要进行预编译,另外ReadOnlyVariables和ReadWriteVariables变量的设置都可以通过后面的选择对话框直接选择。这些改进方便了许多,设置完成后,我们点击编译脚本就可以起用用我们熟悉的C#来完成发送邮件的操作了.
      注意我们需要引入System.Net.Mail,然后通过SmtpClient来通过指定的邮件服务器发送邮件,可以指定邮件帐号和密码,而SSIS的发送邮件任务是不支持非windows论证的。
     

隐藏行号 复制代码 QueryOrder.sql
  1. public void Main()
        
  2. {
        
  3.       SmtpClient  smtpClient = new SmtpClient("邮件服务器地址",25);
        
  4.       string body =  Dts.Variables["varSalesSummaryHTML"].Value.ToString();
        
  5.       string address =  Dts.Variables["varMailTo"].Value.ToString();
        
  6.       MailMessage mm = new MailMessage("发送地址",address, "Order Detail",body);
        
  7.       mm.IsBodyHtml = true;
        
  8.       smtpClient.Credentials = new System.Net.NetworkCredential("帐号","密码");
        
  9.       smtpClient.Send(mm);
        
  10.     
  11.     Dts.TaskResult = (int)ScriptResults.Success;
        
  12. }
        

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

screenshot

 

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

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

参考
    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62678/
项目下载
   http://files.cnblogs.com/lonely7345/SSISMailExample.rar

作者:孤独侠客似水流年
出处:http://lonely7345.cnblogs.com/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。

分类: SQL Server

本文转自孤独侠客博客园博客,原文链接:http://www.cnblogs.com/lonely7345/archive/2009/09/03/1559579.html,如需转载请自行联系原作者
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/你好赵伟/article/detail/728380
推荐阅读
相关标签
  

闽ICP备14008679号