在 SQL Server 預存程序中使用 sp_OA 執行 DTS 套件

應用場景

桌面程式或網頁需按需執行 DTS 套件 (匯入/匯出、資料倉儲 ETL、OLAP 建置、報表產生)。直接在用戶端執行 DTS 部署困難；SQL Agent Job 為非同步無法即時取得結果。使用 sp_OA 系列擴展預存程序在伺服器端同步呼叫 DTS 為最佳折衷。

核心步驟

1. 撰寫 VBScript 原型：參考 DTS Package Object Library，使用 LoadFromStorageFile 載入套件並執行。
2. 轉換為 sp_OA T-SQL：
   • sp_OACreate 對應 CreateObject。
   • sp_OAMethod、sp_OAGetProperty、sp_OASetProperty 對應方法/屬性呼叫。
   • 每個 sp_OA 呼叫必須檢查回傳值 (0 成功，非 0 失敗)，並對輸出參數加 OUTPUT 關鍵字。
3. 封裝為預存程序：接收套件 ID 參數，查詢設定表取得路徑，動態建構並執行。

關鍵代碼片段

DECLARE @hr INT, @objPackage INT, @pkgPath VARCHAR(255);

-- 建立 DTS.Package 物件
EXEC @hr = sp_OACreate 'DTS.Package', @objPackage OUTPUT;
IF @hr <> 0 GOTO ErrorHandler;

-- 載入套件檔案
EXEC @hr = sp_OAMethod @objPackage, 'LoadFromStorageFile', NULL, @pkgPath, '', '';
IF @hr <> 0 GOTO ErrorHandler;

-- 執行套件
EXEC @hr = sp_OAMethod @objPackage, 'Execute', NULL;
IF @hr <> 0 GOTO ErrorHandler;

-- 釋放物件
EXEC sp_OADestroy @objPackage;
RETURN 0;

ErrorHandler:
-- 取得錯誤描述
DECLARE @errDesc VARCHAR(255);
EXEC sp_OAGetErrorInfo @objPackage, @errDesc OUTPUT;
RAISERROR('DTS Execution Failed: %s', 16, 1, @errDesc);
EXEC sp_OADestroy @objPackage;
RETURN -1;

安全性考量

• sp_OA 需 EXECUTE 權限，僅授予必要登入。
• 使用 CURRENT_USER 搭配設定表控制可執行套件清單。
• DTS 執行身分為 SQL Server Agent 服務帳戶，需確保該帳戶具備存取來源/目的檔案權限。

擴充應用

sp_OA 可呼叫任意 COM 物件 (ADO、FileSystemObject、第三方元件)，不限於 DTS，亦可在非 SQL Server 環境 (ODBC/ADO) 透過連結伺服器遠端呼叫。

結語

雖然 T-SQL 代碼較 VBScript 冗長，但 sp_OA 提供伺服器端同步執行外部元件的強大能力，是整合自動化流程的關鍵技術。