In my case, I was supposed to take all excel file from a folder with similar schema and load them in table and then move to Archive folder.
Create a variable (right click on control flow tab and choose varaibles ) FileName.
FilePath will contain output of ForEachLoop that will hold complete path(path,file name and extension).
So i set up a For each Loop container to get all excel files and hold in variable FileName.
After loading files in database(Data Flow Task) i used File System Task .
I chose Rename instead of move file in File System Task,Already i had source connection to the files (FileName Variable above)
**Don’t forget to choose source connection as variable in file System Task and then coose FileName Variable in drop down.
I needed a destination connection so i created another variable FilePath
Click on properties like shown below and choose TRUE
Now finally set expressions of this variable with below:
substring(@[User::FileName],1,len(@[User::FileName])-findstring(reverse(@[User::FileName]),”\\”,1)+1)+”Archive”+”\\”+”Bak”+”_”+(DT_STR, 4, 1252) YEAR( GETDATE() )+(DT_STR, 2, 1252) MONTH( GETDATE() )+(DT_STR, 2, 1252) DAY( GETDATE() )+”_”+ right(@[User::FileName],findstring(reverse(@[User::FileName]),”\\”,1)-1)
In your case Archive folder can be in different location,please modify this code accordingly.
Then choose destination as variable File System task and choose this Variable.