Help writing datatable object to package object variable

Question

Post reply

Help writing datatable object to package object variable

RTaylor2208

SSChampion

Points: 13298
More actions
May 26, 2011 at 9:23 am

#145606

Hi, I have a bit of a problem.
I am taking the contents of a package object variable and reading it into a datatable in a VB.net script task. I want to update the datatable then write the result back to the object variable. I am not really a VB programmer so cannot figure out how to write back to the package variable. I have copied the VBscript below can anyone help me out? I think I just need a function to write the datatable back to the package variable.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Data.OleDb
<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
Public Sub Main()
Dim dt As New DataTable()
Dim da As New OleDbDataAdapter
Dim row As DataRow
Dim currentServer As String
Dim currentPosition As Integer
Dim maxPosition As Integer
'Read the Variables from the package
currentServer = ReadVariable("CurrentServer")
da.Fill(dt, ReadVariable("SuccesConnectList"))
'Display the current servername and how many rows are in the Object Variable
MsgBox("Current: " & currentServer & ", rows: " & dt.Rows.Count.ToString, MsgBoxStyle.OkOnly)
'Add a new row to the datatable
row = dt.NewRow()
row.Item(0) = currentServer
dt.Rows.Add(row)
'Initialise the loop variables
currentPosition = 0
maxPosition = dt.Rows.Count
'Display the Contents of the datatable
'While currentPosition < maxPosition
' row = dt.Rows.Item(currentPosition)
' MsgBox(row.Item("ServerName"))
' currentPosition = currentPosition + 1
'End While
'Write the updated datatable back to the variable
Dts.TaskResult = ScriptResults.Success
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Dim vars As Variables
Try
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Return result
End Function
End Class
MCITP SQL 2005, MCSA SQL 2012