VB.net Object Variable to a string

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
'Imports System.Net
'Imports System.Net.Mail
Imports System.Windows.Forms
Imports System.Xml
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()
        Dts.TaskResult = CInt(ScriptResults.Success)
        Dim oleDA As New OleDbDataAdapter()
        Dim dt As New DataTable()
        Dim col As DataColumn = Nothing
        Dim row As DataRow = Nothing
        Dim strMsg As String = Nothing
        'Dim strMessage1 As String = Nothing


        oleDA.Fill(dt, Dts.Variables("listDates").Value)

        For Each row_ As DataRow In dt.Rows
            row = row_
            For Each col_ As DataColumn In dt.Columns
                col = col_
                strMsg = strMsg & col.ColumnName & ": " & row(col.Ordinal).ToString() & Environment.NewLine
            Next
            MessageBox.Show(strMsg)
            strMsg = ""
            'strMessage1 = strMsg
        Next
        'strMessage1 = strMsg

        Dts.TaskResult = CInt(ScriptResults.Success)

    End Sub

End Class

Open in new window

Hello Experts,

I am trying to write VB script in "SSIS Script Task Component to capture the data from an Object Variable and assign it to a string variable.

I have this below code (Source: Google) which loops for every record and displays it in a mesage box. Instead of this, I need your help in modifying the below code to get all the data from the object variable to a string variable.

Obejct Variable will get the data from a query which results in to 1 column and mutliple rows.



Please help!!!

Thanks in advance!!
ravichand-sqlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ravichand-sqlAuthor Commented:
Got the fix!!

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
'Imports System.Net
'Imports System.Net.Mail
Imports System.Windows.Forms
Imports System.Xml
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()
        Dts.TaskResult = CInt(ScriptResults.Success)
        Dim oleDA As New OleDbDataAdapter()
        Dim dt As New DataTable()
        Dim col As DataColumn = Nothing
        Dim row As DataRow = Nothing
        Dim strMsg As String = Nothing
        'Dim strMessage1 As String = Nothing


        oleDA.Fill(dt, Dts.Variables("listDates").Value)

        For Each row_ As DataRow In dt.Rows
            row = row_
            For Each col_ As DataColumn In dt.Columns
                col = col_
                strMsg = strMsg & col.ColumnName & ": " & row(col.Ordinal).ToString() & Environment.NewLine
            Next
            'MessageBox.Show(strMsg)
            'strMsg = ""
            'strMessage1 = strMsg
        Next
        Dts.Variables("User::strMessage1").Value = strMsg

        Dts.TaskResult = CInt(ScriptResults.Success)

    End Sub

End Class

Open in new window


Thanks!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.