SSIS高級轉(zhuǎn)換任務(wù)—執(zhí)行SQL語句

下面的隨筆中將講述SSIS中的高級轉(zhuǎn)換任務(wù)，和老舊的SQL Server 2000 DTS相比，我們會發(fā)現(xiàn)現(xiàn)在以前的dark-arrow，data-pump任務(wù)沒有了。在轉(zhuǎn)換任務(wù)中隱藏ActiveX腳本和嵌入連接字符的方法也被去除了。在將Package指向不同的數(shù)據(jù)庫的時候也不會忘記修改轉(zhuǎn)換對象，在修改對象連接的時候也一樣。現(xiàn)在新建全局連接。轉(zhuǎn)換任務(wù)可以被更加容易的管理，使用便捷界面可以瀏覽任務(wù)的設(shè)計界面并修改屬性。

在使用SSIS package的時候，有些時候需要避免一些過度依賴的情況。沒有必要在package中使用ActiveX腳本任務(wù)暴力的解決一些很復(fù)雜問題。事實上如果一開始就過度依賴腳本任務(wù)，就需要停下來想一想，有沒有可以替代的任務(wù)。SSIS中有許多任務(wù)可以解決譬如去掉平面文件中的無用數(shù)據(jù)，分隔數(shù)據(jù)輸入內(nèi)容，循環(huán)執(zhí)行幾個任務(wù)。我們還是可能遇到SSIS不能解決的問題，如果遇到了就需要使用靈活的.net腳本任務(wù)來解決。

這里我們將使用這些新的工具來解決生產(chǎn)環(huán)境的遇到的問題，我們需要重新來設(shè)置存儲過程的輸出參數(shù)嗎？根據(jù)行數(shù)來有條件地設(shè)置分支？使用模糊邏輯任務(wù)來代替在表中查找數(shù)據(jù)如何？我們將看到更加復(fù)雜的轉(zhuǎn)換任務(wù)。假定大家已經(jīng)熟悉了BIDS的常見環(huán)境設(shè)置，所以在這下面的隨筆中我們將不再詳細(xì)解釋每一個例子的具體操作步驟，而只說明重要的屬性設(shè)置。

　　

執(zhí)行SQL語句任務(wù)

執(zhí)行SQL任務(wù)算不上是一種高級的SSIS任務(wù)，但是在SQL Server 2000中它是一種最常用的任務(wù)。可以把它作為一個出發(fā)點來學(xué)習(xí)變量，表達(dá)式和其他SSIS中的高級屬性設(shè)置。

在做一些測試的時候經(jīng)常使用執(zhí)行SQL任務(wù)常用來刪除存儲表中的數(shù)據(jù)。另一種用來調(diào)用存儲過程來完成DTS中不能完成的任務(wù)。SSIS還提供一種的工具，現(xiàn)在使用配置管理可以編輯存儲過程的需要使用的參數(shù)數(shù)據(jù)，或者保存輸出參數(shù)數(shù)據(jù)。

新建一個Package，在Control Flow中拖放一個執(zhí)行SQL任務(wù)。這個任務(wù)會顯示一個紅色的標(biāo)志，一個錯誤信息顯示提示這個任務(wù)沒有連接到一個數(shù)據(jù)連接上。為了解決這個錯誤，雙擊打開編輯界面如圖6-1。

圖6-1

這個任務(wù)有四個標(biāo)簽界面：

• General：設(shè)置任務(wù)的名字，描述，和數(shù)據(jù)連接相關(guān)的設(shè)置，和如何執(zhí)行SQL語句相關(guān)的設(shè)置，以及要執(zhí)行的SQL語句
• Parameter Mapping：包含Package級或容器級的變量集合。變量為SQL語句或者存儲過程提供變量輸入值
• Result Set：包含執(zhí)行SQL語句或存儲過程之后得到的數(shù)據(jù)集
• Expressions：包含設(shè)置這個任務(wù)的屬性的表達(dá)式，這個是需要動態(tài)設(shè)置屬性的

這個任務(wù)中的主要的屬性設(shè)置如下：

