SQL Server Integrated Service 初探
2021-04-02
初探 SQL Server Integrated Service，從維護計畫的 SSIS 封裝到如何創立 SSIS 專案。
說明
維護計畫中的 SSIS
維護計畫中的作業步驟包含備份、清除紀錄、重建索引等，都是封裝為 SSIS 並儲存於 SQL Server 之中。如果要檢視這些 SSIS 封裝，必須要連線到 Integrated Service 檢視，從中就可以加以匯出 (Export)。
需要注意的是必須要使用管理者權限開啟 SSMS，或者是依照連結的說明調整權限：
Connecting to Integration Services Access is Denied in SQL Server 2016 or 2017
安裝 SSDT
Visual Studio 2019 使用「Tools」接續「Get Tools And Features」後，找到「SQL Server Data Tools」並安裝。
安裝 SSIS Projects
Visual Studio 2019 使用「Extensions」接續「Manage Extensions」後，搜尋「SQL Server」找到 「SQL Server Integration Services Projects」 後進行安裝。
資料庫中的 SSIS
如果當初在安裝 SQL Server 的時候沒有使用安裝 SSIS，則使用 Source (安裝檔) 對已經安裝的 Instances 新增功能。
SSIS 的片段知識
- SSIS 的定位是 ETL 工具，但不限於此 ETL 也可以做資料庫伺服器的維護管理功能
- SQL Server 維護計畫產生的作業就是使用 SSIS 封裝
- SSIS 專案分為 Solutioni / Project / Package 等不同的層級，其中 Package 是最小的單位，另稱為封裝
- 以往可以在 SSMS 中直接編輯 Package(DTS)，但現在的做法是使用 Visual Studio 並安裝 SSDT 來進行編輯
- SSIS 專案可以設定 Environment / Parameters 來達到藉由參數控制 Package 的函數化用途
- SSIS 有對應的工具版本，例如 SSIS 2008 僅支援 Visual Studio 2008 商業智慧專案進行設計；Visaul Studio 2019 則可以支援 SSIS 2012 至 2019
- 匯出的封裝必須要確認資料的保護方式
- 第一次匯入的封裝必須要重新輸入連線密碼，因此必須妥善管理封裝中用到的所有連線密碼
32 Bit vs 64 Bit
- SSIS 的封裝本身沒有位元之分，負責執行的環境才有分 32 或 64 Bit
- Excel 2003 連線方式所使用的是 Microsoft Jet Database Engine，必須要在 32 位元的環境進行
- Excel 2007 以後連線方式所使用的是 Microsoft Access Database Engine ，必須使用 64 位元的環境進行
- 在 Visual Studio 中可以藉由專案屬性 「Run64BitRuntime」來設定執行環境的位元
- 部署到 Integration Services 目錄上，用以設定執行的環境
Fast Load 快速載入
- DataFlow 中設定使用快速載入，資料從來源是以批次的方式加載，載入的速度非常快
- 快速載入每次是以一個 Buffer 量的方式載入，如果發生錯誤則會整個 Buffer 拋棄
- 快速載入可以藉由設定 DefaultBufferMaxRows 以及 DefaultBufferSize 的方式來增加 Buffer 量
- Buffer 量是以資料先達到 MaxRows 或者 Size 來完成
參考資料
使用 Visual Studio 2019 開發商業智慧方案
How to import/export data to SQL Server using the SQL Server Import and Export Wizard
How to retrieve information about SSIS packages stored in MSDB Database