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.
