SSIS 實戰：查詢結果轉 HTML 郵件自動發送（SQL XML + XSLT + Send Mail Task）

專案背景

B2B 專案需求：老闆每天想看新訂單，但不想登入後台，希望透過 Email 收到格式優美的 HTML 報表。本文示範使用 SSIS 2008 + VS 2008 實作完整流程。

架構流程

1. SQL XML 查詢：使用 FOR XML AUTO, ELEMENTS, TYPE, ROOT 產生結構化 XML
2. XSLT 轉換：套用 XSL 樣式表將 XML 轉為 HTML
3. 郵件發送：Send Mail Task 發送 HTML 內容

實作步驟詳解

1. 建立 SSIS 專案與封裝

• 專案類型：Integration Services Project
• 封裝命名：SendMailPackage.dtsx

2. 準備檔案資源目錄

建立 F:\SSIS_Example\SendMail\ 放置：

• QueryOrder.sql - 查詢腳本
• Orders.xsl - XSLT 樣式表
• Orders.htm - 輸出 HTML 檔

3. SQL 查詢腳本 (QueryOrder.sql)

/* 實際使用時將 @v_CurrentDate 換成 GETDATE() */
DECLARE @v_CurrentDate DATETIME
SET @v_CurrentDate = '2003-07-17'  -- 測試固定日期

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('No sales records available for this date.' AS XML)

4. 連線管理器設定

共需 4 個連線：

5. 控制流任務排程

1. Execute SQL Task：執行 QueryOrder.sql，結果集儲存至 Object 變數
2. Script Task：將 XML 結果套用 Orders.xsl 轉換，寫入 Orders.htm
3. Send Mail Task：讀取 Orders.htm 內容作為郵件本文 (IsBodyHtml=True)

6. XSLT 樣式表重點 (Orders.xsl)

<xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/Order">
    <html><body>
      <h2>Daily Sales Report - <xsl:value-of select="OrderDate"/></h2>
      <table border="1" cellpadding="3" cellspacing="0" style="border-collapse:collapse;">
        <tr style="background:#4472C4;color:white;">
          <th>Product</th><th>Subtotal</th>
        </tr>
        <xsl:for-each select="SalesOrderHeader">
          <tr>
            <td><xsl:value-of select="Product/Name"/></td>
            <td align="right"><xsl:value-of select="format-number(ProductSubtotal, '#,##0.00')"/></td>
          </tr>
        </xsl:for-each>
      </table>
    </body></html>
  </xsl:template>
</xsl:stylesheet>

生產環境部署建議

• 使用 SQL Server Agent 排程每日執行
• 查詢日期改用 DATEADD(day, -1, GETDATE()) 自動取前一日
• SMTP 設定使用加密連線 (SSL/TLS) 並儲存認證於封裝設定
• 加入錯誤處理事件：發送失敗時寫入日誌並重試