Aaron Akin

January 14, 2009

Failed to lock variable for read access in SSIS

Filed under: Checkpoints,SSIS — Aaron Akin @ 5:37 pm

I was working on a new SSIS package recently, and in the process of trying to fix an issue with my package, I created an entirely new problem.  My package now starting failing with the following error:

Error: Failed to lock variable XXXX for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created."

It was so frustrating that I figured I would post it here in case anyone else comes across the same problem.

If you’ve ever seen this error before, you’ll most likely find that it is caused by a script task/component that is referencing a variable that no longer exists.  In my case, I had no scripts but I did change some of my package variables.

After a while of trying to figure out what would cause this issue, I finally realized that my package had checkpoints enabled.  When checkpoints are enable, details about the variables are written to the checkpoint file when the package is executed.

In the process of fixing the original problem with my package, I deleted some of the existing variables.  I made sure to update all of my tasks that referenced the deleted variables, but I forgot to delete the checkpoint file before restarting the package.

When the checkpoint file was read upon execution, it tried to assign a value to the variables that were deleted, but since they did not exist anymore, I got this error.

I guess the point is, if you make changes to the structure of a package when checkpoints are enabled and a checkpoint file exists, it might be worth your time to delete the file before restarting the package with the new changes in effect.



January 3, 2009

SSIS Checkpoints with Event Handlers

Filed under: Checkpoints,Event Handlers,SQL 2005,SSIS — Aaron Akin @ 2:33 am

I recently had a project that required my package to utilize checkpoints, which wouldn’t have been a big deal, except this package had event handlers on some of the tasks.

Working with checkpoints never seems to be as straight forward and intuitive as you’d like it to be, and trying to implement them into a package that has event handlers can be quite frustrating (to say the least).

Since Microsoft BOL contains very limited documentation on the advanced uses of checkpoints in SSIS, I created a simple test package and played with as many properties as I could until I finally got the event handlers to work.

By the way, you can probably get around any of these problems by writing your own custom code, but I wanted to find out if it could be accomplished without using any custom logic (i.e. script tasks).

Here’s what I came up with…

Blog at WordPress.com.