Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

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

Mar 4 2012 12:00AM by Paresh Prajapati   

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 with following where you have various scenario 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 <n> number of files, not based on days. Such a way we can keep <n> 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 different number of files in each folders 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 table which really need to be archived. We have inserted folder paths and number of the files which need to keep for each folders. Let's view the records in table,

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

It fetch the folders and archive numbers for each row and assign to variable which is object datatype. For variable assignment you need set ResultSet option to 'Full result set' as above. After that you mapping variables as follow 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 another 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 values mapping and using them in the script. You need to enable xp_cmdshell from configuration.

Same as earlier steps we need to put the SQL query in SQLStatement 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 get the resulted values in another variable and pass them to next step,

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

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

Now use the following script to perform actual task 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 folders.

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

Tags: #sql, #sql Server, Archive, database, ms sql, ms sql server, retention, SQL, SQL Scripts, SQL Server, SQL Server General, SSDT, SSIS,

Paresh Prajapati
6 · 23% · 7379


Your Comment

Sign Up or Login to post a comment.

"SSIS - Archive files based on number of files configuration in SQL Server" rated 5 out of 5 by 4 readers
SSIS - Archive files based on number of files configuration in SQL Server , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]