SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS 2008 - Failed to lock variable Error (Oh, I fixed it)


SSIS 2008 - Failed to lock variable Error (Oh, I fixed it)

Author
Message
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14894 Visits: 9006
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
jampabsatish
jampabsatish
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 537
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 .
Attachments
4-1-2013 4-52-41 PM.pdf (123 views, 30.00 KB)
Phil Parkin
Phil Parkin
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18921 Visits: 20460
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.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14894 Visits: 9006
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search