Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 809
Isaac Avatar asked Sep 09 '14 18:09

Isaac


3 Answers

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

like image 160
P_Fitz Avatar answered Nov 09 '22 19:11

P_Fitz


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

like image 5
High Plains Grifter Avatar answered Nov 09 '22 20:11

High Plains Grifter


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

like image 1
Isaac Avatar answered Nov 09 '22 21:11

Isaac