SSIS中的腳本—腳本任務

腳本任務主要用來控制數據流，當現有的控制流 任務不能滿足復雜的設計要求的時候，就應該考慮使用腳本任務。
這里我們創建第一個腳本任務，和其他的教學例子一樣，我們也將創建一個簡單的HelloWord程序。

1. 新建一個package 命名為ScriptTaskExample
2. 在Control Flow內拖放一個Script，如圖1
   

   
   圖1
   　　

3. 雙擊打開編輯界面如圖2

   
   圖2
   　　

4. 點擊左邊Script標簽，打開界面如圖3

   
   圖3
   　　

5. 這里有幾個屬性標簽下面做一些說明
   a. ScriptLanguage:在這里可以設置要使用的語言。SSIS2005版本中只支持Visual Basic.NET,后續版本中添加了C#
   b. PrecompileScriptIntoBinaryCode:設置這個屬性為true的時候，腳本在執行之前就被編譯，這樣可以提高性能。設置為false，在調用script的時候才會編譯腳本。當在SQL Server Agent job中調用這個package的時候需要將這個屬性設置為true
   c. EntryPoint:這個屬性設置為Mian，表明程序的入口是Main方法
   d. ReadOnlyVariables:一個用逗號隔開的字符串，包含可以被程序讀取的SSIS變量
   e. ReadWriteVariables:一個用逗號隔開的字符串，包含可以被程序讀取和寫入的SSIS變量

6. 在左邊標簽欄還有一個Expressions我們在前面的章節已經說明，這里不再解釋。

7. 點擊Edit Script按鈕打開Visual Studio編輯界面如圖4

   
   圖4
   
   所有的代碼編輯都在這個Visual Studio for Application界面中進行，它也包含所有的代碼編輯調試工具，包含代碼高亮顯示和智能感知。
   這里的代碼非常的簡單，開頭的代碼：

           ' Microsoft SQL Server Integration Services Script Task

           ' Write scripts using Microsoft Visual Basic 2008.

   ' The ScriptMain is the entry point class of the script.
   這段代碼最好替換成自己的代碼說明：A script to display ‘HelloWord!’ to the user.

   下面的代碼：

           Imports System

           Imports System.Data

           Imports System.Math

   Imports Microsoft.SqlServer.Dts.Runtime

   這段代碼引入了要在程序中使用的類庫。整個代碼如下：

           Imports System

           Imports System.Data

           Imports System.Math

           Imports Microsoft.SqlServer.Dts.Runtime

    

           <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _

           <System.CLSCompliantAttribute(False)> _

           Partial Public Class ScriptMain

           Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    

           Enum ScriptResults

                   Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

                   Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

           End Enum

          

    

           ' The execution engine calls this method when the task executes.

           ' To access the object model, use the Dts property. Connections, variables, events,

           ' and logging features are available as members of the Dts property as shown in the following examples.

           '

           ' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value

           ' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)

           ' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)

           '

           ' To use the connections collection use something like the following:

           ' ConnectionManager cm = Dts.Connections.Add("OLEDB")

           ' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"

           '

           ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

           '

           ' To open Help, press F1.

    

           Public Sub Main()

                   '

                   ' Add your code here

                   '

                   Dts.TaskResult = ScriptResults.Success

           End Sub

   End Class

   這段 程序到目前為止還沒有添加自己的代碼，僅僅是關于程序如何運行的說明和Dts.TaskResult值的設置。必須設置這個變量的值為true或false,否則SSIS運行時不知道程序是否執行成功以便采取下一步行動。

Dts對象

Dts是類Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel類的一個實例，Dts對象有7個屬性和一個方法，下面我們說明這些成員
 　　　　　a. Connections-包中定義的一個連接，使用這個連接可以獲得檢索外部數據
　　　　　 b. Events-一個事件的集合，使用這個接口可以捕獲到預定義事件，擴展事件
　　　　    c.  ExecutionValue-一個可讀寫的屬性，通過這個屬性可以附加自定義屬性，使用自定義的對象可以附加任何想要的信息
　　　　　 d. TaskResult-這個屬性可以設置任務狀態為成功或失敗，這是控制語句流的唯一方法，退出之前必須設置這個屬性
　　　　    e. Transaction-獲得容器中的事務
　　　　　 f.  Log-這個方法用來寫日志

Dts對象提供和package交互的所有屬性，要注意的是Dts只存在于Script task中，在Script Compontent中沒有這個對象
　　

訪問變量

變量是script task中的一個重要屬性，它是一個和package交互的工具。在script task中的變量有兩種類型，只讀的和讀寫的，在script task中有兩種方式獲得變量，VariableDispenser對象提供設置變量只讀和讀寫的屬性，這是標準方法，在早期的SSIS中使用API。
檢索VariableDispenser對象中的變量的代碼如下：
Dim vars As Variables
Dts.VariableDispenser.LockForRead("SomeVariable")
Dts.VariableDispenser.GetVariables(vars)
MsgBox(vars(0).Value)