• ConnectionType，Connection：這些屬性用來設(shè)置數(shù)據(jù)連接，如類型，名字。
• SQL Statement：為task提供要執(zhí)行的SQL語句。這里的SQL語句可以是一個簡單的SELECT語句，一個復(fù)雜的帶GO語句的，或者調(diào)用一個存儲過程
• SQL Source Type：這個屬性是新添加的一個屬性。它提供屬性配置SQLStatement放置在一個變量，文件，或者直接輸入SQL語句
• ResultSet property：這個屬性可以設(shè)置為執(zhí)行SQL語句之后得到的一個單一結(jié)果，多行多列的結(jié)果，或者一個XML數(shù)據(jù)。如果在General標(biāo)簽內(nèi)將ResultSet設(shè)置為NONE，在Result Set標(biāo)簽界面內(nèi)的表格將不可用

在使用這個task之前需要理解SSIS如何處理變量，還有一個很重要的屬性expressions。

　　

變量

在SSIS中變量時有范圍的，Package范圍內(nèi)的只能在整個Package內(nèi)使用，就相當(dāng)于全局變量。變量可以由不同的分離的范圍如圖6-2.默認(rèn)的名稱空間是User。變量名是區(qū)分大小寫的，這些細(xì)節(jié)會導(dǎo)致錯誤的Package邏輯。圖6-2顯示在同一個Package中存在的兩個同名的但是作用范圍不同的兩個變量sSQL。在SSIS中可以方便地使用變量，首先，配置工具允許使用XML文件，環(huán)境變量，甚至注冊設(shè)置來配置Package。當(dāng)Package設(shè)置是靜態(tài)的，在運行之前配置的，這些技術(shù)對于多環(huán)境開發(fā)更加容易管理。當(dāng)從輸入流中獲得設(shè)置信息，或者需要在運行時修改屬性，這種技術(shù)就不太容易了。三種使用非靜態(tài)配置的例子是調(diào)用帶參數(shù)的Package，按照命名規(guī)范規(guī)范修改輸出文件名，在運行時修改連接屬性。

  

圖6-2

變量可以存儲一個任務(wù)中的值并傳遞到另外一個任務(wù)中。變量可以通過SSIS中的屬性表達(dá)式設(shè)置IS(Integrated Service)組件的屬性。可以使用變量設(shè)置任務(wù)中的信息：標(biāo)記，計數(shù)器，或者控制Package的字符串。由于變量的靈活性，可以使用更多的變量設(shè)置。

　　

表達(dá)式

大多數(shù)的任務(wù)，轉(zhuǎn)換，容器都有一個屬性是表達(dá)式，Package本身也有這種屬性。表達(dá)式集合使用邏輯表達(dá)式來顯示容器等的屬性。執(zhí)行SQL語句任務(wù)中可以使用表達(dá)式設(shè)置的屬性是SQL StatementSource，就是將要執(zhí)行的SQL語句或存儲過程的源。另外一個屬性ConnectionString為OLE DB連接設(shè)置連接字符串。在任務(wù)中右擊選擇屬性，在表達(dá)式這一欄點擊表達(dá)式傍邊的按鈕打開編輯表達(dá)式對話框。

如圖6-3顯示的是執(zhí)行SQL任務(wù)的屬性編輯對話框，點擊Property下拉列表框顯示的是屬性表達(dá)式。

圖6-3

點擊表達(dá)式欄傍邊的按鈕創(chuàng)建一個表達(dá)式。如圖6-4，使用這個工具創(chuàng)建一個邏輯表達(dá)式，可以使用的選擇項有字面值，系統(tǒng)，用戶自定義變量，操作符，內(nèi)建函數(shù)。表達(dá)式語言中的函數(shù)和操作符類似于C#，C和TSQL語言，但有區(qū)別。使用VB語言時==代表著判斷相等，&&代表著邏輯和，TSQL語言中使用雙引號包含字符串而不是單引號。表達(dá)式語言不是大小寫敏感的，所以C#和C程序員可以不必在意變量名的大小寫問題。花一點時間很快就會熟悉表達(dá)式語言。

