SSIS高級(jí)轉(zhuǎn)換任務(wù)—OLE DB命令

OLE DB Commond任務(wù)接受輸入流中的行數(shù)據(jù)并執(zhí)行一段SQL語(yǔ)句或自定義存儲(chǔ)過程。這個(gè)轉(zhuǎn)換和Execute Sql任務(wù)容易混淆，但是它非常類似于在ADO連接中循環(huán)接收一個(gè)結(jié)果集中的數(shù)據(jù)，創(chuàng)建，執(zhí)行一個(gè)ADO命令。輸入流中的參數(shù)數(shù)據(jù)可以提供給一個(gè)Sql命令或者存儲(chǔ)過程。這里我們看到“循環(huán)”可能就會(huì)聯(lián)想到另外一個(gè)詞“性能”。這可能涉及到更新，插入，刪除語(yǔ)句，每次都會(huì)有斷開，連接的操作。但是并不意味著這個(gè)轉(zhuǎn)換一無(wú)是處，這里我們著重理解這個(gè)任務(wù)如何配置和使用的。要注意有多少輸入數(shù)據(jù)并在需要緩存數(shù)據(jù)到臨時(shí)表或?qū)嶋H表時(shí)權(quán)衡性能和伸縮性，以達(dá)到最優(yōu)效果。

在配置這個(gè)任務(wù)的時(shí)候，基本點(diǎn)要點(diǎn)是設(shè)置能夠獲得SQL語(yǔ)句的連接，提供可以執(zhí)行的SQL語(yǔ)句，設(shè)置輸入流中的數(shù)據(jù)和SQL語(yǔ)句參數(shù)的映射關(guān)系。打開編輯界面，這個(gè)任務(wù)的編輯界面有4個(gè)標(biāo)簽，并使用高級(jí)編輯：

1. 連接管理:設(shè)置OLE DB連接，通過這個(gè)連接獲得要執(zhí)行的SQL語(yǔ)句，要注意輸入流中的參數(shù)數(shù)據(jù)并不一來(lái)自這個(gè)源
2. 組件屬性:在這個(gè)標(biāo)簽內(nèi)，SQLCommand屬性設(shè)置SQL語(yǔ)句，CommandTimeOut屬性設(shè)置連接失效時(shí)間，這點(diǎn)和ADO連接對(duì)象的工作原理是一樣的，設(shè)置為0表示沒有失效時(shí)間。在這個(gè)標(biāo)簽內(nèi)還可以設(shè)置任務(wù)名字和描述
3. 列映射:這個(gè)標(biāo)簽顯示輸入流中的列和目標(biāo)列，即SQL語(yǔ)句中的參數(shù)名字。使用鼠標(biāo)拖拽的方法將他們連接起來(lái)。他們是一一對(duì)應(yīng)的，假設(shè)語(yǔ)句中的參數(shù)要比輸入流中的多，需要使用派生列轉(zhuǎn)換產(chǎn)生多余的列
4. 輸入和輸出屬性:多數(shù)時(shí)候在列映射標(biāo)簽內(nèi)可以完成輸出列設(shè)置，但是如果OLE DB數(shù)據(jù)源不支持派生參數(shù)，需要在這里使用ParametersNames和DBParamInfoFlags屬性設(shè)置輸出列

學(xué)習(xí)這個(gè)轉(zhuǎn)換任務(wù)的最好方法是操作一個(gè)例子。這個(gè)例子假設(shè)要驗(yàn)證每天的存款數(shù)據(jù)并在數(shù)據(jù)庫(kù)中創(chuàng)建存款項(xiàng)。您還必須為每個(gè)客戶建立支付交易，以便會(huì)計(jì)核算人員通過軟件來(lái)審查。在這個(gè)例子中我們不需要知道如何建立存款項(xiàng)和支付交易，我們只是建立兩個(gè)空的存儲(chǔ)過程來(lái)完成這項(xiàng)工作。

1. 創(chuàng)建一個(gè)package 命名為OLE DB Command，添加一個(gè)Data Flow tesk，雙擊進(jìn)入Control Flow
2. 新建一個(gè)文本文件，并保存到c:\ole db eft data.txt，文件內(nèi)容如下:

       CustomerID,DepositAmt,DepositDate,Invoice

       XY-111-222,$100.00,07/13/2005,222-063105

       XX-Z11-232,$1000.00,07/13/2005,232-063105

       XX-Y88-233,$555.00,07/13/2005,233-053105

3. 運(yùn)行下面的SQL語(yǔ)句，在數(shù)據(jù)庫(kù)AdventureWorks中創(chuàng)建兩個(gè)空的存儲(chǔ)過程

       USE ADVENTUREWORKS

       GO

       CREATE PROC usp_DepositTrans_Add (

            @CUSTOMERID varchar(10),

            @DEPOSITAMT money,

            @DEPOSITDATE smalldatetime,

            @INVOICE varchar(15))

       AS

     ---一個(gè)空的存儲(chǔ)過程只是為了做實(shí)例

       GO

       CREATE PROC usp_PaymentTrans_Add (

            @CUSTOMERID varchar(10),

            @DEPOSITAMT money,

            @DEPOSITDATE smalldatetime,

            @INVOICE varchar(15))

       AS

     --一個(gè)空的存儲(chǔ)過程只是為了做實(shí)例

4. 添加一個(gè)Flat File Source，連接到c:\ole db eft data.txt
5. 在Data Flow設(shè)計(jì)界面內(nèi)拖放一個(gè)OLE DB Command task，將Flat File Source和它連接起來(lái)
6. 打開OLE DB Command的編輯界面，在Connection Manager設(shè)置連接到數(shù)據(jù)庫(kù)AdventureWorks。在Component Properties標(biāo)簽界面內(nèi)，設(shè)置SQLCommand屬性為usp_DepositTrans_Add ?, ?, ?, ?。這里?表示一個(gè)輸入?yún)?shù)。最后的界面如圖1
   
   圖1
7. 在Column Mapping標(biāo)簽內(nèi)將輸入列和對(duì)應(yīng)的參數(shù)連接起來(lái)，如圖2
   
   圖2
8. 再添加一個(gè)OLE DB Command，將第一個(gè)和第二個(gè)連接起來(lái)，配置和第一個(gè)類似，但在這里SQL Commond屬性設(shè)置為usp_PaymentTrans_Add ?, ?, ?, ?
   運(yùn)行這個(gè)package，文本文件中的三行數(shù)據(jù)作為這兩個(gè)存儲(chǔ)過程的參數(shù)輸入執(zhí)行。如果存儲(chǔ)過程是真實(shí)存在的，就會(huì)創(chuàng)建三個(gè)存款項(xiàng)和三個(gè)交易。這里我們也可以看到這里可以重復(fù)使用這三個(gè)存儲(chǔ)過程，只要提供適當(dāng)?shù)膮?shù)。在這個(gè)例子中，可以重復(fù)使用現(xiàn)有的邏輯，將一批數(shù)據(jù)一次輸入到package中執(zhí)行。