Ssis Delete Files Older Than 30 Days

• Ssis Delete Files Older Than 30 Days
• Ssis Delete Files From Directory
• Ssis Delete Files Older Than 30 Days Linux
• Ssis Delete File

By: Basit Farooq | Updated: 2013-06-13 | Comments (9) | Related: More >Integration Services Development

WHERE Datecolumn. DATEADD(dd,-30,GETDATE) To delete records from a table that have a datetime value in Datecolumn older than 12 hours: USE Databasename; DELETE FROM Tablename WHERE Datecolumn. DATEADD(hh,-12,GETDATE) To delete records from a table that have a datetime value in Datecolumn older than 15 minutes: USE Databasename. We have a requirement to delete a group of files that are older than the specified number of days from the company file share. This file share stores sensitive clients extracts (produced by DBAs as part of client's SQL Server Agent extract jobs), database backups, scanned emails, etc.

We have a requirement to delete a group of files that are older than the specified number of days fromthe company file share. This file share stores sensitive clients extracts (produced by DBAs as part of client'sSQLServer Agent extract jobs),database backups, scanned emails, etc. Moreover, due to the complex folder hierarchy and delicate nature of the data stored in these files, this task has to be originated from SQL Server. However, due to company security policy, and based on SQL Server security best practices, we blocked access to OLE Automation stored procedures, CLR features, and xp_cmdshell. Is there any way to accomplish this task without using these features? Check out this tip to learn more.

• This can be achieved very easily in SSIS. Create an integer variable to store the value stating how old files do you wish to delete. Suppose you wish to delete all files older than 3 days, the variable would have the value 3. I name this variable intFileMaxAge. Make this variable configurable so that it can be changed as and when required.
• Advanced SSIS File System Task – SSIS Delete Multiple Files (Use wildcard) How to Delete files older than X days – Advanced Sorting and Filtering.

WHERE Datecolumn. DATEADD(dd,-30,GETDATE) To delete records from a table that have a datetime value in Datecolumn older than 12 hours: USE Databasename; DELETE FROM Tablename WHERE Datecolumn. DATEADD(hh,-12,GETDATE) To delete records from a table that have a datetime value in Datecolumn older than 15 minutes: USE Databasename. We have a requirement to delete a group of files that are older than the specified number of days from the company file share. This file share stores sensitive clients extracts (produced by DBAs as part of client's SQL Server Agent extract jobs), database backups, scanned emails, etc.

We have a requirement to delete a group of files that are older than the specified number of days fromthe company file share. This file share stores sensitive clients extracts (produced by DBAs as part of client'sSQLServer Agent extract jobs),database backups, scanned emails, etc. Moreover, due to the complex folder hierarchy and delicate nature of the data stored in these files, this task has to be originated from SQL Server. However, due to company security policy, and based on SQL Server security best practices, we blocked access to OLE Automation stored procedures, CLR features, and xp_cmdshell. Is there any way to accomplish this task without using these features? Check out this tip to learn more.

• This can be achieved very easily in SSIS. Create an integer variable to store the value stating how old files do you wish to delete. Suppose you wish to delete all files older than 3 days, the variable would have the value 3. I name this variable intFileMaxAge. Make this variable configurable so that it can be changed as and when required.
• Advanced SSIS File System Task – SSIS Delete Multiple Files (Use wildcard) How to Delete files older than X days – Advanced Sorting and Filtering.

Well, this requirement can be fulfilled quiet easily by using the following SSIS toolbox components: Script Task, Foreach Loop container, and the File System Task. The following are the steps, which you can follow to accomplish this task using SSIS.

Steps to Build the SSIS Package for File Deletion

• Launch the SQL Server Data Tools, then from the File menu, choose New, and click Project. This will launchthe New Project dialog box.
• In the New Project dialog box, select Integration Services Project fromthe templates pane. In the Name box, change the default name to DeleteRedundantFiles. Also, specify the project location, and onceproperly configured, click OK to create this new SSIS project.
• By default, SQL Server Integration Service Project createsan empty package, named Package.dtsx, which is added to your project. Inthe Solution Explorer, right-click on this package and rename it to DeleteRedundantFilesFromNetwork.dtsx.

1: Defining Package Variables in SSIS

Next, add the following five variables to your SSIS package:

Variable Name: varFileAgeLimit
Variable Data Type: Int32
Description: This variable stores the age limit variable for the file. This variable is used withinthe Script Task, and accepts both positive and negative values. When a negative value is assignedto this variable, the code within the Script Task searches for files that are older thana specified number of days. When the value is positive, the code withinthe Script Task searches the files that are newer than specified number of days.

Variable Name: varFileFQN
Variable Data Type: String
Description: This variable stores the file name withthe path. For example: C:DataFolderText.txt or MyServerMyDataFolderTest.txt.

Variable Name: varFilePattern
Variable Data Type: String
Description: The purpose of this variable is to store file name pattern. For example: *.* or *.bak or DB1_Extract.dat

Variable Name: varNetworkPath
Variable Data Type: String
Description: Stores eitherthe Network location or the Local path, which is the code within the Script Taskthat will be used to search files in that location and within child directories of this location. For example: C:MyLocation or MyServerMyLocation.

Variable Name: varFileList
Variable Data Type: Object
Description: Stores the list of files that will be deleted.

2: Defining Package Tasks

Next, add and configure the following SSIS package tasks:

2.1: Configuring 'Get File List - ST' Script Task

The first task you need for this solution is the Script Task. To add it to your package, simply drag it from SSIS Toolbox to design surface of the Control Flow tab. Now, on the Control Flow design surface, right-clickon the newly added Script Task, then click Rename, and change the name from default name to Get File List - ST.

Next, double-click the Script Task, to open the Script Task Editor, and then configurethe following properties:

• Set the ScriptLanguage property to Microsoft Visual Studio C# 2010.
• Add the varFileAgeLimit, varFilePattern and varNetworkPathpackage variables in the Script task's ReadOnlyVariables property.
• Add the varFileList package variable in the Script task's ReadWriteVariables property.
• Next, click the Edit Script button on the Script Task Editor page. This will open the VSTA integrated development environment (IDE) window.Copy the code below because this will search and return the listof files that are older than a specified number of days in a specified location, and within the child directories of this specified location. Once done, clickthe OK button on the Script Task Editor to save changes to the Script Task.

Microsoft Visual Studio C# 2010 Code to Delete Files in the File System

2.2: Configuring 'Get Individual File FQN - FLC' Foreach Loop container

The next task you need is the Foreach Loop container. To add it to your package, drag it fromthe SSIS Toolbox to designthe surface of the Control Flow tab. Now, on the Control Flow design surface, right-click the Foreach Loop container, then click Rename, and change the name from default name to Get Individual File FQN - FLC.

Next, double-click the Foreach Loop container, to open the Foreach Loop Editor. On left side of Foreach Loop Editor, click onthe Collection, and then configure its properties:

• Change Enumerator property to Foreach From Variable Enumerator.
• Specify varFileList package variable as Enumerator configuration variable.

Now, click on Variable Mappings and select varFileFQN package variable to map to the collection value.

Once done, connect the Script task (Get File List - ST) with the Foreach Loop container (Get Individual File FQN - FLC).

2.3: Configuring 'Delete Files on Remote Directory - FST' File System Task

Finally, add the File System Task to the Foreach Loop container (Get Individual File FQN - FLC). To do that, simply drag it fromthe SSIS Toolbox tothe design surface of the Loop container (Get Individual File FQN - FLC). Then, right-clickon the newly added File System Task, then click Rename, and change the name from default name to Delete Files on Remote Directory - FST.

Next, double-click on the File System Task, to open File System Task Editor, and then configurethe following properties on General page:

• Set Operation property to Delete file.
• Set IsSourcePathVariable property to True.
• Specify varFileFQN package variable as SourceVariable.

All done, our package is successfully configured, and it should look similar to figure below:

Testing

To test the package, simply assign values to the package variables, and then execute the package. For example, I specified the following values to package variables, to delete all files from JW02410Temp shared folder that are older than 2 days.

When I executed the package, it deleted all files from this location and within child directory of this location that are older than 2 days.

• SSIS comes with rich set of tasks that helps us to design and implement various difficult solutions quickly and efficiently. In this tip, we used Script Task, Foreach Loop container and File System Task to delete group of files from specified directory that are older than specified number of days.
• Check out these related tips on MSSQLTips.com:
  • SSIS SFTP Task Control Flow Component on codeplex
  • Review other SSIS control flow component tips
• Check out abaseline version of this package to begin your development project.

Disk Usage Monitoring tools are capable of alerting us when a given threshold is reached.

But they don't have the ingenuity to fix the disk usage problem on their own.

Manual intervention is needed to solve the problem.

But if you want to fully automate this kind of activity, what you will do.

Yes, it can be done using the bash script.

This script prevents alerts from monitoring tool Driver 8 in 1 card reader go c81la. because we delete old log files before filling the disk space.

We have added many useful shell scripts in the past. If you want to check them out, go to the link below.

I've added two bash scripts to this article, which helps clear up old logs.

1) Bash Script to Delete a Folders Older Than 'X' Days in Linux

We have a folder named '/var/log/app/' that contains 15 days of logs and we are going to delete 10 days old folders.

This script will delete 10 days old folders and send folder list via mail.

You can change the value '-mtime X' depending on your requirement. Also, replace your email id instead of us.

Set an executable permission to 'delete-old-folders.sh' file.

Finally add a cronjob to automate this. It runs daily at 7AM.

Ssis Delete Files Older Than 30 Days

You will get an output like the one below. Ten tec 1054 manual.

2) Bash Script to Delete a Files Older Than 'X' Days in Linux

We have a folder named '/var/log/apache/' that contains 15 days of logs and we are going to delete 10 days old files.

Ssis Delete Files From Directory

The articles below are related to this topic, so you may be interested to read.

This script will delete 10 days old files and send folder list via mail.

You can change the value '-mtime X' depending on your requirement. Also, replace your email id instead of us.

Set an executable permission to 'delete-old-files.sh' file.

Ssis Delete Files Older Than 30 Days Linux

Finally add a cronjob to automate this. It runs daily at 7AM.

Ssis Delete File

You will get an output like the one below.