blog.Toby

問題描述:

當(dāng)你把數(shù)據(jù)從其他數(shù)據(jù)庫(kù), 或者是文本文件之類的其他數(shù)據(jù)源導(dǎo)入到目的數(shù)據(jù)庫(kù)時(shí), 有時(shí)希望在導(dǎo)入的處理中, 能夠?qū)崿F(xiàn)"數(shù)據(jù)存在時(shí)更新, 不存在時(shí)導(dǎo)入"

在之前, 一般是通過導(dǎo)入臨時(shí)表, 然后再判斷處理導(dǎo)入正式表的, 在SQL Server 2005中, SSIS可以在導(dǎo)入處理時(shí)直接完成這種處理.

1.          準(zhǔn)備測(cè)試環(huán)境

-- 1. 在數(shù)據(jù)庫(kù)中創(chuàng)建下面的對(duì)象

USE tempdb

GO

CREATE TABLE dbo.tb(

    id int PRIMARY KEY,

    name nvarchar(128))

GO

-- 2. 準(zhǔn)備兩個(gè)文本文件, 放在d:"test 目錄下, 文件的內(nèi)容如下

t1.txt

id name

1   張三

2   李四

t2.txt

id name

1   張三君

3   李林

4   阿聯(lián)酋

2.          創(chuàng)建新的 Integration Services 項(xiàng)目（創(chuàng)建SSIS包）

Ø       在“開始”菜單中，依次指向“所有程序”、“Microsoft SQL Server 2005”，再單擊 SQL Server Business Intelligence Development Studio。

Ø       在“文件”菜單中，指向“新建”，再單擊“項(xiàng)目”，以創(chuàng)建一個(gè)新的 Integration Services 項(xiàng)目。

Ø       在“新建項(xiàng)目”對(duì)話框的“模板”窗格中，選擇“Integration Services 項(xiàng)目”。

Ø       在“名稱”框中，將默認(rèn)名稱更改為 SSIS Tutorial。或者，清除“創(chuàng)建解決方案的目錄”復(fù)選框。

Ø       接受默認(rèn)位置，或單擊“瀏覽”，以瀏覽并找到要使用的文件夾。

Ø       在“項(xiàng)目位置”對(duì)話框中，單擊文件夾，再單擊“打開”。

Ø       單擊“確定”。

Ø       默認(rèn)情況下，將創(chuàng)建一個(gè)名為 “新建包.dtsx“的空包，并將該包添加到項(xiàng)目中。

Ø       在解決方案資源管理器工具欄中，右鍵單擊 Package.dtsx，再單擊“重命名”，將默認(rèn)包重命名為 Lesson 1.dtsx。

Ø       當(dāng)系統(tǒng)提示重命名包對(duì)象時(shí)，單擊“是”。

3.          為SSIS包添加數(shù)據(jù)源（導(dǎo)入數(shù)據(jù)的源和目標(biāo)數(shù)據(jù)源）

Ø       首先添加導(dǎo)入數(shù)據(jù)的源

Ø       右鍵單擊“連接管理器”區(qū)域中的任意位置，再單擊“新建平面文件連接”。

Ø       在“平面文件連接管理器編輯器”對(duì)話框的“連接管理器名稱”字段中，鍵入 Source。

Ø       單擊“瀏覽”。

Ø       在“打開”對(duì)話框中，瀏覽并找到“d:"test"t1.txt”文件。

Ø       “常規(guī)”選項(xiàng)中，勾選“在第1個(gè)數(shù)據(jù)行中顯示列名稱”。

Ø       “高級(jí)”選項(xiàng)中，選擇“id”列，將數(shù)據(jù)類型設(shè)置為“four-byte single integer[DT_I4]”。

Ø       “高級(jí)”選項(xiàng)中，選擇“name”列，將數(shù)據(jù)類型設(shè)置為“Unicode string[DT_WSTR]”。

Ø       然后，你可以在“預(yù)覽”中查看數(shù)據(jù)是否正確。

Ø       然后添加接收數(shù)據(jù)的目的數(shù)據(jù)源

Ø       右鍵單擊連接管理器區(qū)域中的任意位置，再單擊“新建OLE DB 連接”。

Ø       在“配置OLE DB 連接管理器”對(duì)話框中，單擊“新建”。

Ø       在“服務(wù)器名稱”中，輸入localhost。

Ø       將localhost 指定為服務(wù)器名稱時(shí)，連接管理器將連接到本地計(jì)算機(jī)上Microsoft SQL Server 2005 的默認(rèn)實(shí)例。若要使用SQL Server 2005 的遠(yuǎn)程實(shí)例，請(qǐng)將localhost 替換為要連接到的服務(wù)器的名稱。

Ø       在“登錄到服務(wù)器”組中，確認(rèn)選擇了“使用Windows 身份驗(yàn)證”。

Ø       在“連接到數(shù)據(jù)庫(kù)”組的“選擇或輸入數(shù)據(jù)庫(kù)名稱”框中，鍵入或選擇tempdb。

Ø       單擊“測(cè)試連接”，驗(yàn)證指定的連接設(shè)置是否有效。

