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.