五餅二魚工作室

SQL Server 早期有使用 SQL Mail，可以透過 MAPI 的方式來做寄發通知信，後來又改成 Database Mail，可以支援 SMTP 的方式來寄送通知。雖然這個是蠻方便的方式，但是近來有些朋友和客戶，都會遇到透過 Database Mail 的時候，有些時候會等待較久的時間才能收到信件，或者是他們使用的 Mail 機制不支援 SMTP，變成沒有辦法將 SQL Server 的狀況通知到相關的人員。而這幾年即時通訊軟體已經是每個人都幾乎每天再使用的軟體之一，因此就有很多朋友再詢問是否可以直接使用 Slack 或者是 LINE 來通知即時訊息。

原本覺得就是很單純直接送訊息就好，所以也沒有特別去寫文章去做說明，但剛好最近有朋友遇到類似的問題，一直不知道要怎麼來處理，因此就順手整理一下我自己常用的方法。在此篇中我會使用 LINE Notify 通知的方式來進行，除了可以透過 API 來傳遞，而且不用到費用，而且會比 Slack 來的普及。

既然要用 LINE Notify，那自然要先去申請一個囉。首先先到 LINE Notify 的網站，登入之後選擇「個人頁面」→「發行權杖」

在這裡要稍微注意一下 , 因為這個網站設定跟 Line Bot 的幾乎相同 , 只是 Line Bot 會比較多的程序。當我們按下「發行權杖」後就可以設定我們通知的名稱和要發送到哪一個我們所使用的 LINE 頻道上面。這裡我就先建立一個只有我自己一個人的頻道，設定好之後就可以按下下方的「發行」按鈕。

此時系統就會提供一個權杖給我們，再關閉之前要注意一下確定是有把權杖給確實複製出來，否則關閉之後就不會再出現了，如果遺失就要把該權杖給移除，然後再重新產生一組。

---

在產生完 LINE Notify 的權杖之後，我們先來測試看看，是否可以正常發送訊息，這裡我使用 Postman 來進行測試。這裡有幾個地方要注意

1. 這裡要放 notify 的網址，基本上是 https://notify-api.line.me/api/notify?message={要傳遞的訊息}  ，如果有中文或特殊符號記得用 URI 編碼
2. 要採用 「Post」 的方法去呼叫 API
3. 授權驗證要記得選用「Bearer Token」
4. Token 的地方則用剛剛在 LINE 網站上所申請到的權杖

當我們送出訊息之後，正常來說在我們的 LINE 群組內就會收到訊息了。此時會有一種狀況會沒有收到，就是您沒有將 LINE Notify 加入到您要傳送訊息的群組內，因此這裡要是測試可以正常可以收到訊息，我們就可以來準備把這些處理放到 SQL Server 裡面了。

---

因為基本上 SQL Server 內多半都是用 T-SQL 的語法來處理，因此要去處理 HTTP 的呼叫來說，應該正常來說是沒有辦法的。而要做上述的處理，一般我們會用以下幾種方式來進行

1. 使用 SQL CLR，將 HTTP 的呼叫包裝成 Stored Procedure 來呼叫
2. 建立一個 SQL Job，在 Job 裡面去執行 Power Shell 來呼叫 API
3. 透過 SSIS 的專案佈署模式將封裝放在 SSISDB，利用 Stored Procedure 去呼叫執行

這三個方式各有其優缺點，但就我自己而言會比較喜歡用 SSIS 的封裝來做處理，我自己是覺得使用起來是比較靈活一點，自己也比較好掌握。因此在以下的範例中我會採用 SSIS 的封裝來進行。

而在我的 SSIS 專案裡面，其實是只有一個「指令碼工作」的 Task，在這個 Task 裡面我們會去用 C# 來進行處理

為了讓外部可以呼叫這個封裝的時候，可以傳遞資訊進來讓封裝按照所傳遞的資訊來運作，這裡我們在 Parameters 裡面加入兩個參數，第一個是 Message 的參數，我們就選擇不做加密。而另外一個 Token 因為比較重要，這裡我們在 Sensitive 就選擇 True，設定要做加密，並且把剛剛在前面測試 OK 的權杖值放在這裡，到時候放到 SQL Server 的時候，我們也可以將該設定值用「環境」裡面設定「變數」就可以比較安全的管控 Token 值不會外洩。

而指令碼元件裡面的程式碼也蠻簡單的，大家可以參考以下的程式來修改自己的。這個程式碼裡面有幾段是我自己有卡關的部分，希望大家在寫的時候要稍微注意一下

1. 因為預設在 .Net Framework 下並非採用 TLS 傳遞，造成沒有設定使用 TLS 1.1 以上會呼叫失敗
2. 在 .Net Framework 下不能使用 $"Bearer {token}" 這樣的字串處理，要改回用比較傳統的 string.format() 來做處理
3. 訊息裡面不能用 # 的符號，否則後面的訊息會被截掉
4. PosyAsync 是個非同步的處理，因此不能直接把那個回傳值設定給 response
5. 因為避免 token 被看到，我們在設定參數的時候有選擇加密，所以取得該參數的時候要改用 GetSensitiveValue()

public bool SendMessage(string token, string message)
{
    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;

    using (HttpClient client = new HttpClient())
    {
        client.BaseAddress = new Uri("https://notify-api.line.me/api/");

        client.DefaultRequestHeaders.Add("Authorization", string.Format("Bearer {0}",token ) );
        try
        {
            HttpResponseMessage response = client.PostAsync(string.Format("notify?message={0}", message), null).Result;
            return response.IsSuccessStatusCode;
        } catch ( Exception e)
        {
            Console.WriteLine(e.Message);
            return false;
        }
    }
}

/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
{
    // TODO: Add your code here
    string message = Uri.EscapeUriString((string)Dts.Variables["$Package::Message"].Value);
    string token = (string)Dts.Variables["$Package::Token"].GetSensitiveValue();
    Dts.TaskResult = SendMessage(token, message) ? (int)ScriptResults.Success : (int)ScriptResults.Failure;
}

除了 SSIS 可以用 Script Task 來處理呼叫 Web API 的部分 , 另外我也用了 SSIS 專案佈署模式的特性 , 建立了環境 , 並且建立一個變數來讓我存放 Token 的變數

透過這樣的處理 , 這樣每次呼叫這個封裝的時候就不用將 Token 的值放在程式碼內，一來會顯得比較安全一點 , 二來也可以讓呼叫比較方便 , 這樣我們再呼叫這個封裝的時候 , 可以利用類似以下的指令碼來處理

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'TEST', @project_name=N'SSIS_API', @use32bitruntime=False, @reference_id=1, @runinscaleout=False
Select @execution_id
DECLARE @var0 sql_variant = N'Help ! Server under attack from IP [127.0.0.1]'
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Message', @parameter_value=@var0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=1
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

我們只要去管中間的訊息要傳遞的訊息即可