Delete files older than retention period from folder with parameter using Script Task in SQL Server - SSIS by serverku

After using some of the ways to delete the files from the particular folder like Delete file using Script Task and File System Task in SSIS. We have seen such methods and used logic to get the files from the loop container and process to delete them and the same thing applied for the scripts as well. We also looked for the file deletion which are older than some Retention period with a Script Task also.

In earlier posts we have not used any parameters in the scripts and directly applied folder path and retention period values there. But here I would like to use parameters for the folder path and retention period and pass through the scripts and delete them as per the condition.

1. Let check the files from the target folder.

2. Create parameters and set the values. Here it is going to be delete the files, which are older than 3 days from the E:\ImagesBackup folder in this case.

3. Drag and drop File System Task

4. Put the parameters as ReadOnlyVariables

5. Apply attached script in editor which have additional logic with condition to check the file last modified date and check if older than specified retention period or not. Here you can see the parameters used in the script.

Please note here we need to import system.IO namespace.

6. Turn on the final step and run package. Files older than the specified retention period to get deleted.

You can use the script below for the same as mentioned in the above image,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = Convert.ToInt32(Dts.Variables["User::Period"].Value.ToString());
string directoryPath = Dts.Variables["User::BackupFolder"].Value.ToString();
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.*");

foreach (string currFile in oldFiles)
  {
         FileInfo currFileInfo = new FileInfo(currFile);
              
            if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Delete files older than retention period from specified folder using Script Task in SQL Server - SSIS by serverku

Earlier we have seen in the Delete file using Script Task and file deletion using File System Task in SSIS. We have directly written the logic to get the files and delete from specified folder in the script for the Script Task. For File System Task we have Used Foreach Loop Container and variables to hold file names which are passed from earlier stage one by one and then finally used by the File System Task to delete it.

Here we have same, but don't delete all the files inside pass folder, but get deleted files which are older than the specified days. This is nothing but minor change in the script where we pass the folder path and retention period. Let's start the steps as follow,

1. Let check the files from the target folder.

2. Drag and drop Script Task.

3. Open script editor from the properties.

4. Apply attached script in editor which have additional logic with condition to check the file last modified date and check if older than specified retention period or not.

Please note here we need to import system.IO namespace.

5. Turn on the final step and run package. Files older than the specified retention period to get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here
int RetentionPeriod = 3;
string directoryPath = @"E:\ImagesBackup";
string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.png");
     foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                if (currFileInfo.LastWriteTime < (DateTime.Now.AddDays(-RetentionPeriod)))
                {
                    currFileInfo.Delete();
                }
            }

Hope you liked this post. Stay tuned for more.

Archive old database backup files using TSQL Script - SQL Server by serverku

I experienced into one issue for database backups were failing. And this was due to space issues on the disk drive. This disk drive is specific to allocate for the database backups only. The space was eaten by this database backups and this drive contains so many old backups. I have manually deleted all files and continue this activity so many days. If you create a maintenance plan then it have the option to delete old backup files. But I have the stored procedure for the database backups. So I do not have the option to delete old and unused database backup files.

Finally, I have created a script to clean those old backups. I have created one stored procedure in which you need to pass their parameters, One is Backup type as want to delete full, differential or transaction log backups. Second is From days and the third one is End day.

Please make sure XP_CMDSHELL is enabled on the database instance as this is required to enable it to delete database backup files to be deleted physically. Here is a query to enable it.

USE MASTER
GO

EXEC SP_CONFIGURE 'show advanced options',1
GO
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
GO
RECONFIGURE
GO

I have already told you as I have created scripts to delete the old DATABASEPROPERTY backups, please find below SP for the same.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteDBBackupOldFiles]
@BackupType char(1) = 'D', -- 'D'-Full, 'I'-Differential, 'L'-Log backup type
@StartDayFromToday int,
@EndDayFromToday int
AS
BEGIN
SET NOCOUNT ON

DECLARE @IsFileExists int
DECLARE @DeletedFile VARCHAR(500)
DECLARE @OldFiles VARCHAR(500)

DECLARE OldFiles CURSOR FAST_FORWARD  FOR
SELECT 
bmf.physical_device_name
from msdb.dbo.backupset bs 
INNER JOIN msdb.dbo.backupmediafamily bmf
ON (bs.media_set_id=bmf.media_set_id)
WHERE DEVICE_TYPE = 2 
AND TYPE = @BackupType 
AND BACKUP_START_DATE < = GETDATE() - @StartDayFromToday
AND BACKUP_START_DATE > = GETDATE() - @EndDayFromToday
-- AND DATEDIFF(DAY,BACKUP_START_DATE,GETDATE()) > BETWEEN @StartDayFromToday and @EndDayFromToday 

OPEN OldFiles

FETCH NEXT FROM OldFiles INTO @OldFiles

WHILE @@FETCH_STATUS =0
BEGIN

