這個(gè)例子的情景是一個(gè)信用卡公司,，目前正著手于拓展Florida州新成立的一些公司的業(yè)務(wù)。市場(chǎng)部門(mén)每周都會(huì)向這些公司發(fā)送一些郵件,，我們要為所有的郵件準(zhǔn)備抽取數(shù)據(jù)。假設(shè)Florida州提供的一個(gè)上面這個(gè)dat文件,，它是從老的計(jì)算機(jī)系統(tǒng)里面得到的,，它是定長(zhǎng)分隔的，這意味著文件中沒(méi)有分隔符,，必須手工設(shè)置分隔列的長(zhǎng)度,。從下面的連接下載這個(gè).dat文件：010305c.dat。如果使用工具查看,，它們的模樣類(lèi)似下面的：

01  ANNUAL_MICRO_DATA_REC. 
    03  ANNUAL_COR_NUMBER                       PIC X(12). 
    03  ANNUAL_COR_NAME                         PIC X(48). 
    03  ANNUAL_COR_STATUS                       PIC X(01). 
    03  ANNUAL_COR_FILING_TYPE                  PIC X(15). 
    03  ANNUAL_COR_2ND_MAIL_ADD_1               PIC X(42). 
    03  ANNUAL_COR_2ND_MAIL_ADD_2               PIC X(42). 
    03  ANNUAL_COR_2ND_MAIL_CITY                PIC X(28). 
    03  ANNUAL_COR_2ND_MAIL_STATE               PIC X(02). 
    03  ANNUAL_COR_2ND_MAIL_ZIP                 PIC X(10). 
    03  ANNUAL_COR_2ND_MAIL_COUNTRY             PIC X(02). 
    03  ANNUAL_COR_FILE_DATE                    PIC X(08). 
    03  ANNUAL_COR_FEI_NUMBER                   PIC X(14). 
    03  ANNUAL_MORE_THAN_SIX_OFF_FLAG           PIC X(01). 
    03  ANNUAL_LAST_TRX_DATE                    PIC X(08). 
    03  ANNUAL_STATE_COUNTRY                    PIC X(02). 
    03  ANNUAL_REPORT_YEAR_1                    PIC X(04). 
    03  ANNUAL_HOUSE_FLAG_1                     PIC X(01). 
    03  ANNUAL_REPORT_DATE_1                    PIC X(08). 
    03  ANNUAL_REPORT_YEAR_2                    PIC X(04). 
    03  ANNUAL_HOUSE_FLAG_2                     PIC X(01). 
    03  ANNUAL_REPORT_DATE_2                    PIC X(08). 
    03  ANNUAL_REPORT_YEAR_3                    PIC X(04). 
    03  ANNUAL_HOUSE_FLAG_3                     PIC X(01). 
    03  ANNUAL_REPORT_DATE_3                    PIC X(08). 
    03  ANNUAL_RA_NAME                          PIC X(42). 
    03  ANNUAL_RA_NAME_TYPE                     PIC X(01). 
    03  ANNUAL_RA_ADD_1                         PIC X(42). 
    03  ANNUAL_RA_CITY                          PIC X(28). 
    03  ANNUAL_RA_STATE                         PIC X(02). 
    03  ANNUAL_RA_ZIP5                          PIC X(05). 
    03  ANNUAL_RA_ZIP4                          PIC X(04). 
    03  ANNUAL_PRINCIPALS                       OCCURS 6 TIMES. 
    05  ANNUAL_PRINC_TITLE                      PIC X(04). 
    05  ANNUAL_PRINC_NAME_TYPE                  PIC X(01). 
    05  ANNUAL_PRINC_NAME                       PIC X(42). 
    05  ANNUAL_PRINC_ADD_1                      PIC X(42). 
    05  ANNUAL_PRINC_CITY                       PIC X(28). 
    05  ANNUAL_PRINC_STATE                      PIC X(02). 
    05  ANNUAL_PRINC_ZIP5                       PIC X(05). 
    05  ANNUAL_PRINC_ZIP4                       PIC X(04). 
    03  FILLER                                  PIC X(04). 

