Blog Detail

SQL SERVER: SSIS - Rename and move files from source folder to destination folder
10 Sep 2014
Tejas Shah
/Project/ImageURL/2014-09-02_1548_thumb[5].png
With SSIS, most of the time we need to process all the files from the folder. Once file has been processed, we need to move the file to archive folder, so we should know that file has been processed and we have the file in archive folder. Here, We are going to process the files and then will move the file from source folder to archive folder by appending date and time to the filename, so we can use it for future reference. SSIS will do both of these things, Rename a file and move a file, with File System Task with operation “Rename file”. Let’s review how it works:
1. Add Variables:To configure Source folder and Archive Folder by variable 
Name Scope Data Type Value
FileName [Package Scope]  string
 
SourceFolder [Package Scope] string [Source Folder Path] 
(example :- c:\Source_Folder)
SourcePath [Package Scope] string  
TargetFolder [Package Scope] string [Target Folder Path] 
(example :- c:\Archive_Folder)
ArchivePath [Package Scope] string  
2. Set variable’s properties expression:Go to Variable->properties and set EvaluateAsExpression and Expression, so we will have Source Path and Archive Path.
Here, we are adding Current Date time to the filename by expression as mentioned below. 
Variable Name EvaluateAs
Expression		 Expression
SourcePath true @[User::SourceFolder]+"\\"+ @[User::FileName]
ArchivePath true @[User::TargetFolder] +"\\"+REVERSE(SUBSTRING(REVERSE( @[User::FileName] ),FINDSTRING(REVERSE(@[User::FileName] ),".",1)+1,LEN(@[User::FileName])- FINDSTRING(REVERSE(@[User::FileName] ),".",1)))
+"_"+ Right("0" +(DT_STR,4,1252) datepart("yyyy", getdate()),2)  
+ Right("0" +(DT_STR,2,1252) datepart("mm", getdate()) ,2)
+ Right("0" +(DT_STR,2,1252) datepart("dd", getdate()),2)+Right("0" + (DT_STR,2,1252) DatePart("hh",getdate()),2) 
+ Right("0" + (DT_STR,2,1252) DatePart("mi",getdate()),2) 
+ Right("0" + (DT_STR,4,1252) DatePart("ss",getdate()),2)+REVERSE(SUBSTRING(REVERSE( @[User::FileName] ),1,FINDSTRING(REVERSE(@[User::FileName] ),".",1)))
 

3. Add For each Loop Container and set properties :Now, lets loop thru the folder and process each file from the folder by “For Each loop”. Here, we can setup the folder by Expressions->Directory use Expression @[User::SourceFolder]. We should also specify which type of files we are going to process, like “txt”, “csv” etc..by specifying the same in “Files”. We are fetching the file name with extension so that option needs to be selected as displayed in the following screenshot.

We need to assign each file name to the variable, by Variable Mappings->set variable [User::FileName] and Index as 0

4. Add File System Task:Add “File System Task” inside “For each Loop Container”
5. Set properties for File System Task:This is the place where we need to setup the operation, which will do our job.
A. Configure SourceVariable
B. Configure DestinationVariable
C. Select operation: “Rename File”, which will rename the file and move it to the Archive Folder as we have specified in variable.
6. Run package and check
With SSIS, it is much simple to process multiple files as mentioned above. 

Tags

SQLYoga Tejas Shah File System Task for each loop Move Files Process all files with SSIS Rename Files

About me

User

Tejas Shah

Microsoft Certified Professional Expert. I have 14+ years of experience in Design and Develop 100+ .Net applications using Asp.Net, C#, Asp.Net Core, VB.Net, SQL Server, MVC, AngularJS, JavaScript, Azure, HTML5 and CSS3.

Read More

Categories

Recent Post