Ssis Delete Files Older Than 30 Days

1. Mar 16, 2016.
2. Apr 29, 2012.

1. Ssis Delete Files Older Than 30 Days Linux
2. Ssis Delete Files From Directory
3. Ssis Package Delete File
4. Ssis Delete Old Files

Scenario:

One of the post I wrote 'How to delete old files' that you can check Here. In this post we will be deleting the old folders. We will be able to provide the Retention Period in Days. The package will delete all the old Folders older than Retention period in days.

Solution:

I've recently taken up the task to clean my NAS and other storage devices of files not touched in over 6 months to clear up storage space. I once again turn to PowerShell to automate the task. The following script will delete items in my Downloads directory that are older than 6.

In my demo , I want to delete all the folders which are older than 7 days.

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

Problem

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.

Solution

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

Lsi fastpath keygen software. 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.

Next Steps

• 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.

Last Updated: 2013-06-13

Ssis Delete Files Older Than 30 Days Linux

Ssis Delete Files From Directory

About the author

Ssis Package Delete File

Ssis Delete Old Files