<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Data Warehousing
»
Integration Services
»
Foreach Loop Failing to Retrieve Element
Foreach Loop Failing to Retrieve Element
Rate Topic
Display Mode
Topic Options
Author
Message
Mordred
Mordred
Posted Wednesday, August 01, 2012 6:55 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
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
Daniel Bowlin
Daniel Bowlin
Posted Thursday, August 02, 2012 10:16 AM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, June 05, 2013 11:39 AM
Points: 2,679,
Visits: 2,422
I believe your mapping should be index 0.
Post #1339343
SQLRNNR
SQLRNNR
Posted Thursday, August 02, 2012 10:21 AM
SSCoach
Group: General Forum Members
Last Login: Today @ 10:09 PM
Points: 18,848,
Visits: 12,433
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1339349
Mordred
Mordred
Posted Thursday, August 02, 2012 11:48 AM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
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
SQLRNNR
SQLRNNR
Posted Thursday, August 02, 2012 12:14 PM
SSCoach
Group: General Forum Members
Last Login: Today @ 10:09 PM
Points: 18,848,
Visits: 12,433
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 2008
SQL RNNR
Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1339423
Mordred
Mordred
Posted Thursday, August 02, 2012 12:40 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
Thanks sqlrnnr, that helps my understanding a lot. Much appreciated.
Regards:
Mordred
Keep on Coding in the Free World
Post #1339444
Mordred
Mordred
Posted Thursday, August 02, 2012 12:41 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
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
Daniel Bowlin
Daniel Bowlin
Posted Thursday, August 02, 2012 3:00 PM
SSCrazy
Group: General Forum Members
Last Login: Wednesday, June 05, 2013 11:39 AM
Points: 2,679,
Visits: 2,422
You are assigning the value to your variable User::sDbName, that is what you should use inside the FE Loop.
Post #1339536
Mordred
Mordred
Posted Thursday, August 02, 2012 10:13 PM
SSC Journeyman
Group: General Forum Members
Last Login: Tuesday, June 04, 2013 2:25 PM
Points: 96,
Visits: 490
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 »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
