"Variables collection has already been unlocked" error in For Loop Container

  • *** Note that this package DOES NOT use Script Task ***

    I have a "For Loop Container" in an SSIS package with a simple:

    • InitExpression - set loop variable to zero
    • EvalExpression - is loop variable less than a limit variable (set outside the loop)
    • AssignExpression - reduce loop variable by one

    In the "For Loop Container" I have five "stacks" of tasks running in parallel:

    • Expression task - always succeeds, used to anchor an "Expression and Constraint" connector
    • Execute SQL task - inserts into a log table
    • Data Flow task - populates a table
    • Execute SQL task - updates the log table

    As there is no code manipulation of the variables collection, I'm unclear where the "Variables collection has already been unlocked" issue is arising.

    Please note that most of the time this package runs without issue. It is just occasionally that it fails but when it does fail, it is always with this error.

    Any ideas? Thanks.

    • This topic was modified 1 month, 2 weeks ago by  Pete Bishop.
  • Is the error happening in SSDT? If so, you should be able to see from the Output window exactly which component is causing the error.

    Is your Expression task empty? Maybe that is confusing something. You could try using an empty Sequence container instead – that's what I do when I need a fancy precedence constraint 'from nowhere'.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Have you checked the scope of the variables within each "stack"?  For example, if you have a row count within the DFTs that is scoped at the For Loop Container (or package) level then each stack will reference the same variable and may cause issues.  If so then you would need to scope the variables at the "stack" level so that there is no contention for the same variable.

  • This is failing when the package runs from a SQL Agent job.

    I forgot to mention that *most of the time*, it works. We just get this failure occassionally.

  • The variables in each stack are unique to that stack (they're all named for the stack in which they exist).

  • OK, then check the SSISDB 'All Executions' report (assuming the package is deployed to SSISDB). It should tell you which component the package fails on.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • It shows the "For Loop Container" task.

  • I hate it when something fails, but only some of the time. Makes troubleshooting all the more challenging.

    Can you see any sort of pattern between executions which succeed and those which fail? Total number of loop iterations, other jobs running at the same time … you get the picture.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Not yet, Phil. I'm gonna collate some data from the [operation_messages] and see if that points me in a direction.

  • Pete, does your package use any event handlers? I've seen this error pop up on occasion when event handlers are accessing variables.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • Tim - There's an OnError event handler at the package level but that just does an INSERT into a log table

  • Hi Pete,

    If that event handler uses a variable - even if it just reads it - it might be causing contention that leads to this error. If possible, test the package with that event handler disabled to see if the error persists.

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

Viewing 12 posts - 1 through 12 (of 12 total)

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