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 File System Task - Rename File Expand / Collapse
Author
Message
Posted Monday, April 20, 2009 9:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 28, 2011 11:15 AM
Points: 2, Visits: 44
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.
Post #701137
Posted Monday, April 20, 2009 9:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:06 PM
Points: 5,047, Visits: 11,797
Can you confirm the scope of your variables?


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.
Post #701140
Posted Monday, April 20, 2009 9:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 28, 2011 11:15 AM
Points: 2, Visits: 44
There in the base package.
Post #701143
Posted Monday, April 20, 2009 11:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:06 PM
Points: 5,047, Visits: 11,797
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.


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.
Post #701179
Posted Wednesday, April 22, 2009 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 12:55 PM
Points: 47, Visits: 172
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!
Post #702111
Posted Tuesday, September 21, 2010 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 24, 2013 12:15 PM
Points: 3, Visits: 28
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
Post #990341
Posted Tuesday, September 21, 2010 12:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 8:47 AM
Points: 180, Visits: 683
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.
Post #990675
Posted Wednesday, January 19, 2011 8:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 12:17 PM
Points: 2, Visits: 49
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.
Post #1050101
Posted Tuesday, February 7, 2012 8:42 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 4:41 AM
Points: 947, Visits: 156
Thanks ehuhn, that fixed my problem.
Post #1248195
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse