SSIS關(guān)鍵元素的使用

1、SSIS關(guān)鍵元素的使用1第三講（2）在前面創(chuàng)建包的過程中，用到了任務(wù)、容器、數(shù)據(jù)源和目標(biāo)，以及轉(zhuǎn)換等SSIS元素。其中，任務(wù)有數(shù)據(jù)流任務(wù)和文件系統(tǒng)任務(wù)，它們分別完成數(shù)據(jù)流控制和將文件復(fù)制到備份文件夾；容器組件則用到了序列容器，作用是把數(shù)據(jù)流和文件系統(tǒng)任務(wù)打包成一個(gè)整體；數(shù)據(jù)源主要是OLE DB源，即數(shù)據(jù)流中的第一個(gè)組件，其作用是從AdventureWorks數(shù)據(jù)庫的“TransactionHistoryArchive”表中加載歸檔銷售事務(wù)數(shù)據(jù)。firstSSIS包中使用了4個(gè)轉(zhuǎn)換組件來完成數(shù)據(jù)流中數(shù)據(jù)的轉(zhuǎn)換，第1個(gè)是名為“計(jì)算每一項(xiàng)的總成本”的派生列轉(zhuǎn)換，它通過將每個(gè)銷售事務(wù)的成本與數(shù)量相乘并

2、將其結(jié)果存儲(chǔ)在創(chuàng)建的新列中；第2個(gè)是聚合轉(zhuǎn)換（匯總數(shù)量和成本），它按“ProductID”列對(duì)數(shù)據(jù)進(jìn)行分組，并且對(duì)于每一個(gè)“ProductID”都計(jì)算其“Quantity”列的和作為“QuantitySum”列，同時(shí)計(jì)算“LineItemTotalCost”列的和作為“TotalCostByID”列；2第3個(gè)轉(zhuǎn)換也是一個(gè)派生列轉(zhuǎn)換組件，其作用是計(jì)算平均成本并且存儲(chǔ)在一個(gè)新列中；最后，系統(tǒng)還用到了排序轉(zhuǎn)換，將計(jì)算結(jié)果按“ProductID”列進(jìn)行排序。另外還使用到了平面文件目標(biāo)和3個(gè)連接管理器。由此可見，只有這些組件的協(xié)同運(yùn)作，一個(gè)包才能順利地進(jìn)行，同時(shí)，也只有每個(gè)組件都在理解的基礎(chǔ)上應(yīng)用，才能

3、創(chuàng)建一個(gè)完成需要功能的包。3一、包包（package）是最重要的Integration Services對(duì)象。包是一個(gè)有組織的集合，其中可包括連接、控制流元素、數(shù)據(jù)流元素、事件處理程序、變量和配置。包有3種創(chuàng)建方式，包括使用SSIS導(dǎo)入和導(dǎo)出向?qū)А⑹褂肧SIS設(shè)計(jì)器及編程實(shí)現(xiàn)。包對(duì)象的主要功能是把其他SSIS對(duì)象組合成一個(gè)整體。首次創(chuàng)建包后，包是一個(gè)空對(duì)象，不能實(shí)現(xiàn)任何功能。若要向包添加功能，就需要向包添加對(duì)象，如控制流、數(shù)據(jù)流及相關(guān)的元素。包包控制流任務(wù)源轉(zhuǎn)換目標(biāo)數(shù)據(jù)流任務(wù)4二、容器容器、任務(wù)及優(yōu)先約束都屬于控制流元素。其中容器提供包中的結(jié)構(gòu)和服務(wù)，任務(wù)提供功能，優(yōu)先約束將任務(wù)和容器連接為已

4、排序的控制流。SSIS容器主要包括3類容器：Foreach循環(huán)容器：枚舉一個(gè)集合，并對(duì)該集合的每個(gè)成員重復(fù)其控制流。For循環(huán)容器：用于重復(fù)其控制流，直到指定表達(dá)式的計(jì)算結(jié)果為False為止。序列容器：在容器內(nèi)定義控制流的子集，并將任務(wù)和容器作為一個(gè)單元來管理。51、Foreach循環(huán)容器Foreach循環(huán)容器定義包中的重復(fù)控制流。循環(huán)的實(shí)現(xiàn)與編程語言中的FOREACH循環(huán)結(jié)構(gòu)類似，包將會(huì)重復(fù)執(zhí)行循環(huán)容器中枚舉器的每個(gè)成員。該示例的目的是把一個(gè)文件夾中的特定文件復(fù)制到備份文件夾中。（1）新建Integration Services 項(xiàng)目，名稱為“foreachdemo”。（2）在工具箱中選擇“

