Read DB Table and Place All The Read Values into a String

[Stuugie]

Hi All,

I'm working on filling a user variable called "EmailString" via a db connection with SSIS. I've created a script task to do this (the only way I can see how) and in it I have the following code:

Public Sub Main()
        '
        ' Add your code here
        '
        Dim myPackageID As String = Dts.Variables("System::PackageID").Value.ToString()
        Dim myVal As String = String.Empty
        Dim cmdString As String = "Select Email from dbo.tblInternal_Email_List where Active = @1"

        Try
            Dim myCon As SqlClient.SqlConnection
            myCon = DirectCast(Dts.Connections(0).AcquireConnection(Nothing), SqlClient.SqlConnection)
            myCon = New SqlClient.SqlConnection(myCon.ConnectionString)
            myCon.Open()
            Dim cmd = New SqlClient.SqlCommand()
            cmd.CommandText = cmdString
            Dim parm As New SqlClient.SqlParameter("@PACKAGEID", SqlDbType.UniqueIdentifier)
            parm.Value = New Guid(myPackageID)
            cmd.Parameters.Add(parm)
            parm = New SqlClient.SqlParameter("@SETTINGID", SqlDbType.NVarChar)
            parm.Value = "EmailString"
            cmd.Parameters.Add(parm)
            cmd.Connection = myCon
            cmd.CommandText = cmdString

            Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader
            Do While (reader.Read())
                myVal = myVal & reader("value").ToString & "; "
            Loop
            Dts.Variables("User::EmailString").Value = myVal
            reader.Close()
            myCon.Close()
            myCon.Dispose()

        Catch ex As Exception
            Dts.TaskResult = ScriptResults.Failure
            Throw
        End Try

        Dts.TaskResult = ScriptResults.Success
    End Sub

This code has been adapted from a book I have but it throws the following error:

Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

Can anyone tell me why based on what I have provided?

What I want is to read the Email column from the db table and then put the email addresses into the EmailString.

What I want is to read the Email column from the db table and then put the email addresses into the EmailString.

I'm not familiar with VB.

This error:

Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

To fixed it you can read this and get an idea:

http://toddmcdermid.blogspot.com/2011/05/use-connections-properly-in-ssis-script.html

and this:

http://www.sqlservercentral.com/Forums/Topic1077229-148-1.aspx

[Stuugie]

Thanks a lot for those links LastMitch, I searched sqlservercentral but my search criteria didn't get me that thread and the Googles didn't get me that great blog.