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

Scenario:

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.

Solution:

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

FileName:

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

OutputMainFolder: 

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

SourceFolder: 

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;
#endregion

namespace ST_81929bb39573409283a321ba72401cba
{
       [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    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
        };
        #endregion

    }
}


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.


4 comments:

  1. I feel SSIS is the most useful and complex visualisation tool to help offer more and more solutions to large complex IT problems.

    SSIS Postgresql Write

    ReplyDelete
  2. I am able to run the package successfully , But folders are not creating

    ReplyDelete
  3. I am grateful for this blog to distribute knowledge about this significant topic. Here I found different segments and now I am going to use these new instructions with new enthusiasm.Send big files free

    ReplyDelete
  4. This is a game that predicts the outcome of the game by betting on the Toto page. If you win, the dividends will be paid out according to the set dividend rate. Therefore, it is important to choose a safe toy with a high dividend rate. 토토사이트 배팅사이트 안전놀이터

    ReplyDelete