You are here:Home » SSIS » Archive files based on number of files configuration in SQL Server - SSIS

Archive files based on number of files configuration in SQL Server - SSIS

Hope you read earlier posts to archive files which are older as per days and you liked them. If you have not read then you can go there by following where you have various scenarios with the same,
  1. Archive old database backup files using TSQL Script - SQL Server
  2. Delete files from specified folder using File System Task in SQL Server - SSIS
  3. Delete files from specified folder using Script Task in SQL Server - SSIS
  4. Delete files older than retention period from specified folder using Script Task in SQL Server - SSIS
  5. Delete files older than retention period from folder with parameter using Script Task in SQL Server - SSIS
Recently I was asked by my friends to delete the files bases on number of files, not based on days. Such a way we can keep number of files every time and rest files get archived. I will give you an example here as I have three folders and want to keep the different number of files in each folder only. Says keep 3 files in BackupFolder1, keep 2 files in BackupFolder2 and keep 1 file in BackupFolder3. So how can we achieve it? Let's move on the steps,

First we will check the files existing in folders,

We are creating a SSIS package here and will use tasks to perform the archive process,

Also the variables used in the whole task flow,

You can see the flow of the tasks, so we will be here for task 1 which will have fetch the records from a table which really need to be archived. We have inserted folder paths and a number of the files which need to keep in each folder. Let's view the records in a table,

Now we will create a task1 with Execute SQL Tasks and get the list of archive folders as figured below,

It fetches the folders and archive numbers for each row and assign to a variable which is object data type. For variable assignment you need to set a ResultSet option to 'Full result set' as above. After that, you mapping variables are as following for output,

We need to go through the values coming out from variable and then map it to two variables again for archive folder name and archive numbers using Foreach Loop Container,

You can see how the object variable holds the values and assigned to other variables again as per below shot,

Now use Execute SQL Task again and get the list of the files which need to deleted, You need to write some SQL statement to get those files as per archive numbers. So we will look the configuration and variable value mapping and using them in the script. You need to enable xp_cmdshell from the configuration.

Same as earlier steps we need to put the SQL query in SQL Statement option and set 'Full result set' for ResultSet, You can see two arrows which will be input values of the script which going to be executed, You can collect the same script here,

Seq int identity(1,1),
FileName varchar (500)

DECLARE @FullPath varchar(500), @sql varchar(500)

SET @FullPath = ?
SET @sql = 'dir /B /O-D '+ @FullPath

INSERT INTO #TempFiles (FileName)
EXEC xp_cmdshell @sql

SELECT @FullPath + FileName  as FullName
FROM #TempFiles
WHERE FileName is not null
AND Seq > ?


After completion of above to get the resulted values in another variable and pass them to the next step,

Use Foreach Loop Container to process, delete operation for each files same steps we did earlier for Foreach Loop,

Finally, use the Script Task to delete the file one by one as I did in below image. You need to pass the value of the file name as read only,

Now use the following script to perform actual tasks for deletion, Use below script in your script task,

You can copy same script from here,
--//Apply below line in #region namespaces
using System.IO;

--//Apply below code inside 
public void Main()
string FilePath = Dts.Variables["User::FileName"].Value.ToString();
Dts.TaskResult = (int)ScriptResults.Success;

After running the package files will be deleted as per archive number defined in the tables for each folder.

You can schedule this package in some frequency. Hope this help you.