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.

-A

Advertisements

13 Comments »

  1. How do you clear the checkpoints in the SSIS package? I am seeing this same error 😦

    Comment by Chris — June 22, 2009 @ 9:45 am | Reply

    • In the package properties, find the CheckpointFileName property. This will tell you where the checkpoint file is located. Find this file and delete it. This will cause the package to restart from the beginning next time you execute it.

      -A

      Comment by Aaron Akin — June 22, 2009 @ 10:27 am | Reply

  2. Aaron, thanks for the blog on this. I have the exact same error, however I do not (and have not used check points)…have you come across anything else that might cause this?

    Comment by Dennis — October 5, 2009 @ 12:01 pm | Reply

    • Dennis,

      A lot of times this error is caused by script tasks that reference variables that do not exist. A few things to check:

      1. Make sure all variables referenced in the script task is being passed that variable into the script.
      2. Make sure the variable is spelled the correctly.
      3. Make sure the scope of the variable is available to the script task.
      4. Make sure the namespace of the variable (i.e. User, System, etc) is correct. As good practice, I always pass in my variables into scripts using the namespace (user::var1,user::var2) and reference them in the script itself the same way.

      If you’d like, feel free to email me your package and I’ll see if I can figure it out for you. Make sure to strip it off any passwords, etc first. My email is aaronakin [at] gmail [dot] com.

      Comment by Aaron Akin — October 6, 2009 @ 6:49 pm | Reply

  3. I have run across the same problem, it seems to have to do with the fact that I was using expressions in my source and destination variables, in my expression I was concatenation two variables together, I solved it by creating a variable to hold both variables, then concatenate them in a script. I then deleted my expressions and just referenced the full concatenated variables in my file system task. seems to work.

    Comment by andrew hopkinson — June 11, 2010 @ 8:51 am | Reply

  4. I also had the exact same problem as Andrew #3. I fixed by creating three variables. I concatenated the first two into the third with an expression on the third variable (F4).

    Comment by ron foster — August 4, 2010 @ 10:08 am | Reply

  5. Thanks for this post. I found a similar problem, inside the Script Task Editor I’d added a ReadOnlyVariable. Even after deleting this, saving, closing Visual Studio, then re-opening the project – the ReadOnlyVariable was still there. I had to copy out the guts of the script task to a new script task.

    This solved the problem. Thanks again.

    Comment by Lara — August 25, 2010 @ 2:12 pm | Reply

  6. I was reading your post and found it may be the closest solution to the problem I’m having, although it is a different problem altogether. My package uses the following variable throughout multiple sql tasks:

    USER::StartOfWeekDate
    The initial value for this variable is generated by a sql step early in the package: Let’s say User::StartOfWeekDate was set to: 09/08/2010
    IN the following sql task, for example,

    SELECT *
    FROM tableName
    WHERE ref_Date = @[User::StartDate]

    That seemed to work fine every time I ran the package until i handed off my package to another developer. After she ran it the first time, the actual syntax in the above sql statement changed to:

    SELECT *
    FROM tableName
    WHERE ref_Date = 09/08/2010

    When the package stops, we see that the query changed to look like above, with the literal value ’09/08/2010′ replacing what used to be the variable name: @User::StartDate. Before the package runs again, we have to go into each sql task and remove the 09/08/2010 literal and put back in the variable name: @User::StartDate.
    Anyone seen this problem before?

    Comment by David — November 26, 2010 @ 7:41 am | Reply

  7. Hi,

    I had the same issue on my SSIS package but was not using checkpoint files. The task was failing while archiving off a file in the File system object. I couldnt get to the bottom of it so i deleted the object and added a new one, exact same variables passed in and it works.

    weird

    John

    Comment by John Morris — December 7, 2010 @ 4:35 am | Reply

  8. Right click on the script task, don’t do Edit, the variables will be correct their just like the variable pane on the left instead off of right click menu select properties. About 2/3 down the Properties window in the right pane you will see variables. The User variables that you deleted from the variable left hand variable pane are still in that properties cell. Delete them and save pkg. Usually this happens when you use others code and discard unnecessary variables or decide to set them from within the script and not pass them in.

    Just another Friday night discovery.

    Regards,
    R. Torrone
    Realogy Corp
    6/10/11

    Comment by Richard Torrone — June 10, 2011 @ 1:59 pm | Reply

  9. My problem is the same as David’s (#6). I am using variables to store the output of a query and then to rename a file by adding the date i.e., D:\File10.txt should become D:\File10_YYMMDD_hhmmss.txt. The variables are uVar_File10 and uVar_File10Renamed. The package fails with the same message as Aron’s.
    The killer is that there were 2 almost identical containers in the package until I added a third. The first 2 still execute individually, but the new one fails.

    David – did you ever figure out a solution?

    Regards,
    Irene

    Comment by Irene — September 14, 2011 @ 12:08 pm | Reply

  10. I had the same isssue with a sql task. Just deleted the sql task and recreated it problem solved.

    Comment by Olof Szymczak — March 1, 2012 @ 4:09 am | Reply

  11. Am having the same issue with “uploading” ftp task. I have a script that opens the connection, but error is slightly different.
    “Error: The variable “User::vArchived” doesn’t contain file path(s)”
    I tried putting the scope of the variable within this container + and+ tried putting the variable within the single task.

    Sequence Container here

    FTP Task (inside sequence container)
    [IsLocalPathVariable]=True
    [LocalVariable] User::vArchived
    [File Transfer]: Operation = Send files
    [Operation]=Send Files
    [IsRemotePathVariable]=False
    [RemotePath] = /outbox/*.txt
    [OverwriteFileAtDest] = True
    no expressions

    Comment by DNagele — August 28, 2012 @ 10:44 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: