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

Question

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

Pete Bishop

Default port

Points: 1478
More actions
June 17, 2021 at 8:35 am

#3897360
*** 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 3 years, 6 months ago by Pete Bishop.

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

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

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'.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jez-448386 SSCarpal Tunnel Points: 4594 More actions · Answer 2

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.

Pete Bishop Default port Points: 1478 More actions · Answer 3

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.

Pete Bishop Default port Points: 1478 More actions · Answer 4

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Pete Bishop Default port Points: 1478 More actions · Answer 6

Pete Bishop

Default port

Points: 1478

June 17, 2021 at 10:09 am

#3897387

It shows the "For Loop Container" task.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Pete Bishop Default port Points: 1478 More actions · Answer 8

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

Tim Mitchell SSCoach Points: 15685 More actions · Answer 9

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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Pete Bishop Default port Points: 1478 More actions · Answer 10

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

Tim Mitchell SSCoach Points: 15685 More actions · Answer 11

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
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices