SSIS - How to move files to difference folders according to the date part in file name


I have written a post that explains how to create a folder with date by using SSIS Package. You can check here. A reader asked me in comments that if it is possible to move the files to folders according to the date part in the file name.

If the folder already exists with date part then the process will not create new folder. In case the folder does not exist and we have the file with new date, the folder will be created and file/s will be moved to that folder.


We will be using For-each loop with Script Task to perform this task. Let's start step by step.

Step 1:

Create new SSIS Package. Inside SSIS Package created below variables as shown in fig.
 Fig 1: Create variables in SSIS Package


This variable will save the file name with extension in For-Each Loop and we will use in Script Task.


This is the folder in which folders will be created by date part of file name if does not exist.


This is the folder in which our source files exists those need to be moved to different folders according to the date part in file name.

Fig 2: Sample Source files with Date

Fig 3: Main output folder

The main folder already has one folder. Once we will execute the SSIS Package, two more folder should be created and files should be moved to already existing folder and newly created folder.

 Step 2: 
Bring the For-Each Loop Container and configure as shown below.
Fig 4: For-Each Loop Container to loop through source files.

 Fig 5: Map the FileName variable in For-Each Loop Container

Step 3:
Bring the Script Task and place inside the For-Each Loop Container. Double click on Script Task and add the script as shown below.
Fig 6: Place Script Task inside For-Each Loop Container.

Double click on Script Task and configure as shown below.

Fig 7: Map the variables to Script Task

Once you click on Edit Script as shown in #3. Place the below code in Script Task.

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;

namespace ST_81929bb39573409283a321ba72401cba
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        public void Main()
            string filename;
            string datepart;
            bool FolderExistFlg;
            filename = Dts.Variables["User::FileName"].Value.ToString();
            datepart = (filename.Substring(filename.Length - 12)).Substring(0,8);
            FolderExistFlg = Directory.Exists(Dts.Variables["OutputMainFolder"].Value.ToString() + "\\" + datepart);

            if (!FolderExistFlg)
                Directory.CreateDirectory(Dts.Variables["OutputMainFolder"].Value.ToString() + "\\" + datepart); 

            File.Move(Dts.Variables["SourceFolder"].Value.ToString() + "\\" + filename,
            Dts.Variables["OutputMainFolder"].Value.ToString() + "\\" + datepart+"\\"+filename);
            Dts.TaskResult = (int)ScriptResults.Success;

        #region ScriptResults declaration
        enum ScriptResults
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure


using System.IO; is the line that I added and the code added in public void Main(). The rest is auto generated. You can copy that and paste in your Editor. Save the code and then close the window.

Let's run our SSIS Package and see if folders are created and files are moved to required folders.
Fig 8: Output folders with moved files

As we can see that new folders are created if not exist and files according to the date part are moved to the related folder.