How to Purge Old Files (Delete Old Files From Folders By using SSIS Script Task) - SSIS Tutorial

Scenario: 

You want to create an SSIS Package that will read the folder Path, File Type, Filename like and Retention Period from a Table and then loop through each Folder path and delete required files those retention period is more than defined.


Solution:

We will be using Script Task to delete the files from different folders depending upon the retention period which we will define in our table.

Step 1:

Create create the PurgeInformation table so we can insert different entries for folder from which we would like to delete the files.

CREATE TABLE [dbo].[PurgeInformation] (
    [ID] [INT] IDENTITY(1, 1) NOT NULL
    ,[FolderPath] [VARCHAR](300) NULL
    ,[FileType] [VARCHAR](20) NULL
    ,[RetentionPeriod] [INT] NULL
    ,[FileNameLike] [VARCHAR](100) NULL
    ) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[PurgeInformation] ON
GO

INSERT [dbo].[PurgeInformation] (
    [ID]
    ,[FolderPath]
    ,[FileType]
    ,[RetentionPeriod]
    ,[FileNameLike]
    )
VALUES (
    1
    ,N'C:\SSISTraining\HDW'
    ,N'.txt'
    ,5
    ,N'Log'
    )
GO

INSERT [dbo].[PurgeInformation] (
    [ID]
    ,[FolderPath]
    ,[FileType]
    ,[RetentionPeriod]
    ,[FileNameLike]
    )
VALUES (
    2
    ,N'C:\SSISTraining\SourceFiles'
    ,N'.xls'
    ,4
    ,N'Pkg_Log'
    )
GO

SET IDENTITY_INSERT [dbo].[PurgeInformation] OFF
GO


Step 2: 
Create an SSIS Package by using BIDS or SSDT and Create a variable VarObject of type object.

Step 3:
Use Execute SQL Task to read the values from dbo.PurgeInformation table and load into VarObject variable as shown below.

Query :

SELECT Folderpath
    ,FileType
    ,RetentionPeriod
    ,FileNameLike
FROM dbo.PurgeInformation

Read records from SQL Server Table and Save to Object Type variable in SSIS to Purge old Files



Save query results to Object type variable in SSIS Package for Delete old files from folders 


Step 4:
Bring For-each loop container on Control Flow Pane. We need to use Object type variable in For-each Loop so can read one record on each iteration.


How to use Object type variable in Foreach Loop Container to read all the records one by one in SSIS Package


Map the row columns to variables in For-each Loop Container in SSIS Package

Step 5:
After configuring For-each Loop, Drag Script task inside For-each Loop Container and add all user variables we have created above.

How to add variables to Script Task in SSIS Package to delete old files from folders

Step 6:
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO;

Under public void Main() { 
I have added below code.
string FileDirectory, FileNamePattern, FileType;
int RetentionPeriod;

FileDirectory=Dts.Variables["User::VarFolderPath"].Value.ToString();
FileType=Dts.Variables["User::VarFileType"].Value.ToString();
FileNamePattern = Dts.Variables["User::VarFileNameLike"].Value.ToString();
RetentionPeriod =Convert.ToInt32(Dts.Variables["User::VarRetentionPeriod"].Value.ToString());
var files = new DirectoryInfo(FileDirectory).GetFiles(FileNamePattern + "*" + FileType);
// MessageBox.Show("OUT of Loop");
foreach (var file in files)
{

// MessageBox.Show(" I AM IN LOOP");

if (file.CreationTime < DateTime.Today.AddDays(-RetentionPeriod))
    {
        MessageBox.Show(file.Name.ToString());
        file.Delete();
    }
}


SSIS Package to delete old files from different folders

Your SSIS Package is ready, You can schedule your package by SQL Server Agent nightly or can run on demand to delete files from different folder with different retention period.

You can add new folder path in table to delete required files.

5 comments:

  1. It looks like the rest of your C# code got chopped off. Any chance you can re-post?
    Thanks.

    ReplyDelete
  2. Hi Larry,Never got chance to fix the chopped code, Sorry for that. I have fixed the code. Hope this will help.. Thanks

    ReplyDelete
  3. Hi Aamir, i am looking for the solution to delete the file which is already locked by another process in SSIS. Could you please make a video for the same with best approach for this, may be unlock the same file and then try to delete.

    ReplyDelete
  4. Thank you so much for providing such genuine information about SSIS and its utilities.

    SSIS PostgreSql Read

    ReplyDelete