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

Question

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

I don't think you need the "User::" part of the variable names. We are successfully using code like this to refer to variables: Dts.Variables("Tables").Value — Tab Alleman, Sep 09 '14 at 18:21
Hmm, I have always used it and I have been told it is a good practice to include. — Isaac, Sep 09 '14 at 18:23
Your script creates the ScriptResults enum, and includes "Success"? You don't show it. — Tab Alleman, Sep 09 '14 at 18:27
After research, it appears likely that either one of the cases is still incorrect, one of the variable names has changed and needs to be remapped, or one of the mappings is missing. The brackets appear to be useful in C# only. — Eric Hauenstein, Sep 09 '14 at 18:37
I know it sounds silly but have you restarted your PC? Maybe it may clear some memory issue or something as you have double checked the variables being passed matches with variables defined — Tak, Sep 09 '14 at 18:53

score 17 · Answer 1 · answered Jun 07 '16 at 10:27

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:

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

Hope this helps

score 5 · Answer 2 · answered Nov 04 '14 at 15:34

I just had the same issue and unable to find the problem for ages. I found that the reason for the error was that I had missed one of the colons between "User" and the variable name.

I had this (which caused the error):

string FileName = UserVariables["User:CurrentFileName"].Value.ToString();

when I should have had this:

string FileName = UserVariables["User::CurrentFileName"].Value.ToString();

just in case anyone else has the same problem :)

score 1 · Accepted Answer · answered Sep 09 '14 at 18:56

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).

score 0 · Answer 4 · answered Mar 02 '16 at 10:34

0

Had a similar issue, after a lot of debugging, realized that the variable naming convention should be User::varname and NOT USER::varname I guess c# is very case sensitive.

Hope this helps and saves you lot of your valuable time :-)

answered Mar 02 '16 at 10:34

Shantanu Chatterjee

1

1

That's not really answering anything the user is having trouble with. In the code sample, the user is using `User::` correctly. – mikeyq6 Mar 02 '16 at 10:40

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

4 Answers4