5、控制流項(xiàng)”標(biāo)簽中的“文件系統(tǒng)任務(wù)”組件。設(shè)置此文件系統(tǒng)任務(wù)的類型為“復(fù)制文件”。源連接指向任何一個(gè)文件。目標(biāo)連接指向要復(fù)制到的目標(biāo)文件夾?，F(xiàn)在這個(gè)文件系統(tǒng)任務(wù)只能完成單個(gè)文件的復(fù)制。67（3）添加“Foreach循環(huán)容器”組件，在彈出的快捷菜單中選擇“編輯”命令。（4）選擇“集合”選項(xiàng)，在“枚舉器配置”選項(xiàng)組中，單擊“瀏覽”按鈕，設(shè)置成包含需要備份文件的文件夾，為了發(fā)揮Foreach循環(huán)容器的枚舉功能，此文件夾中應(yīng)該有一系列的文件。（5）在“文件”文本框中，鍵入需要復(fù)制的文件屬性，例如若要復(fù)制全部的文本文件，可以輸入“*.txt”。（6）設(shè)置Foreach循環(huán)容器所枚舉的對(duì)象與任務(wù)組件之間的關(guān)

6、聯(lián)。選擇“變量映射”選項(xiàng)，添加新的變量copyfile，通過此變量實(shí)現(xiàn)容器所枚舉的所有對(duì)象與其他SSIS任務(wù)組件的關(guān)聯(lián)。（7）把“文件系統(tǒng)任務(wù)”組件拖動(dòng)到Foreach循環(huán)容器中。（8）設(shè)置source連接屬性窗口中的“表達(dá)式”，在“屬性表達(dá)式編輯器”窗口中屬性列中選擇“ConnectionString”，在彈出的“表達(dá)式生成器”窗口中將第5步設(shè)置的變量拖動(dòng)到“表達(dá)式”窗口中。（9）返回到SSIS設(shè)計(jì)器，執(zhí)行此包。源文件夾中的所有文件已經(jīng)備份到目標(biāo)文件夾中了。8第4步圖9第5步圖10第7步圖11Foreach循環(huán)容器回顧本例的制作過程，實(shí)現(xiàn)文件批量復(fù)制的最關(guān)鍵的地方在于將平面文件連接管理器的C

7、onnectionString屬性修改為在Foreach循環(huán)容器配置時(shí)設(shè)置的用戶定義的變量copyfile。運(yùn)行時(shí)，F(xiàn)oreach循環(huán)容器的每次迭代都將動(dòng)態(tài)更新copyfile變量，這樣會(huì)使連接管理器連接到不同的平面文件，從而逐一處理源文件夾中的文件。122、For循環(huán)容器For循環(huán)容器定義包中的重復(fù)控制流。循環(huán)每次重復(fù)時(shí)，F(xiàn)or循環(huán)容器都計(jì)算一個(gè)表示式并重復(fù)運(yùn)行其工作流，直到表達(dá)式計(jì)算結(jié)果為false。For循環(huán)容器需要三個(gè)定義的要素：為循環(huán)計(jì)數(shù)器賦值的可選初始化表達(dá)式包含用于測試循環(huán)應(yīng)停止還是繼續(xù)的表達(dá)式遞增或遞減循環(huán)計(jì)數(shù)器的可選迭代表達(dá)式132、For循環(huán)容器1、創(chuàng)建一個(gè)變量，選擇“SS

8、IS”“變量”。2、如圖設(shè)置For循環(huán)容器。3、在容器中添加一個(gè)需要重復(fù)執(zhí)行的任務(wù)。143、序列容器當(dāng)控制流過于龐大不容易管理時(shí)，可以把完成流某一項(xiàng)功能的操作作為一個(gè)大控制流的一部分，這就需要使用序列容器。序列容器將包分組到多個(gè)單獨(dú)的控制流中。使用序列容器輔助控制流有許多好處，比如可以禁用任務(wù)組方便調(diào)試。15三、任務(wù)任務(wù)是一些控制流元素，它定義包含控制流中執(zhí)行的工作單元。SQL Server Integration Service包由一個(gè)或多個(gè)任務(wù)組成。如果包中包含多個(gè)任務(wù)，則按照優(yōu)先約束在控制流中進(jìn)行連接和排序。161、大容量插入任務(wù)大容量插入任務(wù)為文本文件的大量數(shù)據(jù)復(fù)制到SQL Serve