圖6-4

現(xiàn)在我們繼續(xù)討論如何在執(zhí)行SQL任務(wù)中使用兩種不同范圍的變量。圖6-4顯示SQLStatementSource屬性的表達(dá)式屬性，展開的變量節(jié)點顯示所有的變量值。點擊變量User::sSQL并拖放到Expression文本框內(nèi)，這樣就創(chuàng)建了一個表達(dá)式。在運行時表達(dá)式的值將會替代SQLStatementSource屬性的值。點擊下方的Evaluate Expression按鈕查看表達(dá)式的值。可以看到表達(dá)式的值是SELECT 2。在這例子中，兩個變量的名字相同，范圍不同。在執(zhí)行任務(wù)時，屬性的值將會是SELECT 2。

　　

使用SQL 輸出參數(shù)動態(tài)改變package設(shè)置

在這個例子中我們將導(dǎo)出AdventureWorks數(shù)據(jù)庫中的[HumanResources].[Shift]中的數(shù)據(jù)到一個txt文件中，并在運行時修改屬性的值。在執(zhí)行SQL任務(wù)中使用輸出表達(dá)式來修改導(dǎo)出路徑。

假設(shè)客戶要求將一個Package和一個包含系統(tǒng)配置信息的數(shù)據(jù)庫整合起來，在這個數(shù)據(jù)庫中分別存放著開發(fā)，測試和產(chǎn)品環(huán)境。在載入數(shù)據(jù)的時候，所有的設(shè)置需要在運行時使用存儲過程從數(shù)據(jù)庫中抽取。從開發(fā)環(huán)境遷移到測試環(huán)境的時候通過修改數(shù)據(jù)庫中存放的配置信息實現(xiàn)修改遷移目的的功能。

因為需要在運行時獲得屬性設(shè)置，需要使用存儲過程來重新獲得設(shè)置，設(shè)置信息在Package中是不能獲得的。這里使用表達(dá)式來解決這個問題。

1. 新建Package，在Control flow界面類拖放一個執(zhí)行SQL語句任務(wù)
2. 在Control Flow設(shè)計界面右擊添加一個變量，為變量命名為MyFile，值為c:\Execute SQL Sample.txt   注意這里不要使用引號將這個值包含起來。
3. 新建一個存儲過程，為簡單起見，將存儲過程放在同一個數(shù)據(jù)庫中。這個存儲過程用來模擬在AdventureWorks數(shù)據(jù)庫中運行下面的語句，注意這個存儲過程會修改傳入的參數(shù)
   

   USE adventureworks
   go
   CREATE PROC dbo.usp_GetConfigParamValue (
           @ApplicationName   Varchar(30),        -- the name of the application.
   
           @ParameterName     Varchar(50),        -- the name of the parameter
           @ParameterTypeName Varchar(30),        -- the name of the parameter type
           @ParameterValueVar Varchar(255) OUTPUT -- output buffer for value
           )
   
   AS
         Set NOCOUNT ON
   
         --Dummy proc to simulate the real usp_GetConfigParamValue
         --Always outputs 'c:\ Execute SQL Sample Changed.txt'
         SET @PARAMETERVALUEVAR='c:\Execute SQL Sample Changed.txt'
   
         Set NOCOUNT OFF

