SSIS - How To Delete Old Folders In SSIS Package


One of the post I wrote "How to delete old files" that you can check Here. In this post we will be deleting the old folders. We will be able to provide the Retention Period in Days. The package will delete all the old Folders older than Retention period in days.


Step 1:

Let's create two variables 

Provide the parent folder path in which other folders exist those we need to drop.

Provide the number of days. The package will delete all folders older than days provided in this variable.

In my demo , I want to delete all the folders which are older than 7 days.
Fig 1: Create Variables in SSIS Package for Delete Old Folders

Step 2: 
Check the folders in your parent folder, I have 3 folders in my Test Folder which are older than 7 days as of August 8,2014.
Fig 2: Parent Folder with Folders to be Deleted

Step 3:
Bring Script Task to Control Flow Pane. Open Script Task and add both variables as shown
Fig 3: Adding Variables to Script Task

Step 4:
Click on Edit Script and write below script

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
namespace ST_a1d5cdbbb4b044319ade671733f495e8.csproj
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

        #region VSTA generated code
        enum ScriptResults
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        public void Main()
     string ParentFolder;
     int RetentionPeriod;
     RetentionPeriod = Convert.ToInt32(Dts.Variables["User::RetentionPeriodInDays"].Value);
     var folders = new DirectoryInfo(ParentFolder).GetDirectories();
     foreach (var Folder in folders)
                if (Folder.CreationTime < DateTime.Today.AddDays(-RetentionPeriod))
            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;

Step 5:
Execute the SSIS Package and check the folder if older folders are deleted.
Fig 4: Delete Old Folder with Files in SSIS Package

As we can see that all the older folders are deleted with files and only one folder is left which was created today( 08/08/2018)

No comments:

Post a Comment