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:
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.
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
, and Twitter
.Freelance Writer: ShadowrunLatchkeys: Nevermore
, Latchkeys: The Bootleg War
, and Latchkeys: Roscoes in the Night
are now available on Nook and Kindle.