這里用四行代碼不是很高效，所以在Dts對象中引入了Variables集合和ReadOnlyVariables，ReadWriteVariables屬性，這兩個屬性告訴script task那些變量需要設置只讀或讀寫屬性，Variables集合用來填充變量，代碼簡化如下：
Msgbox(Dts.Variables("SomeVariable").Value)

嘗試從Variables集合中讀取沒有賦制值的變量將會拋出異常，注意Visual Basic.NET大小寫敏感，但是SSIS中的變量不敏感。要訪問Variables集合中的變量，必須事先設置他們的只讀或讀寫屬性。調用一個名為StringVariable的變量的方法如下：
Variables.StringVariable
　　

事件

Script task中的事件可以用日志記錄下來，事件是從對象中發送一個消息標明一個動作正在發生或者將要發生。程序通過事件處理程序來捕獲事件。使用Dts對象中的Events屬性來發起一個事件，Events屬性是IDTSComponentEvents接口的一個實現對象，這個接口中定義了可以觸發的事件。

　　　　　　a. FireBreakpointHit-運行到斷點時觸發事件
　　　　　　b. FireError-程序出錯時觸發事件
　　　　　　c. FireInformation-使用消息觸發事件
　　　　　　d. FireProgress-遇到progress的時候觸發事件
　　　　　　f. FireQueryCancel-觸發一個事件表明是否停止執行包
　　　　　　g. FireWarning-程序出現警告時觸發事件
　　　　　　h. FireCustomEvent-觸發用戶自定義事件

 在SSIS中所有的事件都可以寫入到一個日志中，這在調試和解決問題中很有用。在SSIS Logging工具中設置日志記錄。在BIDS界面上方點擊SSIS?>logging，彈出SSIS日志編輯界面，在Provider Type下拉框中選擇XML files，點擊Add。點擊<new connection>設置打開File Connection Manager編輯框，選擇Usage type為Create file，將文件命名為log.xml選擇文件路徑，點擊選中設置好的xml文件如圖5

圖5
　　

點擊Details標簽，選中OnInformation，只有這種事件將會被記錄如圖6

圖6
　　

現在回到腳本編輯界面，添加下面的代碼：

Dts.Events.FireInformation(1, "MyScriptTask", "Some Info here", "", 0, False)

該方法的第一個參數是消息代碼，用來標識消息，這個值可以任意設置。第二個參數用來設置事件源，這個值仍然可以任意設置。第三個參數是消息本身。第四個參數是幫助文件的地址，這里設置為空，因為在這里幫助文件不需要。第五個參數是幫助主題內容，這里沒有幫助文件所以設置為0。最后 一個參數是一個布爾類型變量指示是否這個錯誤會被多次捕獲，這里設置為false,表示不會多次捕獲，即使多次調用這個代碼。運行這個package,最后事件信息記錄如下圖7

圖7
　　

這里記錄了事件觸發時間，package所在的機器名和用戶名等信息。也可以設置自己的事件捕獲程序，將在后面介紹。

日志

Log方法用來記錄日志信息，它有3個參數
 　　　　　　messageText-要記錄的日志信息
 　　　　　　dataCode-記錄日志信息代碼
 　　　　　　dataBytes-記錄二進制日志數據
Log方法和FireInformation方法類似，它更加簡單有效，下面的方法將二進制日志信息記錄到日志提供程序中：
Dts.Log("my message", 0, myByteArray)

調試腳本任務

調試是SSIS中的重要特性，使用Msgbox()方法輸出變量值的日子一去部復返了。使用Visual Studio的編輯環境可以設置斷點，測試變量的值，甚至可以運行表達式。
設置斷點使程序在次停留，查看程序的運行情況。有多種方法可以設置斷點，一種方法是在代碼行左側灰白色邊框上點擊鼠標左鍵。另一種方法是光標停留在代碼行上點擊F9。點擊F10使程序運行到下一行，或者點擊F5運行到下一個斷點。
運行到斷點處時界面如下

圖8
　　

運行到斷點處時script task 界面如下

圖9
　　

Visual Studio編輯環境提供一些視圖來觀察程序的運行狀況。例如自動彈出窗口，局部窗口，監視窗口，它們都用來現實變量或者表達式的值。
Autos窗口現實當前的狀況如圖10

圖10
　　

Locals窗口和Autos窗口類似。Watches窗口允許你添加一個監視變量。還可以查看Quick Watch窗口，如圖11

圖11
　　

Immediate窗口允許給表達式賦值，執行存儲過程，打印變量值，這個窗口和dos命令窗口類似，允許向編輯器發送命令。
Immediate窗口非常有用。例如一個自定義的類型，它有方法DoMyStuff()接受一個整數值作為參數，使用Immediate窗口，可以向它傳遞不同的值并查看返回值，給表達式賦值的時候需要使用?開頭：
?obj.DoMyStuff(2)
"Hello"

按下Enter鍵，在下一行輸出結果Hello。