創(chuàng)建數(shù)據(jù)源

這個(gè)文件的內(nèi)容看上去不知所云,，不可能像普通的文本文件一樣處理它們,。下面要建一個(gè)package來(lái)清洗和這個(gè)類(lèi)似的數(shù)據(jù),，得到有用的信息。package完成下面的任務(wù)：

從010305c.dat的存放路徑下將文件內(nèi)容抽取出來(lái)存放到本地?cái)?shù)據(jù)庫(kù)

將文件歸檔避免多次下載

當(dāng)一列數(shù)據(jù)丟失,，這一列需要自動(dòng)重新添加

當(dāng)一行數(shù)據(jù)丟失,，需要輸出錯(cuò)誤的行

新添加一個(gè)Package，重命名為CorporationLoad.dtsx,，右擊Connection Managers選擇新添加一個(gè)連接,，選擇AdventureWorks。創(chuàng)建一個(gè)新的Flat File Connection連接,，重命名為Corporation Extract連接到010305c.dat,。這里不需要設(shè)置分隔符，而是選擇定長(zhǎng)格式,，也不需要設(shè)置列分隔符,，也沒(méi)有必要選擇第一行設(shè)置為列名選擇項(xiàng)。

定長(zhǎng)格式文件意味著每一列不是由分隔符來(lái)分隔,，必須手動(dòng)設(shè)置每一列的開(kāi)始和結(jié)束,。大多數(shù)的大型機(jī)文件都是這種格式，你會(huì)發(fā)現(xiàn)這種設(shè)置會(huì)有些繁瑣。打開(kāi)文件界面并推斷每一列的開(kāi)始位置,。點(diǎn)擊Column標(biāo)簽界面如圖5-6,。在Row Widh欄內(nèi)輸入1172字符（這個(gè)數(shù)字表示文件的開(kāi)始）。

圖5-6

下一步,，在列上設(shè)置豎直線標(biāo)示每一列,。在標(biāo)尺刻度上左擊設(shè)置豎直線，在這個(gè)例子中,，可以使用下面的表中的提示來(lái)設(shè)置列,。在豎直線上雙擊可以刪除，左擊拖動(dòng)可以移動(dòng)豎直線,。

在這個(gè)表中可以看到丟棄了大部分?jǐn)?shù)據(jù),。添加完豎直線之后，點(diǎn)擊Advanced標(biāo)簽界面,，點(diǎn)擊Suggest Types,，在Suggest Column Types對(duì)話框中接受默認(rèn)設(shè)置，點(diǎn)擊OK,。默認(rèn)數(shù)據(jù)類(lèi)型設(shè)置會(huì)滿足大部分的數(shù)據(jù)類(lèi)型需要,，但是也會(huì)有一些錯(cuò)誤。在Column 8（ZipCode column）需要修改DataType選擇項(xiàng)為String[DT_STR],，OutputColumnWidth設(shè)置為10,。最后修改Column 10為String[DT_STR]，OutputColumnWidth保持默認(rèn),，點(diǎn)擊OK保存設(shè)置,。

創(chuàng)建數(shù)據(jù)流

在Control Flow 界面內(nèi)拖放一個(gè)Data Flow task，雙擊進(jìn)入數(shù)據(jù)流標(biāo)簽界面,。在界面中拖放一個(gè)Flat File數(shù)據(jù)源重命名為Uncleansed Corporate Data,，雙擊并選擇上文中新建的數(shù)據(jù)連接，點(diǎn)擊進(jìn)入Columns標(biāo)簽界面反選Column 11和Column 12,，這意味著市場(chǎng)部門(mén)不需要這兩列數(shù)據(jù),。在后面的工作中將添加Destination和數(shù)據(jù)轉(zhuǎn)換任務(wù)。

處理臟數(shù)據(jù)

