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。