Tuesday, April 21, 2009

Copy/Rename a file using File System Task in SSIS

This is a reference answer to a post on MSDN forum but is very useful

Question: While copying and renaming a file to a dynamic location through File System Task using variable throws an 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.".
OR

How to copy and rename a file to a dynamic location using SSIS?

Solution:

I will move and rename a file "test.txt" from c:\ to c:\backup with new name appended with date.

1) Create three variables

Src_File = c:\test.txt
Dest_File = test
Dest_Dir = c:\backup


2) In connection Manager create a File Connection with name as DestinationConn

3) In Expression property of DestinationConn use ConnectionString and provide following expression

@[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"



this will set the destination path and new name for the file

4) Create a File System task and configure like :




5) Execute the package

To run the package create a file named "test.txt"  and a folder "backup"at C:\

Thanks!!
Please leave a comment.

42 comments:

Anonymous said...

thats cool....

Anonymous said...

Im getting following error:-

The error is thrown by Connections collection when the specific connection element is not found

Anonymous said...

hi

need help
In above expression we get getdate as file name . But in my case i need one day previous date from Tusday to Friday and on Monday 3 days previous date .

Could you plz help .

Thanks

Anonymous said...

This is Good One

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Thanks is was exactly what I needed

Anonymous said...

Thanks is was exactly what I needed too

Anonymous said...

Thnxx a lottt..!!

Anonymous said...

Very good it works

Anonymous said...

Thank you so much!!!!!!!! This is what exactly I am looking for and it worked for me. :)

Rahul Kumar said...

Pleasure

Anonymous said...

I've been trying to do similar but with several files. What's the set up on the ForEach Loop Container? Can it be done?

Anonymous said...

Thank you!!! I've just lost 1/2 day trying to do the same thing in a wrong way!

Anonymous said...

Hi , I have got a error stating destination connection manager DestinationConn has an invalid usage type "Folder Exists" KINDLY HELP ME

Anonymous said...

Well done... thank you!

Anonymous said...

This is awesome. thanks!

Anonymous said...

Thanks alot man!!!

Anonymous said...

problems copying or renaming files

website:LongPathTool.com - unlock and delete path too long files

When you are having problems with deleting, copying or renaming files
on your computer, you should get an effective tool. You can find
Windows tool to delete or copy folders and files with errors such as
path too long or the filename too long. Basically search the file and
then press the button to delete or copy it. Path too long application
solves problems with the capacity to delete or copy files and folders
with the long paths in Explorer. At times, errors are created by
software programs, virus, adware and more.

Anonymous said...

Works like a charm, thanks a lot!

Anonymous said...

You are really kind genius

Muhammad Sohail said...

I like this trick. This trick is good for users who love to play with technical ways. However, I have used “Long Path Tool” in this situation and it helped me easily and quickly. So this tool is best for those members who don't love to play with technical skills.

Anonymous said...

This is a great post. Very simple to follow and it works! Love it!

Smaranika said...

Can we move a file into a folder then rename it appending date with the file name?

Anonymous said...

Good One Man.Carry on with your great work

Anonymous said...

Finally! An example of a rename and copy that works! Thank you.

Anonymous said...

perfect, thanks !

Anonymous said...

Worked like a charm!!! Thanks

Free Facebook Likes and Fans said...

Yes this method really works on my blog. I am thankful to your article and suggesting also to other friends to have a look on it.

Anonymous said...

I cant down load this package, what do i have to do?

Allan McLuckie said...

Super helpful...thanks

- said...

Thank you very much for this great tool. It work very well when you run it in Visual Studio.
Unfortunately, when I try to run teh package from a SQL Job, I got the following error :

Error Code: 0xC0012024 Source: TimeStamp & Backup Description: The task "TimeStamp & Backup" cannot run on installed (64-bit) of Integration Services. It requires Standard Edition (64-bit) or higher. DTExec: The package execution returned DTSER_FAILURE

Any idea why ? Thank you again for your work.

Erik said...

Sweet! Thanks. I was struggling with trying to set expressions on the File System Task and not the Connection Manager.

Anonymous said...

Problem: This performs a move & rename not a copy & rename .....

Solution: change operation to copy file

JEKute said...

Hello..

We have same situation.

Long Path Tool helped me. http://PathTooDeep.com

You can visit and check out. Thank You!

AttilaNab said...

Hello. i was also having the same issue but Long Path Tool helped me in this situation. You can see here http://PathTooDeep.com. It might help you.
Thanks and Regards,
Attila

Anonymous said...

Good Job! Exactly what I'm looking for. Thank you!

Anonymous said...

Thanks a lot, it solved a lot of issues!
Walter

Unknown said...

Thanks for posting your posting, it really helped me to solve my requirement.

Bhandary

Larry Heppelmann said...

Thanks,.... that did the trick!

Anonymous said...

How can I get it to copy and rename. When I try this it is moving and renaming.

garry dam said...

I use a software for this" Long Path Too"l and it really works for deleting copying and renaming files or folder.

Anonymous said...

Very Nice! Excellent post and precisely what I needed without a lot of grief or endless searching on the WWW. Thank You Sir!

Bob

Post a Comment