Solved

VB.net Object Variable to a string

Posted on 2013-05-20
2
721 Views
Last Modified: 2016-02-11
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!!
0
Comment
Question by:ravichand-sql
2 Comments
 

Accepted Solution

by:
ravichand-sql earned 0 total points
ID: 39182541
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

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now