在進(jìn)行下一步操作之前,，先暫停來(lái)了解一下數(shù)據(jù),。我們創(chuàng)建了數(shù)據(jù)連接，你可能會(huì)注意到有一些數(shù)據(jù)行是空白的 ,，例如city和state的一些記錄是沒(méi)有的,。為了解決這個(gè)問(wèn)題，需要使用一些任務(wù)將規(guī)范的的數(shù)據(jù)送到一個(gè)路徑,，有缺損的數(shù)據(jù)送到另一個(gè)路徑,。然后嘗試清洗壞的數(shù)據(jù)并送回到主要路徑中。也可能有一些不能清洗的數(shù)據(jù)，需要寫(xiě)入錯(cuò)誤日志,。

首先,，設(shè)置郵政編碼為5位字符，一些包含破折號(hào)的有10位字符,，還有9位的,。使用Derived Column轉(zhuǎn)換來(lái)標(biāo)準(zhǔn)化，從工具欄中拖放一個(gè)Derived Column數(shù)據(jù)轉(zhuǎn)換任務(wù)重命名為Standardize Zip Code,。

使用箭頭連線將Load Corporate Data和Standardize Zip Code連接起來(lái),，雙擊Standardize Zip Code打開(kāi)編輯界面，展開(kāi)左邊欄中的Column樹(shù)形結(jié)構(gòu),，點(diǎn)擊Column 8拖放到下方的表格內(nèi),，這里會(huì)在表格內(nèi)預(yù)先填入一些信息。為了輸出5位郵政編碼需要編寫(xiě)一個(gè)表達(dá)式只截取5位,。使用SUBSTRING函數(shù)可以實(shí)現(xiàn)這種功能,，代碼如下：

SUBSTRING([Column 8],1,5)

在表格Expression列中輸入上面代碼，在Derived列中選擇replace the existing Column 8,，最后可以看到界面如圖5-7,，完成編輯后點(diǎn)擊OK退出界面。

圖5-7

用Conditional Split進(jìn)行數(shù)據(jù)轉(zhuǎn)換

現(xiàn)在數(shù)據(jù)規(guī)范化了,，從工具欄中拖放一個(gè)Conditional Split數(shù)據(jù)轉(zhuǎn)換任務(wù),，使用箭頭連線把它和Standardize Zip Code連接起來(lái)，將Conditional Split重命名為Find Bad Record,。Conditional Split將把一些不符合要求的數(shù)據(jù)進(jìn)行清洗,。

為了去掉沒(méi)有city或state的數(shù)據(jù)行，需要編寫(xiě)條件將缺失city或state的數(shù)據(jù)轉(zhuǎn)移到一個(gè)數(shù)據(jù)流,。雙擊Find Bad Record打開(kāi)編輯界面,，新建一個(gè)Missing State or City條件,，在Output Name列內(nèi)輸入該名字,。編寫(xiě)一個(gè)表達(dá)式來(lái)查找空的記錄。一種方法是使用LTRIM函數(shù),。兩個(gè)豎線|用來(lái)實(shí)現(xiàn)邏輯或,。下面的代碼用來(lái)查找Column 6和Column7。

LTRIM([Column 6]) == "" || LTRIM([Column 7]) == ""

最后要給不滿足條件的數(shù)據(jù)命名,。這里不滿足上述條件的數(shù)據(jù)命名為Good Data,，如圖5-8

圖5-8

使用Look Up轉(zhuǎn)換數(shù)據(jù)

從工具欄中拖放LookUp數(shù)據(jù)轉(zhuǎn)換重命名為Fix Bad Records，當(dāng)你把它和上一個(gè)數(shù)據(jù)轉(zhuǎn)換Find Bad Record連接起來(lái)的時(shí)候,，將會(huì)彈出Input Output Selection對(duì)話框如圖5-9,。下拉列表框中選擇Missing State or City選擇項(xiàng)，點(diǎn)擊OK。這將有缺損的數(shù)據(jù)從Find Bad Record中送出,。

圖5-9

