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

12
votes

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
5
variablesssisdts
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
Hmm, I have always used it and I have been told it is a good practice to include. – Isaac
Your script creates the ScriptResults enum, and includes "Success"? You don't show it. – Tab Alleman
@TabAlleman - I just left it out of the SO post. – Isaac
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

5 Answers

20
votes

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

5
votes

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

1
votes

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

0
votes

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

0
votes

a) Right click on the script task and choose edit b) Locate the Read or Read/Write variables properties in the list. c) Click on the property and the variable you wish to access in the script task.