9、r表或視圖提供了最為快速的方法。172、執(zhí)行SQL任務(wù)執(zhí)行SQL任務(wù)可以從包中運(yùn)行SQL語句或存儲(chǔ)過程。此任務(wù)可以包含單個(gè)SQL語句，也可以包含順序運(yùn)行的多個(gè)SQL語句。執(zhí)行SQL任務(wù)可以與Foreach循環(huán)容器和For循環(huán)容器一起組合使用，以運(yùn)行多個(gè)SQL語句或重復(fù)運(yùn)行執(zhí)行SQL任務(wù)。18四、創(chuàng)建一個(gè)完整的SSIS包該實(shí)例的背景是基于Adventure Works Cycle公司的，該公司新增了5個(gè)銷售區(qū)域，以前這5個(gè)區(qū)域的銷售數(shù)據(jù)沒有匯總到數(shù)據(jù)庫中，現(xiàn)在需要用到這些數(shù)據(jù)，于是公司讓這5個(gè)區(qū)域的主管把客戶信息全部導(dǎo)入到一個(gè)文本文件中，命名為customers.txt。幾個(gè)區(qū)域的數(shù)據(jù)完全混合在

10、一起，而且某些郵政編碼丟失前導(dǎo)零導(dǎo)致位數(shù)不夠，某些數(shù)據(jù)銷售區(qū)域數(shù)值無效。現(xiàn)在的任務(wù)就是要通過SSIS的數(shù)據(jù)提取、轉(zhuǎn)換和加載功能檢測并更正customers.txt不正確的郵政編碼，將具有無效銷售區(qū)域的數(shù)據(jù)行寫入一個(gè)特定的文件，并將有效數(shù)據(jù)行分區(qū)域?qū)氲?AdventureWorks 數(shù)據(jù)庫中的五個(gè)表。 19基本解決思路是，先創(chuàng)建5個(gè)SQL語句來創(chuàng)建5個(gè)表，每一個(gè)表對(duì)應(yīng)一個(gè)有效區(qū)域，使用Foreach循環(huán)容器遍歷保存在文件夾中的文本文件中的SQL語句。Customers.txt保存的數(shù)據(jù)使用條件分割轉(zhuǎn)換分別導(dǎo)入到對(duì)應(yīng)的5個(gè)數(shù)據(jù)表中，不正確的數(shù)據(jù)導(dǎo)入到一個(gè)新的文件中保存。實(shí)例中將用到各種任務(wù)、容器

11、、源、轉(zhuǎn)換、目標(biāo)和連接管理器。通過本例的學(xué)習(xí)，我們會(huì)對(duì)用SSIS完成數(shù)據(jù)的ETL操作有一個(gè)全方位的認(rèn)識(shí)。20（一）生成SSIS解決方案1、創(chuàng)建Integeration Services項(xiàng)目，項(xiàng)目名稱為alldemo。2、在SSIS設(shè)計(jì)界面窗口，把SSIS包文件夾下的Package.dtsx命名為alldemo.dtsx。21（二）設(shè)計(jì)控制流1、將Foreach循環(huán)容器組件拖到控制流編輯器中，命名為“Foreach循環(huán)運(yùn)行SQL語句”。循環(huán)編輯器的“集合”頁按下圖所示進(jìn)行設(shè)置，注意要把文件夾選項(xiàng)定位到此項(xiàng)目中包含創(chuàng)建表的SQL文件的文件夾中。22232、切換到變量映射頁，在變量下拉列表中選擇“新

12、建變量”選項(xiàng)，進(jìn)行如圖所示的設(shè)置。這一步創(chuàng)建了一個(gè)包級(jí)別的用戶變量vfileName。243、在連接管理其中新建一個(gè)文件連接，命名為CreateTableSQL。注意這里先把文件名指向其中一個(gè)創(chuàng)建表的SQL文件，后面會(huì)把變量參數(shù)傳遞給這個(gè)文件連接。4、修改這個(gè)文件連接CreateTableSQL的屬性，將其“表達(dá)式”屬性中的ConnectionString修改為vfileName變量。255、按照需求，需要在循環(huán)中加入一個(gè)創(chuàng)建SQL的組件，故在工具箱中選擇“執(zhí)行SQL任務(wù)”組件拖動(dòng)到Foreach循環(huán)容器中，命名為“創(chuàng)建表”。然后按照如下圖所示進(jìn)行設(shè)置。其中“執(zhí)行SQL任務(wù)編輯器”中的常規(guī)頁面

13、的Connection屬性要?jiǎng)?chuàng)建一個(gè)連接指向AdventureWorks數(shù)據(jù)庫；SQLSourceType為選擇“文件連接”；FileConnection指向CreateTableSQL文件連接。 26276、選擇“數(shù)據(jù)流任務(wù)”組件，拖動(dòng)到控制流設(shè)計(jì)器重，命名為“數(shù)據(jù)清洗和加載”，同時(shí)把優(yōu)先約束從Foreach循環(huán)指向這個(gè)數(shù)據(jù)流組件。28（三）設(shè)計(jì)數(shù)據(jù)連接此項(xiàng)目最核心的任務(wù)是對(duì)文本文件數(shù)據(jù)源的處理，因此還要?jiǎng)?chuàng)建一個(gè)連接指向Customers.txt。1、在連接管理器中創(chuàng)建一個(gè)平面文件連接。由于Customers.txt文件數(shù)據(jù)第一行是字段名，要在編輯器中選中“在第一個(gè)數(shù)據(jù)行中顯示列名稱”選項(xiàng)，

