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.".


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


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:\

Please leave a comment.

Wednesday, April 01, 2009

Trail Period is Over - the funny side