LookUp轉(zhuǎn)換可以根據(jù)數(shù)據(jù)庫(kù)中ZipCode表中的數(shù)據(jù)來(lái)補(bǔ)全數(shù)據(jù)行中缺失的city和state,。雙擊打開(kāi)編輯界面，點(diǎn)擊Connection標(biāo)簽界面,，選擇AdventureWorks數(shù)據(jù)源和ZipCode數(shù)據(jù)表,。點(diǎn)擊Columns標(biāo)簽界面，點(diǎn)擊Column 8不放拖放到右邊ZipCode列上,，這樣在兩邊的表上建立一個(gè)連線如圖5-10,。然后再右邊表中選中State和City列，這兩列會(huì)出現(xiàn)在下方的表格中,，ZipName會(huì)替換Column 6,，State會(huì)替換Column 7 如圖5-10。點(diǎn)擊OK退出編輯界面,。

圖5-10

使用Union All合并

現(xiàn)在數(shù)據(jù)被清洗,，要使用Union All轉(zhuǎn)換將清洗后的數(shù)據(jù)送回到主要數(shù)據(jù)流中。在工具欄中拖放一個(gè)Union All轉(zhuǎn)換,，從Fix Bad Records向Union All拖放一個(gè)連線,，從Find Bad Records向Union拖放一個(gè)連線，Union All不再需要其他配置,。

最后設(shè)置

最后需要將數(shù)據(jù)流送到一個(gè)OLE DB 目的中,。從工具欄中拖放一個(gè)OLE DB Destination，重命名為Mail Merge Table,。從Union All向它拖放一個(gè)連線,，雙擊選擇AdventureWorks數(shù)據(jù)源，Use a Table or View選擇項(xiàng)中點(diǎn)擊New button,。默認(rèn)的建表語(yǔ)句使用的是表名是Mail Merge Table,，數(shù)據(jù)類(lèi)型可能有些不是很合適的，代碼如下：

CREATE TABLE [Mail Merge Table] ( 
    [Column 0] VARCHAR(12), 
    [Column 1] VARCHAR(48), 
    [Column 2] VARCHAR(1), 
    [Column 3] VARCHAR(4), 
    [Column 4] VARCHAR(53), 
    [Column 5] VARCHAR(42), 
    [Column 6] VARCHAR(28), 
    [Column 7] VARCHAR(2), 
    [Column 8] VARCHAR(10), 
    [Column 9] VARCHAR(2), 
    [Column 10] VARCHAR(10) 
) 

修改代碼中的表名和列名,，修改后的代碼如下：

CREATE TABLE MarketingCorporation( 
    CorporateNumber varchar(12), 
    CorporationName varchar(48), 
    FilingStatus char(1), 
    FilingType char(4), 
    AddressLine1 varchar(53), 
    AddressLine2 varchar(42), 
    City varchar(28), 
    State char(2), 
    ZipCode varchar(10), 
    Country char(2), 
    FilingDate varchar(50) NULL 
) 

由于列名是不同的需要點(diǎn)擊Mapping標(biāo)簽將列對(duì)應(yīng)起來(lái),。

處理更多的臟數(shù)據(jù)

這個(gè)Package基本上完成了，但是這里有一個(gè)致命的缺陷,。010305c.dat這個(gè)文件中有一些多余的數(shù)據(jù),，在Find Bad Records和Fix Bad Records之間添加一個(gè)data viewer可以查看這些多余的數(shù)據(jù)。

這樣可以查看在010305c.dat文件中有4條數(shù)據(jù)被Fix Bad Records處理,，只有2條被清洗,。另外2條不能被定為到Fix Bad Records中。在這個(gè)Package的需求中有一條是為市場(chǎng)部門(mén)提供一份地址列表用在郵件內(nèi)容中,。下圖5-11中顯示了錯(cuò)誤所在,。

圖5-11

在輸出界面中你可以看到如下的錯(cuò)誤信息：

Error: 0xC020901E at Load Corporate Data, Fix Bad Records [87]: Row yielded no

match

during lookup.

Error: 0xC0209029 at Load Corporate Data, Fix Bad Records [87]: The "component "Fix

Bad

Records" (87)" failed because error code 0xC020901E occurred, and the error row

disposition on "output "Lookup Output" (89)" specifies failure on error. An error

occurred on the specified object of the specified component.

Error: 0xC0047022 at Load Corporate Data, DTS.Pipeline: The ProcessInput method on

component "Fix Bad Records" (87) failed with error code 0xC0209029. The identified

