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

2. /*    

3.    實際使用時將@v_CurrentDate換成當時日期    

4. */    

6. declare    @v_CurrentDate    datetime    

7. set    @v_CurrentDate =    '2003-07-17'    

8.  /*set    @v_CurrentDate  = GETDATE()*/    

10. if    exists    (select 1    

11.         from    Sales.SalesOrderHeader    

12.         where    OrderDate =    @v_CurrentDate)    

14. begin    

16.     select    top 10 oh.OrderDate,     

17.         (select    round(sum(TotalDue), 2)    

18.         from    Sales.SalesOrderHeader    

19.         where    OrderDate =    @v_CurrentDate) as DayTotal,    

20.         p.ProductID, p.Name,     

21.         round(sum(oh.TotalDue), 2) as ProductSubtotal    

22.     from    AdventureWorks.Sales.SalesOrderHeader    oh    

23.     join    AdventureWorks.Sales.SalesOrderDetail    od    

24.     on    od.SalesOrderID =    oh.SalesOrderID    

25.     join    AdventureWorks.Production.Product    p    

26.     on    p.ProductID =        od.ProductID    

27.     where    oh.OrderDate =    @v_CurrentDate        

28.     group     

29.     by    oh.OrderDate, p.ProductID, p.Name    

30.     order        

31.     by    5 desc, p.ProductID asc     

32.     for xml auto, elements, type, root('Order')    

34. end    

36. 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

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;    

9.       smtpClient.Credentials = new System.Net.NetworkCredential("帳號","密碼");    

10.       smtpClient.Send(mm);    

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

13. }    

12. 到現在為止已經完成了功能，直接執行包，就可以直接收到訂單列表了，但是要想讓這個包週期性執行，需要部署這個SSIS包，然後在資料庫job中定義執行此包.右擊當前的項目，設定CreateDeploymentUtility屬性為True，這樣能夠自動產生部署檔案.

 

     重新編譯此項目，在bin目錄下面Deployment檔案夾，雙擊下面的尾碼名為SSISDeploymentManifest檔案，啟動包安裝嚮導，在這裡我們不詳細介紹，直接下一步，直到完成.

13. 開啟你的SSMS，確認已經啟動Sql Server代理，新增作業，步驟裡面設定執行我們剛才的SSIS包，再把計劃設定為你想要的時間即可，這裡就不再詳細介紹。
     

參考
    http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/62678/
項目下載
   http://files.cnblogs.com/lonely7345/SSISMailExample.rar

作者：孤獨俠客（似水流年）
出處：http://lonely7345.cnblogs.com/
本文著作權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文串連，否則保留追究法律責任的權利。