Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Foreach Loop Failing to Retrieve Element Expand / Collapse
Author
Message
Posted Wednesday, August 1, 2012 6:55 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
Hi all, I am stuck at figuring out why the following parameters for an Execute SQL Task and Foreach Loop Container are not right. This format is almost identical to a book I am reading but it is not working! The variables themselves are scoped to the package and everything else here seems correct but being new to SSIS I am not understanding the errors which are:
Error: 0xC002410D at Foreach Loop Container: The enumerator failed to retrieve element at index "1".
Error: 0xC001C012 at Foreach Loop Container: ForEach Variable Mapping number 1 to variable "User::sDbName"
cannot be applied.

I know that the SQL statement outputs all of the rows that I want because I built it and the SQL Task completes successfully but the Foreach Loop stops. There are only 26 rows from this table for now so I know that the enumerator should have been able to retrieve the element at index 1 but for some reason it does not. Any ideas? I've been stuck on this for a bit now and really don't understand why it isn't working.

Execute SQL Task
General:
ResultSet = Full result set
SQL Statement:
ConnectionType = OLE DB
Connection = MICHAEL-PC.EconAnalysis
SQLSourceType = Direct input
SQLStatement =
SELECT Table_Links FROM dbo.tblLinks

BypassPrepare = True
No Parameter Mapping
Result Set:
Result Name = 0
Variable Name = flObj (of Object datatype)
No Expressions

Foreach Loop Container
Collection:
Enumerator = Foreach ADO Enumerator
ADO object source variable = User::flObj
Rows in first table selected
Variable Mappings:
Variable = User::sDbName
Index = 1


Regards:
Mordred
Keep on Coding in the Free World
Post #1338911
Posted Thursday, August 2, 2012 10:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 AM
Points: 2,818, Visits: 2,567
I believe your mapping should be index 0.
Post #1339343
Posted Thursday, August 2, 2012 10:21 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 18,060, Visits: 16,090
Daniel Bowlin (8/2/2012)
I believe your mapping should be index 0.


Yes it should




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1339349
Posted Thursday, August 2, 2012 11:48 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
I'm loading my package as I type this and am curious as to why internet examples as well as the book I am reading set the mapping index to 1? I understand coded for loops but this container confuses me a bit. Doesn't setting the index to 1 make it iterate 1 record at a time? I guess I'm just not fully understanding how the container iterates.

EDIT: Also, if I want to store each output from my query in a variable (in the Variable Mappings section), should that variable be of String type or Object Type? The loop works now but the Script task I have is failing with the following errors:
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: 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.
There was a lot from the error output that I could have added but I didn't think it was all necessary.


Regards:
Mordred
Keep on Coding in the Free World
Post #1339406
Posted Thursday, August 2, 2012 12:14 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 7:55 AM
Points: 18,060, Visits: 16,090
The index value is the column position of the recordset you are looping. You want the first column so the index is 0.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1339423
Posted Thursday, August 2, 2012 12:40 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
Thanks sqlrnnr, that helps my understanding a lot. Much appreciated.

Regards:
Mordred
Keep on Coding in the Free World
Post #1339444
Posted Thursday, August 2, 2012 12:41 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
I meant to ask, how or where do I assign the record or element from that column so that I can access it via script in the Foreach loop?

Regards:
Mordred
Keep on Coding in the Free World
Post #1339446
Posted Thursday, August 2, 2012 3:00 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:24 AM
Points: 2,818, Visits: 2,567
You are assigning the value to your variable User::sDbName, that is what you should use inside the FE Loop.
Post #1339536
Posted Thursday, August 2, 2012 10:13 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 8, 2013 9:25 AM
Points: 96, Visits: 495
Thank you for that Daniel. Thank you to everyone else too.

Regards:
Mordred
Keep on Coding in the Free World
Post #1339625
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse