Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS 2008 - Failed to lock variable Error (Oh, I fixed it) Expand / Collapse
Author
Message
Posted Thursday, October 6, 2011 5:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 5,676, Visits: 6,488
I have a package in SSIS that I just created. It contains 2 ForEach loop containers. Within each container are 2 File System tasks with a precedence constraint between them. The first FS task copies a file to an FTP directory. The second FS task moves the file to an archive folder. (To reiterate, Copy then Move).

The FS tasks are loaded with variables. I have 5 of them.

1) OutboundDirectory - is used in the enumerator expression of the ForEach and gives the directory to be searched for the file.

2) xx_FileName - populated by the ForEach container with the fully qualified path name of the discovered file, then used as the Source variable for both FS tasks. (there are two of these, one for each ForEach container. They are named differently. "xx" is a stand-in value.).

3) FTPDirectory - used in the FS copy task as the Destination variable.

4) ArchiveDirectory - used in the FS move task as the Destination variable.

The kicker here is if I have one copy of a file to be processed, the entire package works fine. But if I have multiples of a file (ABC_20111002.txt, ABC_20111003.txt, etc.), then the first execution of one container works fine. File is copied, then moved. But the execution of the other container fails on the FS move task with the following error:

SSIS Error

Error: 0xC0014054 at Archive Feed: Failed to lock variable "User::ArchiveDirectory" 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.".

Error: 0xC002F304 at Archive Feed, File System Task: An error occurred with the following error message: "Failed to lock variable "User::ArchiveDirectory" 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.".
".


There appear to be multiple solutions on Google, few of which reference my specific scenario. All of them seem to indicate a complete failure of the package rather than a success, then failure. Also, many of them advise changing the variables to Read Only, which I tried. But this fails my entire package, so it's a no-go as far as I'm concerned.

................................
MAJOR EDIT

Just as I was writing this, I realized what my problem was. I was using the same variable in two different containers and two different threads were trying to write two different file names to it.

For example, I have files that start with ABC_ and files that start with DEF_. I need 2 different containers because you can't put both file names in the enumerator without including files that you may not want to pick up. The Destination variable of a File System Task will try to take both ABC_20111005.txt and DEF_20111005.txt and write them both to the ArchiveDirectory variable. So you might have \\MyServer\Archive trying to get overwritten and used (at the same time) by both file names.

SSIS apparently doesn't like this. So you need two separate variables pointing to the same path name for each individual container to use. And that does work. I've verified it.

I've decided to go ahead and post this because when I searched for this error, I could not find anything remotely similar to my situation. I will also be posting this on my blog for future reference. I hope this helps someone else out.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1186426
Posted Tuesday, June 4, 2013 2:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 2:54 PM
Points: 11, Visits: 269
Hi Brandie,

I'm facing the same issue but not able to figure out wt the issue is .

Im attaching a screenshot of the same error i got . could you give me some direction please .




  Post Attachments 
4-1-2013 4-52-41 PM.pdf (41 views, 30.10 KB)
Post #1459951
Posted Wednesday, June 5, 2013 1:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:15 AM
Points: 5,317, Visits: 12,354
jampabsatish (6/4/2013)
Hi Brandie,

I'm facing the same issue but not able to figure out wt the issue is .

Im attaching a screenshot of the same error i got . could you give me some direction please .




Based on your attached image, it looks like you have entered the value of the variable somewhere, not its name.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1460053
Posted Wednesday, June 5, 2013 4:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 5,676, Visits: 6,488
jampabsatish (6/4/2013)
Hi Brandie,

I'm facing the same issue but not able to figure out wt the issue is .

Im attaching a screenshot of the same error i got . could you give me some direction please .


Are you using a config file / table?

Did you create any variables and then delete them later because you thought you weren't using them?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1460142
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse