SSIS:Set expressions of a variable,Rename in File System task

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.

Advertisements