Post reply

Foreach Loop Failing to Retrieve Element

  • August 1, 2012 at 6:55 pm

    #279041

    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

  • August 2, 2012 at 10:16 am

    #1520987

    I believe your mapping should be index 0.

  • August 2, 2012 at 10:21 am

    #1520993

    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[/url]
    Learn Extended Events

  • August 2, 2012 at 11:48 am

    #1521041

    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

  • August 2, 2012 at 12:14 pm

    #1521055

    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[/url]
    Learn Extended Events

  • August 2, 2012 at 12:40 pm

    #1521075

    Thanks sqlrnnr, that helps my understanding a lot. Much appreciated.

    Regards:
    Mordred
    Keep on Coding in the Free World

  • August 2, 2012 at 12:41 pm

    #1521077

    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

  • August 2, 2012 at 3:00 pm

    #1521154

    You are assigning the value to your variable User::sDbName, that is what you should use inside the FE Loop.

  • August 2, 2012 at 10:13 pm

    #1521226

    Thank you for that Daniel. Thank you to everyone else too.

    Regards:
    Mordred
    Keep on Coding in the Free World

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply