Friday, September 21, 2012

SQL Server Integration services - Rename and move files from source folder to destination folder

In this post,i am going to explain about renaming & moving processed files from active folder to archive folder using
Foreach Loop Container and
File System Task




Step 1:

Under Toolbox -> Control Flow Items ,drag&drop Foreach Loop Container  into Control Flow tab as shown in below image





Step 2:
Double-click  Foreach Loop Container ,under Collection tab do the changes as shown in below image:

 



Step 3:
Double-click  Foreach Loop Container ,under Variable Mappings tab,create an variable as shown in below image  for holding filename (for example :
A.txt if you have Name and extension under Collection tab - > Retrieve file name
A  if you have Name only under Collection tab - > Retrieve file name
C:\Users\sathya\Desktop\Source_Folder\A.txt  if you have Fully qualified under Collection tab - > Retrieve file name
)



Step 4:
Under Toolbox -> Control Flow Items ,drag&drop File System Task inside Foreach Loop Container as shown in below image.



Step 5:
Under SSIS - > Variables tab ,create variables as shown in below image



Step 6:
Double-click  File System Task,do the following changes as shown in below image



Step 7:
Click on FileTaskSourcePathVariable and then goto Properties or press F4,
Set EvaluateAsExpression = True and write the Expression and click Evaluate Expression as shown in below image



Step 8:
Click on FileTaskDestinationPathVariable and then goto Properties or press F4,
Set EvaluateAsExpression = True and write the Expression and click Evaluate Expression as shown in below image


Step 9:
Check the files in source folder ,execute the package and then check the files in destination folder.
 

 

 







 
 

2 comments:

Anonymous said...

Very Impressive, It worked perfectly
Thanks for sharing your knowledge

simon M said...

BEst bets best solution in 1 shot, THANK YOU