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


SSIS File System Task - Rename File


SSIS File System Task - Rename File

Author
Message
Maximus-1065478
Maximus-1065478
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

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

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.
Maximus-1065478
Maximus-1065478
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 44
There in the base package.
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

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

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.
Famson
Famson
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 230
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:BigGrinest_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!
suhel.choudhury
suhel.choudhury
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 36
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
jdgonzalez@jdandrachel.com
jdgonzalez@jdandrachel.com
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1241 Visits: 760
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.
ehuhn
ehuhn
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 52
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.
Steve McEwen
Steve McEwen
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 166
Thanks ehuhn, that fixed my problem.
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