June 17, 2021 at 8:35 am
*** Note that this package DOES NOT use Script Task ***
I have a "For Loop Container" in an SSIS package with a simple:
In the "For Loop Container" I have five "stacks" of tasks running in parallel:
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.
June 17, 2021 at 9:13 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2021 at 9:16 am
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.
June 17, 2021 at 9:55 am
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.
June 17, 2021 at 9:57 am
The variables in each stack are unique to that stack (they're all named for the stack in which they exist).
June 17, 2021 at 10:00 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2021 at 10:09 am
It shows the "For Loop Container" task.
June 17, 2021 at 10:28 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2021 at 10:34 am
Not yet, Phil. I'm gonna collate some data from the [operation_messages] and see if that points me in a direction.
June 17, 2021 at 12:36 pm
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
June 18, 2021 at 7:29 am
Tim - There's an OnError event handler at the package level but that just does an INSERT into a log table
June 21, 2021 at 5:13 pm
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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply