November 20, 2007 at 9:23 pm
Im trying to copy files from one directory to another in what i tought would be a simple way
Im using a foreach loop. in the folder the foreach loop is looking at i have two files. I have a variable mapping to "FileName" to pick up just the file name and extension. If i put a Script task inside the loop with a simple msgbox , I get the filenames in a msgbox, 1 for each loop. Now is where i get confused . if i put a file system task in and set the destination as a variable and use "MyDestFile" as the variable. I use the following expression in the system file task
Destination - @(User::MyDestFile) = @(User::NewFolder) + (User::FileName). I can Evaluate the Expression and get "C:\Temp\Test1.txt" ... all good so far, however when I try and execute the foreachloop i cone up with the following error
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Copy File To Daily Files: Failed to lock variable C:\Temp\" 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 at Copy File To Daily Files [File System Task]: An error occurred with the following error message: "Failed to lock variable C:\Temp\" 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 at Copy File To Daily Files: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
I can not seem to get around this.. I know the filename is being generated by the foreach loop, and i know the folder exists... what am i overlooking ??
November 21, 2007 at 4:21 am
Hi Peter,
Try this: Remove the ConnectionString expression from the File System Task. Edit the properties of the @[User::MyDestFile] variable. Set the EvaluateAsExprerssion property to True and enter your logic (@[User::NewFolder] + @[User::FileName]) in Expression Builder.
I'm not sure of hte impact of adding an assignment operation to the expression (assuming you are truly assigning @[User::MyDestFile] in the expression.
Hope this helps,
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
November 22, 2007 at 7:32 am
Check the scope of the variable. SSIS has all those pretty little boxes and you can scope the variable to any one of them.
February 12, 2008 at 7:04 pm
I'm having the same problem, but I don't understand the previously posted solution. There is no ConnectionString property on the File System Task.
I have a Script Task that creates text files and updates variables with the complete file name. I'm using Daniel Read's Read/Write Variable code & I can re-run the package, create new files & update the variables multiple times so I know they are not locked.
But the File System task keeps erroring out with the "Failed to lock variable error" message.
I've really tried to keep an opened mind on this whole SSIS (DTS revamp), but it's been nothing but a misery. Nothing is logical or based on VB programming. It seems that once again, MS has gone overboard without a clue to the real world. Sorry for the rant, but I've spent 2 weeks trying to do something so simple it's a joke. In DTS with scripting, I could have been done in 2 days.
February 13, 2008 at 1:37 am
This job took me about ten minutes to set up the first time I did it - import from each file in a directory and move them to a "processed" directory. I used the Wrox book "Professional SQL Server 2005 Integration Services" as my bible plus various articles from the net including the databasejournal series.
My destination flat file connector doesn't include the file name, just the directory - after all you don't need it DOS do you?
My variable is global to the package - click on the background of control flow outside any component before adding a variable.
Edit for each loop container collection page and set folder to folder name and files to e.g. *.xls
retrieve file name fully qualified
On variable mappings page set variable name and index 0
Do your data flow task then connect to file system task
Edit the file system task and set:
Is destination path variable - No
Destination connection - name of flat file connection
Operation - move file
Is source path variable - true
sourcevariable - variable name - the same as in the for each container
It works for me.
February 13, 2008 at 10:20 pm
Thanks for your help. I tried implementing as you outlined, with a couple of differences. All the files I want to move are created in a Script Component (I made sure all the text files are flushed & closed at the end of the script code). I added the ForEach Loop with the set-up as you outlined and changed my File System task per your outline. You didn't say what the variable was supposed to represent. I assumed it was a file name because you said to use it in the ForEach loop's variable mapping page.
Unfortunately, I still get the same error. I'm beginning to think it's because of the script component. I changed the contraint to be Complete hoping that would fix it, didn't.
Could it be trying to move the files while it's still writing them? Even through the Contraint says Complete?
Thanks.
February 14, 2008 at 11:56 am
I found the solution, not ideal but it works. I had to put the ForEach Loop & File System Task in another Control Flow. Sorry but I still say the whole set-up for this simple set of tasks was convoluted and illogical. If something is labeled variable usage, you should be able to use a variable, not trick it into using a variable.
They need to implement the KISS method at Microsoft.
Thanks again for your help. It did get me throught the loop & file task.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply