In some ETL scenarios, when processing files, it is necessary to rename the already processed files and move them to a different location. In SSIS you can accomplish that in a single step using the File System Task. The example I have prepared assumes the package will process a set of files using a ForEach Loop container; then for each file, using the 'Rename' operation in File System Task will do both; rename and move the file.
Here are some screen shots and notes about the package:
First of all, the big picture. The control flow has a ForEach Loop Container with a File System Task inside. Notice that the DataFlow task is empty and it is intended to show where the real ETL work should go; but this can be different or not required at all.
Then details about the ForEach Loop container. Basically ,this container is configured to process all *.txt files in C:\Temp\Source folder, where all the files 'to be processed' are expected to be.
Now the trick, few variables, some of them using expressions:
The expressions are:
in FullSourcePathFileName:
@[User::SourcePath] + @[User::MyFileValue]
in FullArchivePathFileName:
@[User::ArchivePath] + SUBSTRING( @[User::MyFileValue] , 1 , FINDSTRING( @[User::MyFileValue],".",1) - 1 ) + "-" + (DT_STR, 2, 1252) Month( @[System::StartTime] )+ (DT_STR, 4, 1252) Year( @[System::StartTime] )+ SUBSTRING( @[User::MyFileValue] , FINDSTRING( @[User::MyFileValue],".",1) , LEN( @[User::MyFileValue] ) )
Notice that SourcePath and ArchivePath variables hold only the origin and destination paths of the files.
Note: Make sure you set EvaluateAsExpression property of the variable as TRUE.
Lastly, the File System Task should be configured like this:
I am pretty sure there are different ways of accomplishing this simple task; but I like this one because it does not require writing custom code and relies on expressions.
You can download the sample package from here:
