Scenario:
You want to create an SSIS Package that will read the folder Path, File Type, Filename like and Retention Period from a Table and then loop through each Folder path and delete required files those retention period is more than defined.
Step 1:
Solution:
We will be using Script Task to delete the files from different folders depending upon the retention period which we will define in our table.
Create create the PurgeInformation table so we can insert different entries for folder from which we would like to delete the files.
CREATE TABLE [dbo].[PurgeInformation] (
[ID] [INT] IDENTITY(1, 1) NOT NULL
,[FolderPath] [VARCHAR](300) NULL
,[FileType] [VARCHAR](20) NULL
,[RetentionPeriod] [INT] NULL
,[FileNameLike] [VARCHAR](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[PurgeInformation] ON
GO
INSERT [dbo].[PurgeInformation] (
[ID]
,[FolderPath]
,[FileType]
,[RetentionPeriod]
,[FileNameLike]
)
VALUES (
1
,N'C:\SSISTraining\HDW'
,N'.txt'
,5
,N'Log'
)
GO
INSERT [dbo].[PurgeInformation] (
[ID]
,[FolderPath]
,[FileType]
,[RetentionPeriod]
,[FileNameLike]
)
VALUES (
2
,N'C:\SSISTraining\SourceFiles'
,N'.xls'
,4
,N'Pkg_Log'
)
GO
SET IDENTITY_INSERT [dbo].[PurgeInformation] OFF
GO
Step 2:
Create an SSIS Package by using BIDS or SSDT and Create a variable VarObject of type object.
Use Execute SQL Task to read the values from dbo.PurgeInformation table and load into VarObject variable as shown below.
Query :
SELECT Folderpath
,FileType
,RetentionPeriod
,FileNameLike
FROM dbo.PurgeInformation
Read records from SQL Server Table and Save to Object Type variable in SSIS to Purge old Files |
Save query results to Object type variable in SSIS Package for Delete old files from folders |
Step 4:
Bring For-each loop container on Control Flow Pane. We need to use Object type variable in For-each Loop so can read one record on each iteration.
How to use Object type variable in Foreach Loop Container to read all the records one by one in SSIS Package |
Map the row columns to variables in For-each Loop Container in SSIS Package |
Step 5:
After configuring For-each Loop, Drag Script task inside For-each Loop Container and add all user variables we have created above.
How to add variables to Script Task in SSIS Package to delete old files from folders |
Step 6:
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO;
Under public void Main() {I have added below code.string FileDirectory, FileNamePattern, FileType; int RetentionPeriod; FileDirectory=Dts.Variables["User::VarFolderPath"].Value.ToString(); FileType=Dts.Variables["User::VarFileType"].Value.ToString(); FileNamePattern = Dts.Variables["User::VarFileNameLike"].Value.ToString(); RetentionPeriod =Convert.ToInt32(Dts.Variables["User::VarRetentionPeriod"].Value.ToString()); var files = new DirectoryInfo(FileDirectory).GetFiles(FileNamePattern + "*" + FileType); // MessageBox.Show("OUT of Loop"); foreach (var file in files) { // MessageBox.Show(" I AM IN LOOP"); if (file.CreationTime < DateTime.Today.AddDays(-RetentionPeriod)) { MessageBox.Show(file.Name.ToString()); file.Delete(); } }
SSIS Package to delete old files from different folders |
Your SSIS Package is ready, You can schedule your package by SQL Server Agent nightly or can run on demand to delete files from different folder with different retention period.
You can add new folder path in table to delete required files.
It looks like the rest of your C# code got chopped off. Any chance you can re-post?
ReplyDeleteThanks.
Hi Larry,Never got chance to fix the chopped code, Sorry for that. I have fixed the code. Hope this will help.. Thanks
ReplyDeleteHi Aamir, i am looking for the solution to delete the file which is already locked by another process in SSIS. Could you please make a video for the same with best approach for this, may be unlock the same file and then try to delete.
ReplyDeleteThank you so much for providing such genuine information about SSIS and its utilities.
ReplyDeleteSSIS PostgreSql Read
Liên hệ Aivivu, đặt vé máy bay tham khảo
ReplyDeletevé máy bay đi Mỹ bao nhiêu tiền
áy bay đà nẵng hà nội
đặt vé máy bay thanh hóa sài gòn
vé máy bay giá rẻ đi nha trang
đăng ký về việt nam ở mỹ
bảng giá taxi sân bay nội bài
combo quy nhơn tháng 7/2021