Ø       單擊“確定”。

Ø       單擊“確定”。

Ø       在“配置OLE DB 連接管理器”對(duì)話框的“數(shù)據(jù)連接”窗格中，確認(rèn)選擇了localhost.tempdb。

Ø       單擊“確定”。

4.          為SSIS包添加數(shù)據(jù)流任務(wù)

Ø       單擊“控制流”選項(xiàng)卡。

Ø       在“工具箱”中，展開“控制流項(xiàng)”，并將一個(gè)數(shù)據(jù)流任務(wù)拖到“控制流”選項(xiàng)卡的設(shè)計(jì)圖面上。

Ø       在“控制流”設(shè)計(jì)圖面中，右鍵單擊新添加的數(shù)據(jù)流任務(wù)，再單擊“重命名”，將名稱更改為Import Data。

5.          在數(shù)據(jù)流任務(wù)中設(shè)置數(shù)據(jù)流源

Ø       打開“數(shù)據(jù)流”設(shè)計(jì)器，方法是雙擊Import Data 數(shù)據(jù)流任務(wù)或單擊“數(shù)據(jù)流”選項(xiàng)卡。

Ø       在“工具箱”中，展開“數(shù)據(jù)流源”，然后將“平面文件源”拖動(dòng)到“數(shù)據(jù)流”選項(xiàng)卡的設(shè)計(jì)圖面上。

Ø       在“數(shù)據(jù)流”設(shè)計(jì)圖面上，右鍵單擊新添加的“平面文件源”，單擊“重命名”，然后將該名稱更改為Source Data。

Ø       雙擊此平面文件源，打開“平面文件源編輯器”對(duì)話框。

Ø       在“平面文件連接管理器”框中，鍵入或選擇Source。

Ø       單擊“列”并驗(yàn)證列名是否正確。

Ø       單擊“確定”。

6.          在數(shù)據(jù)流任務(wù)中添加查找處理組件

Ø       在“工具箱”中，展開“數(shù)據(jù)流轉(zhuǎn)換”，然后將“查找”拖動(dòng)到“數(shù)據(jù)流”選項(xiàng)卡的設(shè)計(jì)圖面上。將“查找”直接放置在Source Data 源的下面。

Ø       單擊Source Data 平面文件源，并將綠色箭頭拖動(dòng)到新添加的“查找”轉(zhuǎn)換中，以連接這兩個(gè)組件。

Ø       在“數(shù)據(jù)流”設(shè)計(jì)圖面上，右鍵單擊新添加的“查找”轉(zhuǎn)換，單擊“重命名”，然后將該名稱更改為Lookup id。

Ø       雙擊Lookup id 轉(zhuǎn)換。

Ø       在“查找轉(zhuǎn)換編輯器”對(duì)話框的“OLE DB 連接管理器”框中，確保顯示localhost.tempdb。

Ø       在“使用表或視圖”框中，鍵入或選擇[dbo].[tb]。

Ø       單擊“列”選項(xiàng)卡。

Ø       在“可用輸入列”面板中，將id 拖放到“可用查找列”面板的id 上。

Ø       單擊“確定”。

7.          在數(shù)據(jù)流任務(wù)中添加插入數(shù)據(jù)處理需要的目標(biāo)數(shù)據(jù)源

Ø       在“工具箱”中，展開“數(shù)據(jù)流目標(biāo)”，并將“OLE DB 目標(biāo)”拖到“數(shù)據(jù)流”選項(xiàng)卡的設(shè)計(jì)圖面上。將OLE DB 目標(biāo)直接放置在“Lookup id”轉(zhuǎn)換的下面。

Ø       單擊“Lookup id”轉(zhuǎn)換，并將紅色箭頭拖到新添加的“OLE DB 目標(biāo)”上，以便將兩個(gè)組件連接在一起。

Ø       在出現(xiàn)的“配置錯(cuò)誤輸出”對(duì)話框中，“錯(cuò)誤”列中選擇“重定向行”

Ø       單擊“確定”。

Ø       在“數(shù)據(jù)流”設(shè)計(jì)圖面上，右鍵單擊新添加的“OLE DB 目標(biāo)”組件，單擊“重命名”，然后將名稱更改為Insert data。

Ø       雙擊Insert data。

Ø       在“OLE DB 目標(biāo)編輯器”對(duì)話框中，確保已在“OLE DB 連接管理器”框中選中localhost.tempdb。

Ø       在“表或視圖的名稱”框中，鍵入或選擇[dbo].[tb]。

Ø       單擊“映射”。

Ø       驗(yàn)證id, name 輸入列是否已正確映射到目標(biāo)列。如果映射了同名列，則說明映射正確。

Ø       單擊“確定”。

8.          在數(shù)據(jù)流任務(wù)中添加更新數(shù)據(jù)處理需要的OLE DB命令組件

Ø       在“工具箱”中，展開“數(shù)據(jù)流組件轉(zhuǎn)換”，并將“OLE DB 命令”拖到“數(shù)據(jù)流”選項(xiàng)卡的設(shè)計(jì)圖面上。將OLE DB 目標(biāo)直接放置在“Lookup id”轉(zhuǎn)換的下面。