4. 設(shè)置執(zhí)行SQL任務(wù)的連接類型為ADO.NET，首先新建一個ADO.NET連接，在server name欄中輸入”.”表示本地服務(wù)器，在下拉列表框中選擇AdventureWorks數(shù)據(jù)庫，保持默認(rèn)訪問機(jī)制NT Authentication。注意：在這個例子中使用OLE DB連接將不再適用，不同的連接中處理存儲過程參數(shù)的方法不同
5. 設(shè)置Execute SQL SQLStatemnet屬性為以下的代碼：EXEC usp_GetConfigParamValue 'MYAPP', 'MYPARM', 'STRING', @MYVALUE OUTPUT
6. 在參數(shù)映射標(biāo)標(biāo)簽界面添加一個映射MyFile，設(shè)置variable name為User::MyFile，direction為Output，數(shù)據(jù)類型為String，Parameter Name為MYVALUE，點擊OK保存設(shè)置。這里要注意這個變量名字一定要和上面的SQL語句中的變量@MYVALUE一致，可以不加@
7. 從工具欄中拖放一個Data Flow任務(wù)，然后將Execute SQL和Data Flow連接起來
8. 在Data Flow 設(shè)計界面拖放一個ADO NET Source和一個Flat File Destination。注意：檢查你拖放的是一個Flat File Destination而不是一個Flat File Source，這兩個容易混淆
9. 配置OLE DB Source選擇AdventureWorks連接設(shè)置SQLCommand屬性為：Select * from [HumanResources].[Shift]
10. 把OLE DB Source和AdventureWorks連接起來
11. 雙擊Flat File Destination打開編輯界面，新建一個delimited文件，暫時地設(shè)置文件名為c:\myfile.txt，點擊Mapping標(biāo)簽查看列名，保持默認(rèn)設(shè)置點擊OK退出編輯界面
12. 這里要演示的是在運行時修改task的屬性，現(xiàn)在這個Package能完成的任務(wù)是將數(shù)據(jù)庫中[HumanResources].[Shift]表中的數(shù)據(jù)導(dǎo)入到c:\Execute SQL Sample.txt 。現(xiàn)在想要在運行時修改文件名字，需要一個表達(dá)式修改連Flat File Connection的連接字符串，我們使用表達(dá)式來達(dá)到這個目的
13. 右擊Connection Managers中的Flat File Connection Manager選擇屬性，在屬性欄中點擊Expressions傍邊的按鈕，要修改的屬性是ConnectionString，在Expression Builder界面內(nèi)選擇變量@[User::MyFile]。在運行時表達(dá)式將會被賦值為變量MyFile中的值

運行這個Package檢查路徑C:\SSISDemos下查看文件Execute SQL Sample Changed.txt，我們本來是要將數(shù)據(jù)導(dǎo)入到Execute SQL Sample.txt中的，現(xiàn)在修改了路徑，數(shù)據(jù)將傳到新的文件Execute SQL Sample Changed.txt中。這樣就實現(xiàn)了動態(tài)修改Flat File Connection的屬性值的目的，這樣可以不用手動修改package的配置，直接修改存儲過程中的這一句：SET @PARAMETERVALUEVAR='c:\Execute SQL Sample Changed.txt' ，這樣每次都可以重新設(shè)置數(shù)據(jù)的存放地址。

執(zhí)行完成之后的效果如圖6-5

這個例子的應(yīng)用情景是首選將數(shù)據(jù)輸出到一個開發(fā)環(huán)境，然后再使用表達(dá)式將屬性設(shè)置為輸出到一個生產(chǎn)環(huán)境。這樣做的好處是一旦設(shè)置被一直到一個不同的環(huán)境，不必修改所有的SSIS嵌入環(huán)境設(shè)置就可以修改Package的輸出路徑。這個例子也使用了SSIS表達(dá)式，使用ADO.NET演示了輸出參數(shù)，有些地方需要注意：

• 如果數(shù)據(jù)提供者沒有識別出輸出參數(shù)的名字和次序，一個變通的方法是是存儲過程返回一個結(jié)果集并映射到變量中。如果不能修改存儲過程，也可以執(zhí)行簡單的SQL語句查詢結(jié)果并返回。下面是SQLStatement設(shè)置的語句：
  DECLARE @MYVALUE AS VARCHAR(255)
  EXEC usp_GetConfigParamValue 'MYAPP', 'MYPARM', 'STRING', @MYVALUE OUTPUT
  SELECT @MYVALUE AS MyValue
• 這里IsQueryStoredProduced屬性默認(rèn)設(shè)置為false，看上去應(yīng)該設(shè)置為true。但是設(shè)置為true會產(chǎn)生錯誤，顯示找不到儲存過程。