Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
The enumerator failed to retrieve element at index "1" error occurs randomly

Answered The enumerator failed to retrieve element at index "1" error occurs randomly

  • Tuesday, January 29, 2013 12:37 PM
     
     
    Sign In to Vote
    0

    Hi,

    I have a SSIS package that randomly fails with error:

    Error: The enumerator failed to retrieve element at index "1".
    Error: ForEach Variable Mapping number 2 to variable "User::variable" cannot be applied.

    The package reads a two columns from a database table and stores the result set in an object type variable. This variable is then used in a foreach loop container to iterate through the results. The package seems to fail without a good reason. If the error happens when I'm running the package from BIDS, I can get the package working by restarting BIDS. The real problem is that if the package fails when running it outside BIDS, I have no reliable way to get it working again. What's going on here?

    I'm running SQL Server 2008 R2 EE on Windows Server 2008 R2 Enterprise.

     

All Replies

  • Tuesday, January 29, 2013 12:39 PM
     
     
    Sign In to Vote
    0

    What are the datatypes of the columns being retrieved and what are your variable types?

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Tuesday, January 29, 2013 12:50 PM
     
     
    Sign In to Vote
    0
    Both variables are strings. The first column retrieved from the database is varchar(6) and the second is date converted to yyyymm as varchar.
     
  • Tuesday, January 29, 2013 12:53 PM
     
     
    Sign In to Vote
    0
    Well that is strange then.  Either of the values have the potential to be NULL?

    Chuck Pedretti | Magenic – North Region | magenic.com

     
  • Tuesday, January 29, 2013 2:35 PM
     
     
    Sign In to Vote
    0
    That's just it. The table where the values are retrieved has no NULL values in it. And the fact that one minute the package doesn't work and another minute it does without any changes in the table or the package is weird.
     
  • Tuesday, January 29, 2013 3:10 PM
     
     
    Sign In to Vote
    0

    In that case, if the package is small to medium size, did you tried to create it from scratch?

    Thanks,

    hsbal

     
  • Tuesday, January 29, 2013 3:27 PM
     
     
    Sign In to Vote
    0
    Yeah, I'll probably have to try that. Its not too large so I should be able to recreate it in an hour or so.
     
  • Tuesday, January 29, 2013 3:33 PM
     
     Answered
    Sign In to Vote
    1
    Yeah, I'll probably have to try that. Its not too large so I should be able to recreate it in an hour or so.

    While you are at it - add some error handling - direct all of your error rows and messages to a flat file so that you can debug the issue if it continues.

    Chuck Pedretti | Magenic – North Region | magenic.com