Ø       單擊“Lookup id”轉(zhuǎn)換，并將綠色箭頭拖到新添加的“OLE DB 命令”上，以便將兩個(gè)組件連接在一起。

Ø       在“數(shù)據(jù)流”設(shè)計(jì)圖面上，右鍵單擊新添加的“OLE DB命令”組件，單擊“重命名”，然后將名稱更改為Update data。

Ø       雙擊Update data。

Ø       在“Update Data 高級(jí)編輯器”對(duì)話框中，“連接管理”選項(xiàng)的“連接管理器”列中，選中localhost.tempdb。

Ø       在“組件屬性”選項(xiàng)中，“自定義屬性”的“SQLCommand”屬性中輸入：

UPDATE dbo.tb SET name = ? WHERE id = ?

Ø       在“列映射”選項(xiàng)中，設(shè)置“輸入列”，將name映射到param_0，將id映射到param_1。注：param_0對(duì)應(yīng)UPDAT語(yǔ)句中的第1個(gè)?，而param_1對(duì)應(yīng)UPDATE語(yǔ)句中的第2個(gè)?，這是固定的。

Ø       單擊“確定”。

9.          測(cè)試

Ø       按“F5”執(zhí)行SSIS包

Ø       執(zhí)行結(jié)束（所有的組件都變?yōu)榫G色），你會(huì)看到數(shù)據(jù)流向“Inset Data”的有兩條數(shù)據(jù)

Ø       雙擊“連接管理器”中的Source，重新設(shè)置文件名為D:"test"d2.txt。

Ø       單擊“確定”

Ø       按“Ctrl+Shift+F5”，重新啟動(dòng)SSIS

Ø       執(zhí)行結(jié)束（所有的組件都變?yōu)榫G色），你會(huì)看到數(shù)據(jù)流向“Inset Data”的有兩條數(shù)據(jù)，流向“Update Data”的有1條數(shù)據(jù)

Ø       最后，在數(shù)據(jù)庫(kù)中查詢tempdb.dbo.tb，驗(yàn)證數(shù)據(jù)導(dǎo)入的正確性

10.       添加循環(huán)，一次完成test目錄下所有文件的導(dǎo)入

Ø       在Business Intelligence Development Studio 中，單擊“控制流”選項(xiàng)卡。

Ø       在“工具箱”中，展開“控制流項(xiàng)”，然后將“Foreach 循環(huán)容器”拖到“控制流”選項(xiàng)卡的設(shè)計(jì)圖面上。

Ø       右鍵單擊新添加的“Foreach 循環(huán)容器”，并選擇“編輯”。

Ø       在“Foreach 循環(huán)編輯器”對(duì)話框的“常規(guī)”頁(yè)中，為“名稱”輸入Foreach File in Folder。單擊“確定”。

Ø       為Foreach 循環(huán)容器配置枚舉器

Ø       雙擊文件夾中的Foreach 文件以重新打開“Foreach 循環(huán)編輯器”。

Ø       單擊“集合”。

Ø       在“集合”頁(yè)中，選擇“Foreach 文件枚舉器”。

Ø       在“枚舉器配置”組中，單擊“瀏覽”。

Ø       在“瀏覽文件夾”對(duì)話框中，找到d:"test。

Ø       在“文件”框中，鍵入*.txt。

Ø       單擊“變量映射”，將枚舉器映射為用戶定義的變量。

Ø       在“變量映射”頁(yè)的“變量”列中，單擊空單元格并選擇“<新建變量…>”。

Ø       在“添加變量”對(duì)話框中，為“名稱”鍵入varFileName。

Ø       單擊“確定”。

Ø       再次單擊“確定”，退出“Foreach 循環(huán)編輯器”對(duì)話框。

Ø       將數(shù)據(jù)流任務(wù)Import Data 數(shù)據(jù)流任務(wù)拖動(dòng)到現(xiàn)已重命名為Foreach File in Folder 的Foreach 循環(huán)容器中。

Ø       配置平面文件連接管理器以使用連接字符串的變量

Ø       在“連接管理器”窗格中，右鍵單擊Source Data，再選擇“屬性”。

Ø       在“屬性”窗口中，針對(duì)“表達(dá)式”，單擊空單元，然后單擊省略號(hào)按鈕“(…)”。

Ø       在“屬性表達(dá)式編輯器”對(duì)話框的“屬性”列中，鍵入或選擇ConnectionString。

Ø       在“表達(dá)式”列中，單擊省略號(hào)按鈕“(…)”以打開“表達(dá)式生成器”對(duì)話框。

Ø       在“表達(dá)式生成器”對(duì)話框中，展開“變量”節(jié)點(diǎn)。

Ø       將變量用戶::varFileName 拖到“表達(dá)式”框中。

Ø       單擊“確定”關(guān)閉“表達(dá)式生成器”對(duì)話框。

Ø       再次單擊“確定”關(guān)閉“屬性表達(dá)式編輯器”對(duì)話框。