April 20, 2009 at 9:21 pm
I have a file, test.txt that I want to copy/rename in a backup directory.
I have created the following User variables within my SSIS package:
Src_File = c:\test.txt
Dest_File = test
Dest_Dir = c:\backup
DelayValidation = True
I also created an Expression within the File System Task for the Destination:
@[Dest_Dir] + "\\" + @[Dest_File] + "_" + (DT_WSTR,4)DatePart("yyyy", GetDate()) + RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("hh", GetDate()), 2) + RIGHT("0" + (DT_WSTR,2)DatePart("mi", GetDate()), 2) + ".txt"
which gives me the following evaluation value: c:\test\test_200904202005.txt
When I go to debug, the package fails, and thows out the following error:
Error: Failed to lock variable "c:\test\test_200904202009.txt" 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.".
I have been to a number of forums without any advice that has resolved this issue. Is there a bug within SSIS that won't allow you to concatenate a datetime stamp to a string variable? Or am I missing something very rudementary? This should not be this difficult. Any help would be highy appreciated.
April 20, 2009 at 9:28 pm
Can you confirm the scope of your variables?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 20, 2009 at 9:31 pm
There in the base package.
April 20, 2009 at 11:25 pm
That is not what I mean. If you right click white space in the control flow window and select Variables, a list will be displayed. Column 1 is variable name, column 2 is Scope - that is what I want to know.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 22, 2009 at 2:08 am
To resolve this issue , I'll suggest the following:
Create a destination variable e.g Dest_Var
Set its EvaluateAsExpression to "True", and copy and paste the expression you created above into the variable's Expression property
For the File System Task:
Set its IsDestinationPathVariable property to "True"
Set its DestinationVariable propety to the destination variable you created
(usually in the format-scope::Dest_Var)
Set its IsSourcePathVariable property to "True"
Set its SourceVariable property to the source variable you created
(usually in the format-scope::Src_File)
And finally, delete
DelayValidation = True, and the
Destination property you've set in the Expressions tab.
I hope this helps.
Cheers!
September 21, 2010 at 8:19 am
can you please clarify this bit of your instructions:
And finally, delete
DelayValidation = True, and the
Destination property you've set in the Expressions tab.
Thanks
September 21, 2010 at 12:57 pm
I'm just tossing this out as a suggestion only because the file system task seems a bit finicky with me. Why not use a script task to rename the file. I seem to have much better luck with that task.
January 19, 2011 at 8:29 am
I had the same problem, and a co-worker figured out what I was doing wrong.
After you set the IsDestinationPathVariable to True, your variable name goes directly into next property DestinationVariable.
Do NOT (as you might in some other types of tasks) open the Expressions dialog and set it there. If you do, it will treat your destination file name as the variable name.
See in the error message where it says it can't lock the variable 'C:\...' ? That's not a variable name, it's the value of it.
February 7, 2012 at 8:42 am
Thanks ehuhn, that fixed my problem.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply