SSIS脚本任务中访问记录集RecordSet

脚本任务中访问记录集RecordSet

在”脚本任务”中访问变量.我们可以使用Dts.Variables(“变量名”).Value 的方法得到变量的值.
但如果变量是ADO的结果集.就需要将ADO结果集填充到一个DataTable中.然后再查这个DataTable.

在脚本任务的ReadOnlyVariables中写上我们的Object类型的ADO结果集的变量名称.
然后进入脚本编辑器.

在Visual Studio for Applications IDE中.添加引用”System.xml.dll”

然后编写代码:

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.



Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Data.OleDb



Public Class ScriptMain



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

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

	' and logging features are available as static members of the Dts class.

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

	' 

	' To open Code and Text Editor Help, press F1.

	' To open Object Browser, press Ctrl+Alt+J.



	Public Sub Main()

        Dim Adapter As New OleDbDataAdapter

        Dim Table As New DataTable



        Adapter.Fill(Table, Dts.Variables("v_Result").Value)  '此时我的ADO变量是v_Result

        '得到DataTable,就可以做所有我们想做的事情了.



        Dim msg As String



        For Each _dataRow As DataRow In Table.Rows

            msg = ""

            For Each _dataColumn As DataColumn In Table.Columns

                msg += _dataColumn.ColumnName + " : " + _dataRow(_dataColumn.Ordinal).ToString + vbTab

            Next



            '显示msg出来

            MsgBox(msg)

        Next

		Dts.TaskResult = Dts.Results.Success

	End Sub



End Class

这里的”执行SQL任务”是得到一个完整的结果集.然后给一个Object类型的变量