[MSSQL] SQL Server Agent 排程自動化更新不同 Server 上的資料表

串接 Stored Procedure 、不同 Server 的資料匯出匯入的步驟，產生 SSIS 封裝。依 Stored Procedure 執行結果的回傳值，決定是否執行資料匯入匯出的步驟。

Published in

C.Shan

10 min readMay 3, 2021

前言

近期工作遇到的需求是每個月須將測試機的資料，同步至正式機資料庫。簡化實際流程，在這篇文章單純分享: 如何透過 Stored Procedure (簡稱 SP) 的執行結果回傳值，決定是否進行資料匯入匯出的步驟。若 SP 回傳特定值或前一部執行失敗，會寄發信件通知。流程如下:

工具

1. Visual Studio 2019 (以下簡稱 VS2019)
   擴充功能(參考文章): 
   - SQL Server Integration Service Projects
   - Microsoft Reporting Services Projects
2. SQL Server 2017

步驟

1. Create a stored procedure in SQL Server
2. Create SQL Server connections in SSIS
3. Create a execute SQL task in SSIS
4. Set parameter in SSIS
5. Create a data flow task in SSIS
6. Create a script task for email sending in SSIS
7. Design the pipeline with parameter
8. Create a SQL task on SQL Server Agent

1. Create a stored procedure in SQL Server

首先，先簡單建一個有參數回傳的 SP。

CREATE PROCEDURE TestSP@result int OUTPUT, 
  @message varchar(max) OUTPUTASSET @result = 2;
  SET @message = '來自 Stored Procedure 的訊息'

2. Create SQL Server connections in SSIS

在 Visual Studio 裡，建一個專案 (請回顧參考文章)，由於會使用到 2 個不同 Server 上的資料庫，所以先建立其連接管理員。

實作上，連接 SP 我是用 ADO.NET 類型的連線；匯出匯入則是使用 OLE DB 類型的連線。不同類型的連接管理員，會影響到參數設定，詳細可以參考這篇。

3. Set a parameter in SSIS

TestSP 有 result 及 message 的參數回傳，在這次的實作中，需要在專案中，點右鍵選擇變數。接著新增 result 及 message ，並設定其型別與初始值。

這裡 result 參數的用途，與 pipeline 的邏輯判斷相關；message 參數，則是會用於寄發通知信的內容，也就是說信件的內文，是來自於 SP 的回傳參數。讓我們繼續看下去 …

4. Create a execute SQL task in SSIS

先建 執行 SQL 工作 (execute SQL task)

這裡的變數名稱 User::result 、 User::message 是上個步驟所設定的變數。假如上一步沒有設定好變數，這裡抓取對應的變數名稱可能會出現錯誤:

Failed to lock variable “User::result” for read access with error 0xC0010001 The variable cannot be found.

這種情況，是我把上一步設定的 result 變數刪除，模擬在執行 SQL 工作中，有用到 User::result 參數。但在專案裡，沒有設定對應的變數時，會顯示此錯誤。

執行 SQL 工作設定好之後，對其點右鍵，選執行工作，可以測試 TestSP 是否能執行成功。我必須說， VS2019 真的很方便！一開始，我並沒有看到這個功能鍵。假如沒有用 Integration Services Project 專案開啟封裝檔，並不會顯示這項功能，也因此踩很多雷。

執行成功後，在元件上，會顯示綠色勾。反之失敗，會顯示紅色叉，此時請看輸出視窗的訊息。

什麼情況是沒有用 Integration Services Project 專案開啟封裝檔?

我一開始是透過 SQL Server 匯出和匯入精靈產生 .dtsx 封裝檔，並非用 VS2019 Integration Services Project 專案建立一個封裝檔。只差這個步驟，少了執行工作功能鍵，遇到問題就很難 debug ，讓我踩很多坑，撞很多牆。

VS2019 建立 Integration Services Project 專案

5. Create a data flow task in SSIS

接續建資料流程工作 (data flow task)，也就是匯出匯入的步驟。

在資料流程的地方，會需要設定匯出的來源及匯入的目的地。

請設定來源端的資料庫與資料表。

請設定目的地端的資料庫與資料表。

接著可以按下執行工作，實際測試結果是否成功。

6. Create a script task for email sending in SSIS

請參閱 [MSSQL] SSIS 透過指令碼工作寄發信件。

7. Design the pipeline with the parameter

完成執行 SQL 工作、資料流程工作以及指令碼工作的元件設定，可以發現滑鼠移到元件上的時候，底下會出現綠色箭頭。拖曳箭頭，會出現延伸虛線。這個虛線碰到另一元件，可以將兩個元件關聯起來。

雙點擊綠色連接線，會出現優先順序條件約束編輯器，如下圖。假設箭頭是由執行 TestSP，指向同步線上資料庫。在條件約束選項中，如圖設定，當 TestSP 元件執行成功，才會接著執行同步線上資料庫元件；反之 TestSP 元件執行失敗，就不會接著執行同步線上資料庫元件。