Search code examples
variablesssisdts

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

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
Solution

  • Ohhh.........man. It's amazing how you can stare at this stuff and miss something stupid, for hours.

    strFooter was missing in the listing.

    ALL SET NOW. Sincere thanks to those who looked and commented. Eric, thanks, I'll remember that as sometimes I will probably need to use C insatead of VB (haven't yet but will).