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 :
55 comments:
thats cool....
Im getting following error:-
The error is thrown by Connections collection when the specific connection element is not found
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
This is Good One
Thanks is was exactly what I needed
Thanks is was exactly what I needed too
Thnxx a lottt..!!
Very good it works
Thank you so much!!!!!!!! This is what exactly I am looking for and it worked for me. :)
Pleasure
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?
Thank you!!! I've just lost 1/2 day trying to do the same thing in a wrong way!
Hi , I have got a error stating destination connection manager DestinationConn has an invalid usage type "Folder Exists" KINDLY HELP ME
Well done... thank you!
This is awesome. thanks!
Thanks alot man!!!
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.
Works like a charm, thanks a lot!
You are really kind genius
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.
This is a great post. Very simple to follow and it works! Love it!
Can we move a file into a folder then rename it appending date with the file name?
Good One Man.Carry on with your great work
Finally! An example of a rename and copy that works! Thank you.
perfect, thanks !
Worked like a charm!!! Thanks
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.
I cant down load this package, what do i have to do?
Super helpful...thanks
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.
Sweet! Thanks. I was struggling with trying to set expressions on the File System Task and not the Connection Manager.
Problem: This performs a move & rename not a copy & rename .....
Solution: change operation to copy file
Hello..
We have same situation.
Long Path Tool helped me. http://PathTooDeep.com
You can visit and check out. Thank You!
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
Good Job! Exactly what I'm looking for. Thank you!
Thanks a lot, it solved a lot of issues!
Walter
Thanks for posting your posting, it really helped me to solve my requirement.
Bhandary
Thanks,.... that did the trick!
How can I get it to copy and rename. When I try this it is moving and renaming.
I use a software for this" Long Path Too"l and it really works for deleting copying and renaming files or folder.
Very Nice! Excellent post and precisely what I needed without a lot of grief or endless searching on the WWW. Thank You Sir!
Bob
Just what I needed. Thanks!
Hi Buddy,
It executes only once , if we want to rerun the task its not executing ,is there any alternative to process it.
Hi
I suggest to try "Long Path Tool" program .
before I have problem cannot copy file because file name too long. and after I found long
path tool and now my problem solved
Thanks
Patrick
the maximum ... thanks
It is usefull software for copy, delete, long path files.
Try "Long Path Tool" program.
Thank you for such a wonderful Information !!
Here is a list of Top LINUX INTERVIEW QUESTIONS
Veritas Cluster Interview Questions
SAMBA Server Interview Questions
Linux FTP vsftpd Interview Questions
SSH Interview Questions
Apache Interview Questions
Nagios Interview questions
IPTABLES Interview Questions
Ldap Server Interview Questions
LVM Interview questions
Sendmail Server Interview Questions
YUM Interview Questions
NFS Interview Questions
Read More at :- Linux Troubleshooting
I would recommend in this case to try program Long Path Tool
very helpful. thank you
i need to do this for some files is possible ?
Exactly what I needed! Perfect solution for single file archiving and date-stamping, when looping through a directory is NOT required! Thanks Rahul - you're a stud brother!
many many thx Rahul
exactly what I was looking for and documented in a way that I was able to use it without any experience with SSIS.
I would recommend you to try Long Path Tool program to fix this problem
Worked great for me. Thanks!
Post a Comment