SQL Server 2005 Integration Services(IS)
Download
Report
Transcript SQL Server 2005 Integration Services(IS)
SQL Server 2005 Integration
Services （IS）
使用 Integration Services 的 SSIS
設計師轉換資料
（Ref. Microsoft SQL Server 2005 管理實務，
施威銘研究室，旗標）
13章
1
Integration Services
SQL Server 匯入和匯出精靈：
Integration Services：
可以將單純的資料從各種來源匯入 SQL Server
需要先將資料處理或過濾, 例如檢查、合併或計算, 或是同時從多個
異質的來源匯入, 便必須使用 Integration Services 才能達成
Integration Services 為 SQL Server 2005 新增加的服務
從原本的 DTS (Data Transformation Services, 資料轉換服務 )
衍生並重新以 .NET 改寫而成
可以和其他OLE DB（object linking and embedding
database）、ODBC（ open database connectivity ） 來源、
純文字檔...等各格式, 進行資料轉移的工作
Integration Services 會將資料轉換過程的工作存成封裝 (Package)
物件, 以方便重複使用
SQL Server 可用來建立封裝的工具包括：
1.
2.
3.
複製資料庫精靈
SQL Server匯入和匯出精靈
SSIS 設計師
13章
2
SSIS 設計師
SSIS 設計師：
提供圖形化的介面, 自由設計資料轉換的流程
用法：
類似目前流行的圖形介面程式開發工具, 使用者可將需
要的物件 (如：代表資料來源與目的的物件), 用滑鼠拉
出, 並在各物件之間建立轉換的工作流程, 進而定義轉換
工作的各項內容等
SSIS 設計師還能設定發送電子郵件之類的事件處理動作
13章
3
Integration Services 的架構
Integration Services 與 DTS 最大的不同：
將控制與資料的流程分開, 提供了較佳的流程控管功能
Integration Services架構的簡要示意圖如下：
13章
4
Integration Services的架構說明
控制流程：
1.
∩
主要是由工作 (task) 所組成
Integration Services 提供了許多控制流程的工作元件, 包括
發送郵件、使用 FTP 傳送...等
資料流程也是屬於其中一個工作元件 控制流程
資料流程
控制流程中還可以放置容器 (container)
每個容器可內含多個工作或是其他容器
使用容器可將相關工作分組, 讓流程控制更加結構化
還可提供迴圈功能, 重複執行容器內的工作
2.
資料流程：
用來處理資料轉換的工作, 所有資料的匯入、匯出、轉換便是在
資料流程內進行
Integration Services製作封裝時：
1.
2.
大原則應該先在控制流程內建立一個資料流程的工作
然後才能在資料流程內設計資料的來源、目的與轉換
13章
5
使用 SSIS 設計師
－製作簡單封裝
1.
2.
3.
4.
5.
6.
資料的來源與目的
SSIS 設計師操作介面簡介
建立資料來源的連接
建立資料目的的連接
使用 SSIS 設計師製作封裝
執行封裝
13章
6
1.資料的來源與目的
－ Sales.CreditCard 資料表
SQL Server 的 AdventureWorks 資料庫中有一個
Sales.CreditCard 資料表, 結構與內容如下：
13章
7
1.資料的來源與目的
－ ccnumber.txt 純文字檔案
ccnumber.txt 純文字檔案, 其內容如下：
欄位間以逗號分隔
目的：使用 SSIS 設計師製作一個封裝, 將 ccnumber.txt 的內容
轉移到AdventureWorks 資料庫的 Sales.CreditCard 資料表
13章
8
2. SSIS 設計師操作介面簡介
－開啟SSIS 設計師
開始/ 所有程式/Microsoft SQL Server 2005 /SQL Server
Business Intelligence Development Studio
開啟 Microsoft Visual Studio 視窗, 執行『檔案/新增/專案』,
如下開啟 SSIS 設計師：
13章
9
2. SSIS 設計師操作介面簡介
－功能介紹
13章
10
2. SSIS 設計師操作介面簡介
－功能介紹說明
13章
11
2. SSIS 設計師－操作注意事項
Integration Services 必須先建立來源與目的的連接
(connection), 才能讀取或寫入資料
例如：
將純文字檔資料轉移到 SQL Server, 須先建立
檔案與 SQL Server 資料庫這兩個連接, 才能
製作封裝進行資料轉移
13章
12
3. 建立資料來源的連接
－純文字檔案的連接（1）
建立資料來源, 也就是純文字檔案的連接, 請在 SSIS 設計師中操作：
13章
13
3. 建立資料來源的連接
－純文字檔案的連接（2）
13章
14
3. 建立資料來源的連接
－純文字檔案的連接（3）
13章
15
3. 建立資料來源的連接
－純文字檔案的連接（4）
13章
16
3. 建立資料來源的連接
－設定欄位的屬性
請如下設定各個欄位的屬性：
注意：
欄位名稱 (Name) 可以自由設定
長度 (OutputColumnWidth)與型別 (DataType) 則要依照匯入目的地資
料表內各欄位而定
特別是數字,應該設定為多少位元組、是否帶正負號等屬性
設定錯誤, 會造成封裝無法正常執行
13章
17
3. 建立資料來源的連接
－設定目的資料表欄位的屬性
目的資料表：
ExpMonth 欄位為無負號的 tinyint 類型
來源的到期月欄位應該選擇單一位元組不帶正負號的整數
[DT_UI1]
ExpYear 欄位是有負號的 smallint 類型
到期年欄位必須選擇二位元組帶正負號的的整數 [DT_I2]
CardType 與 CardNumber 欄位是 nvarchar 類型
卡別與卡號欄位必須選擇 Unicode 字串 [DT_WSTR]
13章
18
3. 建立資料來源的連接
－預覽輸出的資料
選擇預覽頁次：預覽此連接將會輸出的資料
13章
19
4. 建立資料目的的連接
－與AdventureWorks 資料庫連接（1）
與 SQL Server 的 AdventureWorks 資料庫連接
13章
20
4. 建立資料目的的連接
－與AdventureWorks 資料庫連接（2）
13章
21
4. 建立資料目的的連接
－與AdventureWorks 資料庫連接（3）
13章
22
4. 建立資料目的的連接
－與AdventureWorks 資料庫連接（4）
13章
23
5. 使用 SSIS 設計師製作封裝
－控制流程頁次
在 SSIS 設計師選擇控制流程頁次, 如下操作：
13章
24
5. 使用 SSIS 設計師製作封裝
－資料流程頁次
13章
25
5. 使用 SSIS 設計師製作封裝
－一般檔案來源元件
13章
26
5. 使用 SSIS 設計師製作封裝
－一般檔案來源元件編輯（1）
13章
27
5. 使用 SSIS 設計師製作封裝
－一般檔案來源元件編輯（2）
13章
28
5. 使用 SSIS 設計師製作封裝
－一般檔案來源元件編輯（3）
13章
29
5. 使用 SSIS 設計師製作封裝
－SQL Server 目的地元件（1）
13章
30
5. 使用 SSIS 設計師製作封裝
－SQL Server 目的地元件（2）
13章
31
5. 使用 SSIS 設計師製作封裝
－SQL Server 目的地元件編輯（1）
13章
32
5. 使用 SSIS 設計師製作封裝
－SQL Server 目的地元件編輯（2）
13章
33
5. 使用 SSIS 設計師製作封裝
－SQL Server 目的地元件編輯（3）
13章
34
5. 使用 SSIS 設計師製作封裝
－SQL Server 目的地元件編輯（4）
13章
35
6. 執行封裝
按 SSIS 設計師工具列上的
鈕, 即可執行封裝：
13章
36
6. 執行封裝－注意事項
輕微的錯誤：
不影響執行,所以元件仍然會顯示為綠色, 但是上面會有驚
嘆號的圖示：
滑鼠按一下該元件後, 將游標放置於驚嘆號圖示, 可以看
到警告的訊息
13章
37
控制流程中加入迴圈
範例：
ccnumber1.txt ~ ccnumber6.txt 共 6 個相同格式檔案輸
入到 SQL Server
可以使用迴圈一次完成資料轉移的動作
控制流程中加入迴圈說明：
1.
2.
3.
4.
複製之前製作的封裝
插入迴圈容器
設定 Foreach 迴圈容器
修改來源連接
13章
38
1. 複製之前製作的封裝
－開啟之前的方案方法一
重新開啟之前的方案：
SQL Server Business Intelligence Development Studio, 執行
『檔案/ 開啟/ 專案/方案』命令
13章
39
1. 複製之前製作的封裝
－開啟之前的方案方法二
也可直接在 Microsoft Visual Studio 視窗的起始頁頁次, 選擇最近
使用的專案：
13章
40
1. 複製之前製作的封裝
－複製舊封裝（1）
建議：請以複製舊封裝的方式來所要的封裝
方案總管窗格內如下操作：
13章
41
1. 複製之前製作的封裝
－複製舊封裝（2）
13章
42
2. 插入迴圈容器
－控制流程頁次
開啟封裝, 在 SSIS 設計師選擇控制流程頁次：
13章
43
2. 插入迴圈容器
－ For 迴圈容器 & Foreach 迴圈容器
For 迴圈容器：
利用條件判斷來決定是否繼續執行迴圈
例如：設定判斷運算式為 “i < 5”, 若 i 變數大於等於 5 時, 該判斷
運算式為假, 迴圈就會停止運作
Foreach 迴圈容器：
使用各種的列舉值來執行迴圈
例如：
變數式的列舉：
設定 i 變數，列舉值為 1, 2, 3 等三個值
Foreach 迴圈容器便會逐一將各個值代入i 變數, 總共會
執行 “i=1”、“i=2”、“i=3” 共三次迴圈
檔案式列舉值：
設定列舉值為 a*.txt
迴圈就會逐一讀取指定資料夾, 以 a 開頭、副檔名
為 .txt 的所有檔案
13章
44
3. 設定 Foreach 迴圈容器（1）
使用Foreach 迴圈容器的檔案式列舉值來執行迴圈
設定 Foreach 迴圈容器如何執行迴圈如下：
13章
45
3. 設定 Foreach 迴圈容器（2）
13章
46
3. 設定 Foreach 迴圈容器（3）
13章
47
3. 設定 Foreach 迴圈容器（4）
13章
48
4. 修改來源連接（1）
建立來源連接時：
修改為使用 Foreach 迴圈的檔名變數, 否則迴圈將無法讀取各個來源資
13章
49
4. 修改來源連接（2）
13章
50
4. 修改來源連接（3）
13章
51
新增、合併、取代與計算新欄位
真實例子可能來源與目的的欄位數量不同, 甚至可能
需要取代、合併或計算新的欄位值, 才能將資料輸入
到目的地
說明如何在封裝內進行資料的處理：
1.
2.
3.
4.
5.
欄位不符合的來源檔
修改來源連接
修正資料來源元件
加入並設定資料處理的元件
將處理過的資料輸出到目的元件
13章
52
1. 欄位不符合的來源檔
－欄位不一致
假設ccnumber.txt 不再與 Sales.CreditCard 資料表
的欄位一致, 新的內容如下：
13章
53
1. 欄位不符合的來源檔
－轉換需求條件
轉換需求條件：
1.
2.
3.
信用卡的到期月份都是 07 月 (不過來源檔案中並沒有此資料)
到期的年份有誤, 必須全部減兩年
flagbank 與 runpcbank 發行的卡是 Vasa 信用卡, 其他銀行的卡則
都是 SuperCard 信用卡
要將資料輸入到 Sales.CreditCard 資料表, 需先經過以下處理：
1.
2.
3.
4.
全部的到期年 - 2
新增一個到期月的欄位, 且欄位值為 07
將卡號欄位1、卡號欄位2、卡號欄位3、卡號欄位4 合併為新的卡
號欄位
建立判斷式：
如果發卡銀行欄位值為 flagbank 與 runpcbank, 則卡別為
Vasa；否則為 SuperCard
13章
54
2. 修改來源連接（1）
來源的純文字檔案格式不相同, 要先修改來源連接
於連接管理員窗格的純文字檔案連接上按右鈕, 執行『編輯』
13章
55
2. 修改來源連接（2）
13章
56
2. 修改來源連接（3）
進階頁次如下設定各個欄位的屬性：
13章
57
3. 修正資料來源元件（1）
在 SSIS 設計師的資料流程頁次如下操作：
13章
58
3. 修正資料來源元件（2）
13章
59
4. 加入並設定資料處理的元件
－刪除原來的流程
須刪除原來的流程, 在兩者中間加入處理資料的元件
在 SSIS 設計師的資料流程頁次中如下操作：
13章
60
4. 加入並設定資料處理的元件
－加入衍生的資料行元件
13章
61
4. 加入並設定資料處理的元件
－編輯衍生的資料行元件（1）
13章
62
4. 加入並設定資料處理的元件
－編輯衍生的資料行元件（2）
13章
63
4. 加入並設定資料處理的元件
－編輯衍生的資料行元件（3）
其他資料行處理：
13章
64
4. 加入並設定資料處理的元件
－編輯衍生的資料行元件（4）
信用卡別的欄位：
須以判斷式來判定卡別
問號前的判斷式會測試發卡銀行欄位值是否為 “ flagbank”
或“runpcbank”
若判斷式為真, 則輸出 “Vasa”, 否則輸出 "SuperCard“
13章
65
5. 將處理過的資料輸出到目的元件（1）
在 SSIS 設計師的資料流程頁次如下操作：
13章
66
5. 將處理過的資料輸出到目的元件（2）
13章
67
5. 將處理過的資料輸出到目的元件（3）
確保正確性：重新編輯 SQL Server 目的地
13章
68