SQL Server Integration Services 架构概观.ppt

1 1/23/23 SQL Server Integration Services SQL Server Integration Services 架構概觀架構概觀 2 2/23/23 大綱大綱 uu概覽概覽 uuBusiness Intelligence Development Business Intelligence Development Studio Studio 與設計與設計 SSIS SSIS 封裝的各種流封裝的各種流 程程 uu封裝設計的整體功能封裝設計的整體功能 uu封裝的部署與執行封裝的部署與執行 3 3/23/23 SQL Server Integration Services SQL Server Integration Services 是是 什麼？什麼？ uu全新的全新的 SQL Server SQL Server 商業智慧應用商業智慧應用 程式程式 uuData Transformation Services Data Transformation Services 的接的接 續版本續版本 uu高效能的資料整合平台高效能的資料整合平台 uu全新用全新用 .NET .NET 撰寫的架構，更豐撰寫的架構，更豐 富的功能富的功能 uu完善的資料檢視、單步除錯與錯完善的資料檢視、單步除錯與錯 誤處理誤處理 uu透過透過 pipeline pipeline 可以更豐富地加值可以更豐富地加值 資料，且全部在記憶體內完成，資料，且全部在記憶體內完成， 不需要中介資料表不需要中介資料表 uu以封裝以封裝(Package)(Package)為執行單位，而為執行單位，而 非專案非專案(Project)(Project) 4 4/23/23 SSIS SSIS 與與 DTS DTS 的差異概觀的差異概觀 uu將資料流程與工作流程分開將資料流程與工作流程分開 uu以更豐富，更細緻的資料流程工作以更豐富，更細緻的資料流程工作(Data Flow Task)(Data Flow Task) 取代取代 DTS DTS 的資料轉換工作的資料轉換工作 uu以容器工作提供迴圈、執行順序與交易管理以容器工作提供迴圈、執行順序與交易管理 uu以變數搭配以變數搭配 SSIS Expression SSIS Expression 取代取代 DTS DTS 的動態屬性工的動態屬性工 作作 uu封裝改以封裝改以 XML XML 的格式存放在的格式存放在 SQL Server SQL Server 或檔案系統或檔案系統 上，更方便做上，更方便做 Source ControlSource Control uu可以透過可以透過 DTS Migration Wizard DTS Migration Wizard 將舊的封裝轉換到新將舊的封裝轉換到新 的的 SSISSSIS 5 5/23/23 SSIS SSIS 架構架構 SSIS SSIS 物件核心，提供原物件核心，提供原 生及生及 .NET .NET 的程式存取介的程式存取介 面，可與面，可與 SSIS SSIS 所附的工所附的工 具程式或自行開發的程具程式或自行開發的程 式溝通。式溝通。 執行封裝、容器、工作執行封裝、容器、工作 。提供執行記錄、設定。提供執行記錄、設定 中斷點、可彈性設定、中斷點、可彈性設定、 管理連線、交易、事件管理連線、交易、事件 處理處理 資料流程引擎，將資料資料流程引擎，將資料 從來源提取出來，經過從來源提取出來，經過 各種工作完成轉換，再各種工作完成轉換，再 放入到目的。在流程中放入到目的。在流程中 ，你也可以加入自製的，你也可以加入自製的 元件。元件。 內含其他物件、如內含其他物件、如 TaskTask 、Container Container 等，提供等，提供 群組物件、執行單元、群組物件、執行單元、 迴圈、交易管理等服務迴圈、交易管理等服務 在在 For Each Loop For Each Loop Container Container 可以透過不可以透過不 同的同的 enumerator enumerator 來取來取 得各種集合內的物件，得各種集合內的物件， 也可以自行撰寫也可以自行撰寫 enumerator enumerator 類別類別 設計時管理各連線屬性設計時管理各連線屬性 ，執行時實際建立出連，執行時實際建立出連 線。線。SSIS SSIS 支援多種支援多種 Connection Manager Connection Manager 型態，如型態，如 ADOADO、 ADO.NETADO.NET、ExcelExcel、FileFile 、OLE DBOLE DB 各種可執行的物件，如各種可執行的物件，如 封裝、封裝、ContainerContainer、 Task Task 會丟出事件，你可會丟出事件，你可 已撰寫事件處理函式來已撰寫事件處理函式來 因應這些事件的發生因應這些事件的發生 透過既有的工具，如設透過既有的工具，如設 計環境、精靈、命令列計環境、精靈、命令列 工具或是自行開發的應工具或是自行開發的應 用程式，經由標準的程用程式，經由標準的程 式介面可以控制式介面可以控制 SSIS SSIS 引引 擎擎 封裝、封裝、Container Container 以及以及 Task Task 都可以輸出執行過都可以輸出執行過 程紀錄，經由多種紀錄程紀錄，經由多種紀錄 提供者，如文字逗號分提供者，如文字逗號分 隔檔隔檔(CSV)(CSV)、Profiler Profiler Trace(trc)Trace(trc)、SQL SQL 檔案檔案 等等，可以記錄到不等等，可以記錄到不 同的地方，你可以此監同的地方，你可以此監 控、除錯控、除錯 各種資料來源，可以提各種資料來源，可以提 供資料給供資料給 SSISSSIS Windows Windows 服務程式。用服務程式。用 來管理封裝儲存、遠端來管理封裝儲存、遠端 啟動執行與呈現當下執啟動執行與呈現當下執 行的封裝。行的封裝。 6 6/23/23 提供新的資料倉儲處理架構提供新的資料倉儲處理架構(1/2)(1/2) 在以往的運作模式下，資料整合的流程大多只做簡單的轉換與載入的工作 在資料庫伺服器內執行彙總、排序等其他工作 在做上述的整理資料動作的同時會與一般使用者查詢搶資源，互相耗損系統效能 這種解決方案無法完成大規模、多重且複雜的彙總 傳統的資料倉儲載入資料方式 7 7/23/23 提供新的資料倉儲處理架構提供新的資料倉儲處理架構(2/2)(2/2) 現在，透過 SQL Server Integration Services 可以同時完成整合、彙 總、排序等諸多資料處理動作，然後再載入到資料庫 讓資料庫伺服器可以專注在回答使用者的查詢 透過 64 位元版本的引擎，可以讓此方案處理大規模資料量且複雜的彙 總運算 透過 SQL Server Integration Services 載入資料 到資料倉儲 8 8/23/23 大綱大綱 uu概覽概覽 uuBusiness Intelligence Business Intelligence Development Studio Development Studio 與設計與設計 SSIS SSIS 封裝的各種流程封裝的各種流程 uu封裝設計的整體功能封裝設計的整體功能 uu封裝的部署與執行封裝的部署與執行 9 9/23/23 SSIS SSIS 管線管線(Plumbing)(Plumbing) 控制流程(Control Flow) 迴圈迴圈 FTP Task Send Mail Execute SQL Data Flow Task 資料流程(Data Flow) Flat File SourceSQL Data Source Merge Multi-cast Derive Column SQL Server Flat File 1010/23/23 工作流程工作流程 uu流程導向流程導向 uu前置條件前置條件( (成功，失敗，或完成成功，失敗，或完成) ) 前置條件可以做前置條件可以做 AND AND 和和 OR OR 等布林等布林 運算運算 uu從環境輸入參數從環境輸入參數 uuFile System TaskFile System Task、Execute SQL TaskExecute SQL Task、 Send Mail TaskSend Mail Task、Data Flow TaskData Flow Task Demo Demo 工作流程工作流程 1111/23/23 資料流程資料流程 uu特殊的工作流程：特殊的工作流程：Data Flow TaskData Flow Task uu資料處理導向，在記憶體中，以管線資料處理導向，在記憶體中，以管線 流程的概念流程的概念(pipeline)(pipeline)處理資料處理資料 uu建立或使用資料紀錄建立或使用資料紀錄 uu可以觀察資料變化的過程可以觀察資料變化的過程 uuSource AdapterSource Adapter、TransformTransform、Destination Destination AdapterAdapter Demo Demo 資料流程資料流程 1212/23/23 事件處理流程事件處理流程 uu經由事件觸發的特殊流程經由事件觸發的特殊流程 ( (例如：例如： OnErrorOnError、OnPostExecute)OnPostExecute) uu階層的架構階層的架構 uu可以簡單地寄發一封可以簡單地寄發一封 e-mail e-mail 或是複雜地或是複雜地 執行另外一段執行另外一段 dataflow task dataflow task 以處理錯誤以處理錯誤 的資料紀錄的資料紀錄 uu自訂的自訂的 components components 從基礎類別自動繼從基礎類別自動繼 承相關的事件承相關的事件 (OnError, OnWarning)(OnError, OnWarning) Component Component 的建立者可以自訂他的事的建立者可以自訂他的事 件件( (例如：例如：OnFTPFileFinishedCopy)OnFTPFileFinishedCopy) Demo Demo 事件處理流程事件處理流程 1313/23/23 大綱大綱 uu概覽概覽 uuBusiness Intelligence Development Business Intelligence Development Studio Studio 與設計與設計 SSIS SSIS 封裝的各種流封裝的各種流 程程 uu封裝設計的整體功能封裝設計的整體功能 uu封裝的部署與執行封裝的部署與執行 1414/23/23 變數變數 uu系統提供的變數：如系統提供的變數：如 MachineNameMachineName、 StartNameStartName、ErrorDescriptionErrorDescription等等等等 uu使用者自定變數，定義使用者自定變數，定義 NamespaceNamespace ScopeScope 資料型態資料型態 初始值初始值 uu可以在工作、程式碼、資料轉換將產生可以在工作、程式碼、資料轉換將產生 的資料集的資料集將值、物件儲存在變數中將值、物件儲存在變數中 Demo Demo 使用變數使用變數 1515/23/23 封裝除錯封裝除錯 uu設計時期設計時期 若發生設定錯誤，自動提供錯誤提示若發生設定錯誤，自動提供錯誤提示 檢視檢視 XML XML 原始碼原始碼 ( (可提供搜尋，但不要編輯可提供搜尋，但不要編輯) ) uu監控執行監控執行 編輯中斷點編輯中斷點 使用使用 Watch WindowWatch Window uu測試和除錯測試和除錯 停用停用 / / 啟用某些工作啟用某些工作 ForceExecutionResult ForceExecutionResult 強制執行結果為：強制執行結果為：SuccessSuccess、FailureFailure、CompletionCompletion 會影響前置條件會影響前置條件 1616/23/23 交易管理交易管理 uu讓多個工作可以一起成功、失敗讓多個工作可以一起成功、失敗 uu多個資料來源可以一致地修改資料多個資料來源可以一致地修改資料 uu連線到的資料來源需要支援分散式交易連線到的資料來源需要支援分散式交易 uu交易屬性設定在交易屬性設定在 Container Container 上上 RequireRequire、SupportedSupported、NotSupportedNotSupported uu透過不同透過不同 Container Container 對交易屬性的設定，可以對交易屬性的設定，可以 建立參予一同執行的交易，或是分開不同的建立參予一同執行的交易，或是分開不同的 交易。交易。 Demo Demo SSIS SSIS 的交易管理的交易管理 1717/23/23 執行紀錄執行紀錄 uu支援多種記錄提供者支援多種記錄提供者 文字檔、文字檔、SQL Server ProfilerSQL Server Profiler、寫到、寫到 SQL SQL Server Server 資料庫內資料庫內 sysdtslog90 sysdtslog90 資料表、資料表、 Windows Windows 事件、事件、XML XML 檔案檔案 uu透過透過 LoggingMode LoggingMode 屬性，設定各工作是否可以屬性，設定各工作是否可以 獨立設定記錄的事件獨立設定記錄的事件 uu某些工作有特有的記錄事件某些工作有特有的記錄事件 如指令碼工作的如指令碼工作的 ScriptTaskLogEntryScriptTaskLogEntry Demo Demo 設定封裝執行的紀錄設定封裝執行的紀錄 1818/23/23 大綱大綱 uu概覽概覽 uuBusiness Intelligence Development Business Intelligence Development Studio Studio 與設計與設計 SSIS SSIS 封裝封裝 uu封裝設計的整體功能封裝設計的整體功能 uu封裝的部署與執行封裝的部署與執行 1919/23/23 SSIS SSIS 生命週期相關的工具生命週期相關的工具 uu設計設計 SSIS PackageSSIS Package Business Intelligence Studio (Visual Business Intelligence Studio (Visual Studio)Studio) 為為 SQL Server 2005 SQL Server 2005 之前版本之前版本 packages packages 提供的提供的 Migration wizardMigration wizard 整合版本控管整合版本控管 (VSS)(VSS) uu佈署佈署/ /執行執行 複製複製 packages packages 的佈署工具的佈署工具 (dtutil.exe)(dtutil.exe) 命令列與使用者介面的執行工具命令列與使用者介面的執行工具 (dtexec.exe (dtexec.exe 和和 dtexecui.exe)dtexecui.exe) 彈性的設定選項彈性的設定選項 uu支援工具支援工具 豐富的豐富的 per-package Logging per-package Logging 透過透過 SQL Management Studio SQL Management Studio 監控監控 正在執行的正在執行的 packages packages 並組織管理並組織管理 packages packages Checkpoint - RestartabilityCheckpoint - Restartability 2020/23/23 SSIS SSIS 相關工具相關工具 SSIS 封裝 封裝檔案 BI Studio SSIS 服務 Mgt Studio 匯入/匯出精靈 佈署 安裝相關的檔案群組 Dtexec.exe Dtexecui.exe Dtutil.exe 執行 檢視當下的執行狀況 與匯入/匯出 佈署 管理 2121/23/23 SSIS Security SSIS Security 概觀概觀 uuPackage Package 可以透過可以透過 Package Protection level property Package Protection level property 屬性設定加解密屬性設定加解密 uu(A) (A) 若存放在若存放在 MSDBMSDB，再透過，再透過 SQL Database roles SQL Database roles 限制可以存取者的角色限制可以存取者的角色 uu(B) (B) 透過作業系統設定對於目錄和透過作業系統設定對於目錄和 package package 檔案設定存取權限檔案設定存取權限 uu利用程式碼簽署憑證替封裝簽章利用程式碼簽署憑證替封裝簽章 對存放 Package 的 MSDB 具有 Reader 和 Writer roles 作業系統 權限 SQL DB 角色 對於存放 Package 的檔案和目錄有存 取權限 Package Protection Level 屬性 A B 對具機密性的資料 加密還是一般文字 存放 Dem