Skip to main content

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.


  1. Paresh Provides Online Training by Real Time Professionals from Hyderabad,India ,For Testing Tools|SQL Server 2012 DBA Online Training|MSBI|.net 4.0|DOT Net 4.0|Selenium Online Training|Mobile Testing|Database Testing|Sharepoint training Institute In India.

  2. Paresh is known to be a reputed consulting firm for SQL SERVER related
    Courses with top talented database professionals and software engineers
    coming from some of the most established and most successful software developmentand
    technology consulting firms around the globe for providing a tailored-made solutions to
    their clients at the very best of their knowledge and liable time.

  3. Dear friend. I truly just like your posting and your current web page all in all! That write-up is really plainly composed and without difficulty understandable. Your Wordpress style is awesome as well! Would be awesome to discover exactly where My partner and i are able obtain this. Please hold up the very good work. We all need much more this kind of website owners just like you on the web and much less spammers. Wonderful man!

  4. Hey very nice blog!!
    Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!
    Awesome post

  5. I can see that you are are genuinely passionate about this! I am trying to build my own website and youve helped me with some great information.

  6. Thank you for this unique article. You guided me well.


Post a Comment