Solved

VB.net Object Variable to a string

Posted on 2013-05-20
2
718 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

919 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

19 Experts available now in Live!

Get 1:1 Help Now