EXEC XP_FILEEXIST @OldFiles,@IsFileExists OUTPUT
IF @IsFileExists=1
BEGIN

PRINT 'Deleted File : ' + @DeletedFile

SET @DeletedFile = 'DEL ' + @OldFiles

EXEC XP_CMDSHELL @DeletedFile

END
FETCH NEXT FROM OldFiles INTO @OldFiles
END
CLOSE OldFiles
DEALLOCATE OldFiles
END

Finally, after creating a stored procedure, it is time to run and clean the old database backups. After running it will remove all the old database backups as per specified parameters. Like we have passed 'D', 3 and 10 with SP, So I will delete Full database backups while being older than 3 days ago and 10 days before created.

EXEC [DeleteDBBackupOldFiles] 
  @BackupType  = 'D',
  @StartDayFromToday = 3,
  @EndDayFromToday = 10

Which method you use for old database backup maintenance? You can read my earlier posts for deleting files using File System Task and Script Task in SSIS.

Delete files from specified folder using Script Task in SQL Server - SSIS by serverku

Earlier we have seen for the file deletion using File System Task in SSIS. We have used it in Foreach Loop Container. Used a variable to hold file names which are passed from earlier stage one by one and then finally used by the File System Task to delete it.

Now I am going to use the Script Task to delete all files from a specified folder. Here I have added script to get each file from specified folder and then delete them as you can see in the following steps. Let's start to follow them.

1. Drag and drop Script Task.

2. Open script editor from the properties.

3. Apply attached script in editor and save it.

Please note here we need to import system.IO namespace.

4. Turn on the final step and run package. Files get deleted.

You can also find script code here,

--// You need to apply below one line in "namespaces" region.
using System.IO;

--//You need to apply below lines inplace of  // TODO: Add your code here

 string directoryPath = @"E:\TestFolder";
            string[] oldFiles = System.IO.Directory.GetFiles(directoryPath, "*.txt");
            foreach (string currFile in oldFiles)
            {
                FileInfo currFileInfo = new FileInfo(currFile);
                currFileInfo.Delete();
              
            }

Hope you liked this post. Stay tuned for more.

Delete files from specified folder using File System Task in SQL Server - SSIS by serverku

We know all the various methods to delete the particular or all files from the specified folder using some of the methods like xp_delete_file, Ole Automation Procedures and with xp_cmdshell command line utility which we used for the old files archive or cleanup purpose. Here I am going to share some of the screens which delete the files with SSIS using File System Task. Let me share them one by one.

1. Folder having some test files,

2. Drag and drop Foreach Loop Container and File System Task. Foreach Loop Container used to get all the files inside that folder one by one and process with File System Task. Open the Foreach Loop Container properties, GOTO Collection tab and select Foreach File Enumerator as specified in the screen below.

3. Select the folder from where need to delete the files. Also apply extension if you need.

4. For the file assignment, we need one variable and the values allocated from the Foreach Loop Container process. So assign it from variable mapping as per shot taken below.

5. Drag File System Task, select Delete File operation and define Source Connection.

6. Finally, run package and files will get deleted.

Please write your comments.

My recent blog posts and just learned tips - SQL Server by serverku

Recently I have posted some of the articles and some just learned from the month of October. This article just summarizes all the posts which recently published. You can find the links below to go into the details.

Blog Posts:

• The database owner SID recorded in the master database differs from the database owner SID recorded in database
• Database Backup files Verification and Details
• Grouping Sets vs Native method of Group By - Performance review
• Multi server Query with Central Management Servers
• How to reset SQL Server all default settings in SQL Server Denali CTP3
• Stored Procedure Internal Activation in Service Broker
• New Logical functions coming in SQL Server Denali CTP3
• The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
• New String functions arrived by SQL Server Denali CTP3
• Service Broker - Conversation between Databases
• Backup Statistics and History
• NOLOCK Hint & READ UNCOMMITTED Isolation level On table and Query/Session level
• Can not add a shared registered server with the same name as configuration Server
• Database Backup Compression, Amazing feature for DBA
• Review of some replication issues and workaround
• Multi Monitor Support of SSMS

Just Learned tips:

• Script to download file via FTP in SQL Server
• Perform database backup using SQLCMD utility in SQL Server
• Could not connect to server 'RemoteServer' because 'LocalServer' is not defined as a remote server in sql server
• Could not find stored procedure 'sp_MSins_TableName' in replication in sql server
• There is insufficient system memory to run this query
• Change SQL Server instance name
• Adding a transactional pull subscriptions to a publication in replication
• Adding Indexed views in publication for transactional replication
• Adding the transactional publication in Replication
• Enabling the replication database
• Add Functions in publication for transactional replication using tsql script
• Add Stored Procedures in publication for transactional replication using script
• Script to add views in existing publication in transactional replication
• Script to add table in existing publication in transactional replication

Hope these posts help you.