SSIS Element cannot be found in a collection (but I have them all listed!)

Isaac picture Isaac · Sep 9, 2014 · Viewed 24.5k times · Source

I'm getting a persistent error:

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.

I've checked, double and triple-checked my variable listings in the Read-Only and Read-Write variables in my Script task. I've debugged it to death and gotten input from another programmer here who couldn't spot the issue either. I've also researched to no end.

  • Does anyone see anything wrong with my code?

Script Task code:

Public Sub Main()
    Dts.Variables("User::strMailBody").Value = "Thank you for submission. For your convenience, we are including the last four of the HICN# and the Name on the application(s) we have received* from you." _
        & vbNewLine & vbNewLine & "Here are the following: " & vbNewLine & vbNewLine
    Dts.Variables("User::strMailBody").Value = Dts.Variables("User::strMailbody").Value.ToString() & vbNewLine & Dts.Variables("User::strListing").Value.ToString()
    Dts.Variables("User::strMailBody").Value = Dts.Variables("User::strMailBody").Value.ToString() & vbNewLine & vbNewLine & Dts.Variables("User::strFooter").Value.ToString()

    If Left(Dts.Variables("User::strAgentID").Value, 2) = "TX" Then
        Dts.Variables("User::strSubject").Value = "ACME Health Plans Confirmation:  Total "
    Else
        Dts.Variables("User::strSubject").Value = "ACME2 Baptist Health Plans Confirmation:  Total "
    End If

    Dts.Variables("User::strSubject").Value = Dts.Variables("User::strSubject").Value.ToString() & Dts.Variables("User::lngCountAgent").Value.ToString() & "   " & "[RESTRICTED: CONFIDENTIAL]"
    Dts.Variables("User::DateSent").Value = Now()
    Dts.Variables("User::UserSent").Value = "SSIS"

    Dts.TaskResult = ScriptResults.Success
End Sub

Answer

P_Fitz picture P_Fitz · Jun 7, 2016

For anybody else struggling with this issue the resolution for me was as follows: (note I am NOT using User:: when getting variable values within my script task)

  • On the package Properties I hadn't included the variables as ReadOnlyVariables

You'll need to set your newly added variables as follows:

  1. Right click on the package and select Edit
  2. In the Script section click on ReadOnlyVariables or ReadWriteVariables (depending on your how you want your variables behave)
  3. Check the check-box beside the variables you wish to use in your script task
  4. Click Ok to save your changes

Hope this helps