how to move files to different folders , based on

Question

I have four files xxxxxxCd999, xxxxCf999, xxxxC999 , xxxxD999 ... I need to move these files to their respective folders based on file name , for example file xxxxxCd999 should be moved to folder Cd999 , file xxxxCf999 should be moved to folder Cf999 ,file xxxC999 should ne moved to folder C999 so on ... How do I achieve this in ssis ?

I have used a for each loop container, assigned some variables for sourcepath, destinationpath , and a file system task to use these variables , but im lost now n have no idea how to proceed , Kindly help me

Answer 1

Try this :-

The Foreach Loop will enumerate the source folder and the path will be stored in a variable. In the script task write a code to get the folder Name using regular expression .The script task value will be stored in another variable which will be used in File System Task

The package design will be

• Create 3 variable

   Name         DataType   Expression
   FolderName    string
   DestLoc       string     "D:\\"+ @[User::FolderName]   
   LoopFiles     string
  

In the above expression for DestLoc variable ,change it as per your location

• ForEach Loop configuration

Change the source folder location as per the need

• Script task -Add the 2 variable as below

• You need to extract the folder name from the variable LoopFiles

Example

LoopFiles variable will have D:\ForLoop\SampleFolder1.txt at runtime

So in order to extract folder name from the above variable use regular expression

Open Edit Script and write the following code

List<string> filePatterns = null;

public void Main()
 {
     filePatterns = new List<string>();
     filePatterns.Add("Folder1");
     filePatterns.Add("Folder2");
     string fileName = Path.GetFileNameWithoutExtension(Dts.Variables["User::LoopFiles"].Value.ToString());
     Match match = Regex.Match(fileName, string.Join("|", filePatterns.ToArray()));
     Dts.Variables["User::FolderName"].Value = match.Value;
     Dts.TaskResult = (int)ScriptResults.Success;
  }

In the above code ,you are extracting the folder name and storing it in the variable FolderName.If you have multiple folders ,then just add the folder names to the filePatterns collection variable.

• File System Task Configuration