component returned an error from the ProcessInput method. The error is specific to

the

component, but the error is fatal and will cause the Data Flow task to stop

running.

Error: 0xC0047021 at Load Corporate Data, DTS.Pipeline: Thread "WorkThread0" has

exited

with error code 0xC0209029.

Error: 0xC0047039 at Load Corporate Data, DTS.Pipeline: Thread "WorkThread1"

received a

shutdown signal and is terminating. The user requested a shutdown, or an error in

another

thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Load Corporate Data, DTS.Pipeline: Thread "WorkThread1" has

exited

with error code 0xC0047039.

不能因?yàn)檫@些錯(cuò)誤提示而放棄這個(gè)Package,，需要將錯(cuò)誤信息輸入到錯(cuò)誤消息隊(duì)列中以備查看。需要?jiǎng)?chuàng)建一個(gè)ErrorQueue表,，從工具欄中拖放一個(gè)Audit轉(zhuǎn)換,，重命名為Add Auditing Info，從Fix Bad Records中拖拽紅色箭頭連線連接到Add Auditing Info,。

如圖5-12,，可以看到Configure Error Output對(duì)話框，在這個(gè)對(duì)話框中配置如果錯(cuò)誤出現(xiàn)時(shí)SSIS如何反應(yīng),。Truncation列表明如果一行太長(zhǎng)而不能加入到轉(zhuǎn)換中時(shí)所作的反應(yīng),。Error列表明遇到轉(zhuǎn)換錯(cuò)誤時(shí)如何反應(yīng)。在Description列中可以看到期望捕獲的錯(cuò)誤,。例如對(duì)于Lookup轉(zhuǎn)換,，需要捕獲的錯(cuò)誤是lookup failure，意思是lookup轉(zhuǎn)換不能找到對(duì)應(yīng)的輸入,。在這個(gè)例子中選擇錯(cuò)誤類(lèi)型如圖5-10,。默認(rèn)情況會(huì)使任務(wù)失敗，結(jié)果會(huì)使整個(gè)Package失敗,。也可以從下拉列表中選擇完全忽略錯(cuò)誤,。

圖5-10

完成配置之后點(diǎn)擊OK退出界面。

錯(cuò)誤處理之后,，雙擊Audit transform打開(kāi)編輯界面,，添加兩列。繼續(xù)添加兩列,，在Audit Type列中選擇Task Name和Package Name,，Output Column Name默認(rèn)同名，去掉名字中的空格,，如圖5-13,。由于可能有多個(gè)Package向表中寫(xiě)入數(shù)據(jù)，所以這些信息是必須的,。

圖5-13

最后的工作是將臟數(shù)據(jù)送入到SQL Server中的ErrorQueue表中,，從工具欄中拖放另外一個(gè)OLE DB目的，重命名為Error Queue,，雙擊選擇AdventureWorks數(shù)據(jù)源,，點(diǎn)擊New新添加一個(gè)表,，重命名表名ErrorQueue,，代碼如下：

CREATE TABLE ErrorQueue( 
    CorporateNumber varchar(12), 
    CorporationName varchar(48), 
    FilingStatus char(1), 
    FilingType char(4), 
    AddressLine1 varchar(53), 
    AddressLine2 varchar(42), 
    City varchar(28), 
    State char(2), 
    ZipCode varchar(10), 
    Country char(2), 
    FilingDate varchar(10) NULL, 
    ErrorCode INT, 
    ErrorColumn INT, 
    TaskName NVARCHAR(19), 
    PackageName NVARCHAR(30) 
) 

注意：可以看到這個(gè)表中的信息是很籠統(tǒng)的。

這次需要點(diǎn)擊mapping將列一一對(duì)應(yīng)起來(lái),，點(diǎn)擊OK退出編輯界面?，F(xiàn)在可以執(zhí)行這個(gè)Package了,，4條記錄被清洗，2條送到error queue,。執(zhí)行成功之后的界面如圖5-14,。

圖5-14

原文鏈接：http://www.cnblogs.com/tylerdonet/archive/2011/04/22/2023843.html