14、如下圖所示。29302、在“平面連接管理器”窗口中切換到“高級(jí)”頁面配置各列的屬性。這里可以設(shè)置各列的名稱和長度等屬性。此處應(yīng)該按照前面創(chuàng)建表的SQL語句設(shè)置數(shù)據(jù)的字段類型，否則在后面數(shù)據(jù)導(dǎo)入的時(shí)候會(huì)由于字段類型的不匹配而發(fā)生錯(cuò)誤。另外，因?yàn)樾枰４鏌o效的客戶數(shù)據(jù)，還要?jiǎng)?chuàng)建一個(gè)連接指向保存這些數(shù)據(jù)的文本文件，將此連接命名為CustomersWithInvalidTerrritoryID。313233（四）設(shè)計(jì)數(shù)據(jù)流雙擊控制流中的“數(shù)據(jù)清洗和加載”數(shù)據(jù)流任務(wù) 組件，切換到數(shù)據(jù)流編輯器。1、把“平面文件源”組件拖動(dòng)到數(shù)據(jù)流編輯器中，改名為“數(shù)據(jù)提取”，按下圖所示進(jìn)行設(shè)置。 342、選擇“條件性拆分

15、”組件，命名為“按照TerritoryID拆分?jǐn)?shù)據(jù)”，并將數(shù)據(jù)流從上一步創(chuàng)建的“數(shù)據(jù)提取”文件源中指向這個(gè)條件性拆分組件。3、雙擊條件性拆分組件，在“條件性拆分轉(zhuǎn)換編輯器”中，展開左上角的列對(duì)象，把Territory字段拖動(dòng)到網(wǎng)格中的“條件”列，同時(shí)編輯“條件”列表達(dá)式為“Territory=1”。為了使操作的結(jié)果更直觀，可以把輸出名稱改為“區(qū)域1”。其他的條件列可以進(jìn)行相應(yīng)的設(shè)置。默認(rèn)輸出名稱改為“無效區(qū)域”。最終結(jié)果如下圖所示。35364、拖動(dòng)4個(gè)“OLE DB目標(biāo)”組件到數(shù)據(jù)流編輯器中作為條件拆分后數(shù)據(jù)的歸宿目標(biāo)。把數(shù)據(jù)流從條件拆分組件連接向數(shù)據(jù)目標(biāo)時(shí)，會(huì)彈出“選擇輸入輸出”對(duì)話框。選擇

16、需要的輸出，數(shù)據(jù)流就創(chuàng)建好了。375、切換到控制流執(zhí)行容器，在數(shù)據(jù)庫中創(chuàng)建表。編輯OLE DB目標(biāo)，使其指向前面用SQL語句創(chuàng)建的目標(biāo)表。如下圖所示。注意：如果前面對(duì)Customers數(shù)據(jù)連接的設(shè)置中數(shù)據(jù)類型沒有設(shè)置正確描繪出現(xiàn)錯(cuò)誤提示：列“FirstName”無法在 unicode 和非 unicode 字符串?dāng)?shù)據(jù)類型之間轉(zhuǎn)換。 返回Customers數(shù)據(jù)連接，重新編輯其字段的屬性即可。38396、設(shè)置無效區(qū)域目標(biāo)文件，指向CustomersWithInvalidTerrritoryID平面文件連接。40417、在條件拆分中還有區(qū)域2的數(shù)據(jù)沒有目標(biāo)宿主，這是因?yàn)殇N售區(qū)域2在數(shù)據(jù)錄入的時(shí)候郵編

17、本應(yīng)該是5位，而有些數(shù)據(jù)由于省略了前面的0只輸入了4位數(shù)字，因此還需要對(duì)郵編進(jìn)行清洗操作才能導(dǎo)入到表中。選擇“派生列”組件，按下圖進(jìn)行設(shè)置。在表達(dá)式中輸入代碼： LEN(PostalCode) = 4 ? 0 + PostalCode : PostalCode ，含義是如果PostalCode字段的長度為4，則在前面加0，否則直接使用PostalCode作為目標(biāo)列的值。通過這種方法對(duì)數(shù)據(jù)進(jìn)行了有效的清洗。42438、將區(qū)域2的目標(biāo)添加到數(shù)據(jù)流設(shè)計(jì)器中，并與派生列組件連接起來。4445五、包的部署要使包能在不同的環(huán)境下完成ETL的功能，必須把包含包的項(xiàng)目生成部署實(shí)用工具，然后安裝包安裝向?qū)惭b到文件系統(tǒng)或