I have a package that starts by loading a recordset into an object variable. The recordset is then enumerated with a ForEach loop. The loop sets some string variables. Within the loop container I have a Script task that uses a MsgBox to show the results for testing purposes. The package uses checkpoint restart (if that matters ).
The first time I run the package the 1st record is displayed in the MsgBox, then the 2nd, but then the loop is stuck on the 2nd record forever. I break the run, and when I rerun it the 1st record is displayed followed by each subsequent record correctly and the package completes successfully. Now, if I were to run again the same problem would occur on rec 2 and I would have to break the run, and then the next run everything would work fine.
Why does the script get caught in an infinite loop the first time it's run, but works fine when restarting from the checkpoint
Here's my relevant code:
Shredding recordset object var in ForEach loop (problem)
ashoksharma
Gordon,
That sounds very strange. I can't really say anything to help yu except to say that checkpoints do not allow you to begin part way through an iteration of a ForEach loop. It WILL allow you to start from the first iteration of the ForEach loop thereby skpping all previous tasks.
Sorry I can't be of more help.
-Jamie
amanrathi
Jamie,
Yes, I knew that about checkpoints. I included the info about using checkpoints in case it was relevant. I also know that checkpoints don't save the object vars (thank you for that tidbit earlier).
But here's something interesting. If the recordset object var is created in an earlier task, and the restart (which in this case is the one that works) starts at the ForEach loop, where is the object var that is being used in the loop coming from Is the recordset being saved somewhere and used during the restart Should object variables be destroyed after use in SSIS packages
The_Geezer
Log it here http://lab.msdn.microsoft.com/productfeedback/default.aspx ...with detailed repro steps.
-Jamie
toSarvan
Marc Selman
Very very good question. Given that Object variables are not stored I don't know how this one would play out. I would assume it would error actually but that's only an assumtion.
-Jamie
acerpgh
Here's some more info....
Turning SaveCheckpoints on causes the problem with getting stuck in a recordset ForEach loop (1st run only). But setting TransactionOption to Required fixes the problem.
Ryo M
Saving checkpoints is the problem. Regardless of how the CheckpointUsage is set, the SaveCheckpoints property causes the problem when set to True. When I set it to False the loop works perfectly every time. A rather unfortunate bug which cripples the use of checkpoints.
How do we report a bug