如何在SQL Server存儲過程中執行DTS包
2024-08-31 00:48:18
供稿：網友
本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。數據轉換服務（dts）在數據庫管理和開發的多種領域都有會涉及dts： 數據倉庫-將數據從原始的處理系統和表格中提取出來以供報表使用 建立olap 將大量數據從文本文件或其它非數據庫格式的文件中拷貝到數據庫 生成microsoft office文檔報表 使用 distributed transaction coordinator (dtc)實現多數據庫操作 在客戶的桌面程序或網站上，經常需要允許用戶按需執行dts包。在這種情況下，在部署dts包時，你應該決定將dts包安置在何處，以及通過何種手段調用它。 你的選擇 要建立一個按需執行的dts包，可以有多種選擇。下面就對這些選擇進行逐一說明。 sql server job 你可以在sql server里建立一個job，并調用sp_start_job存儲過程。使用sp_start_job的不足之處在于它是一個異步過程。由于它不能返回成功或失敗指示，你必須強制使用sp_help_job系統存儲過程查詢job的結果。除非不關心job調用后的結果，否則異步的job將使桌面程序或web程序變得很復雜。一個job可以被設置成非管理員（sa）模式，但需要一些額外的步驟。 在客戶端桌面使用dts dlls 第二種方法是用戶電腦載入enterprise manager或dts dlls，在用戶的電腦上調用dts包。雖然用戶電腦執行dts包有一定可行性，但也有不足：必須考慮到升級dts包帶來的分發和安裝問題。 在服務器上使用sp_oa 擴展存儲過程 第三種選擇，也就是本文所介紹的核心內容，就是使用sp_oa系統存儲過程族并有計劃的調用dts包。這種方案可以有效的避免上兩種方案的弊端。 使用 vbscript調用dts包 實現一個可以運行dts包的存儲過程的第一步是，編寫一段vbscript代碼。因為sp_oa存儲過程使用起來有些麻煩，因此在利用sp_oa存儲過程實現目標之前，要用vbscript編寫你希望實現的代碼。一般傾向于使用visual basic進行簡單的腳本開發工作。如圖a所示，通過在項目引用窗口中加入dts包對象庫，就可以在腳本中引用dts包對象了。 圖a：dts對象庫 在代碼中使用了loadfromstoragefile函數。一般說，開發工作應該在一個測試環境進行。了解dts格式的結構化，對將測試產品變為實際產品時很有幫助。 sp_oa 實現 寫好了vbscript代碼，就可用sp_oa擴展存儲過程實現代碼。和vbscript類似，sp_oa系統存儲過程允許與對象庫的com+ api進行交互。 sp_oacreate和在vb或vbscript中調用的createobject函數類似。sp_oagetproperty、sp_oasetproperty以及sp_oamethod用來連接對象庫中的特性和函數。和vb或 vbscript不同的是，sp_oa存儲過程導致的com+錯誤不會令sql語句失敗，因此必須手動檢查每個使用sp_oa的函數是否工作正常。 同時，很多sp_oa存儲過程都會引用參數，因此必須在sp_oa存儲過程中的適當參數后加入output語句。如果省略了output 語句，t-sql也不會發出警告信息。因此在運行時狀態，雖然存儲過程運行正常但也不會返回正確值。列表b是一個詳細的實現代碼。 解決方案中包括可以重命名dts包的表格以及實現的過程。其中sp_adrundtspackageonserver存儲過程接收一個id參數。在繼續執行前，程序會從t_addtspackagesetup表中，查找到達dts包的sql server路徑。 安全性 詳細的安全性問題不在本文的討論范圍，這里要說的是一些必須考慮到的基本問題： 在主數據庫的sp_oa擴展系統存儲過程中，實現sp_adrundtspackageonserver存儲過程需要execute權限。為了防止一些惡意用戶通過sp_oa過程實現某種目的，可以針對應用程序修改sql server規則，以加強安全性。 t-sql的current_user函數對系統安全會有稍許幫助。使用current_user和t_addtspackagesetup表格中的區域，可以查詢某個用戶是否被設為：使用給定的dts包。 dts包在sql server上執行時，會受到sql server agent服務的帳戶設置影響，如果從文件系統中讀取ascii文件，應該確定sql server agent的帳戶設置對該文件有通過許可。 擴展范例 可以使用sp_oa系統存儲過程與其他com+庫進行交互。同時在其他使用odbc和activex數據對象（ado）的非sql server系統上，sp_oa也可以有效的調用存儲過程。一個僅10行左右的vbscript腳本根本沒有實用價值，而最后合成的t-sql代碼會變得非常冗長。網上的sql server 2000 books 包括詳細的com+對象庫的支持說明，并包含了sp_oa系統存儲過程的相關文檔。當用戶再需要按需運行dts包時，不妨考慮使用sp_oa系統存儲過程來實現。
