April 29, 2011 at 5:45 am
I have a folder in d drive called Archive (d:\archive) which stores all data load files. The name of these files various, and I need to create a ssis job to delete all these archived load files older than 7 day.
How I can do that?
Thanks,
Q
April 29, 2011 at 5:58 am
My personnal favorite is to put the file date in the filename. IE : ETL_ABC_20110429.
Then simply use xp_cmdshell to load the file names. Parse the date. Start a cursor with the list of files to delete and execute.
There are other options but they have had bugs over the years and this has always worked for me so I stuck with it.
April 29, 2011 at 6:52 am
Thanks.
These are load files of various sort and do not come with date extension.
Q
April 29, 2011 at 6:56 am
Can you ask to have those file names sent with timestamp? It's really not that much work. Also it makes it easier to understand what's in the file.
April 29, 2011 at 9:00 am
QQ-485619 (4/29/2011)
I have a folder in d drive called Archive (d:\archive) which stores all data load files. The name of these files various, and I need to create a ssis job to delete all these archived load files older than 7 day.How I can do that?
Thanks,
Q
Here is a powershell script I have scheduled daily that removes old files:
$Now = Get-Date
$Days = “10”
$TargetFolder = “C:\Database_Backups”
$LastWrite = $Now.AddDays(-$days)
$Files = get-childitem $TargetFolder -include *.zip -recurse | Where {$_.LastWriteTime -le “$LastWrite”}
foreach ($File in $Files)
{if (!($File -eq $null)) {remove-item $File | out-null} }
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 29, 2011 at 11:34 am
You can use a script task in SSIS and use something like the following:
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim SourcePath As String
Dim PurgeDays As Integer
PurgeDays = CInt(Dts.Variables("User::PurgeDays").Value)
SourcePath = CStr(Dts.Variables("User::SourcePath").Value)
For Each dir As DirectoryInfo In New DirectoryInfo(SourcePath).GetDirectories()
For Each file As FileInfo In dir.GetFiles()
If (Now - file.LastWriteTime).Days > PurgeDays Then file.Delete()
Next
Next
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
In the above, I have 2 variables defined. The PurgeDays variable contains the number of days to keep files and the SourcePath variable contains the directory where the files are.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2011 at 12:24 pm
You could use a maintenance plan if you know the file extensions... Is that cheating ?
April 29, 2011 at 12:31 pm
The script task is more of something that I am looking for. However, I got an error when I am testing it:
Error 30002: Type 'DirectoryInfo' is not defined.
Line 23 Column 25 through 37
Can you guide me to fix it?
Thanks
April 29, 2011 at 12:43 pm
QQ-485619 (4/29/2011)
I have a folder in d drive called Archive (d:\archive) which stores all data load files. The name of these files various, and I need to create a ssis job to delete all these archived load files older than 7 day.How I can do that?
Thanks,
Q
If you must do this purely with SSIS then a Script Task that loops over the directory, checks the file info of each file and conditionally deletes it is one option. You could offload the file enumeration to a ForEach Loop container and only use a Script Task to get the file info and conditionally do the delete.
If you are not restricted to using SSIS then I would second what toddasd said...use PowerShell! It's much simpler than what I just described above on how to do the task with SSIS, does not require enabling xp_CmdShell and can be scheduled and executed in any number of contexts.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 29, 2011 at 2:54 pm
QQ-485619 (4/29/2011)
The script task is more of something that I am looking for. However, I got an error when I am testing it:Error 30002: Type 'DirectoryInfo' is not defined.
Line 23 Column 25 through 37
Can you guide me to fix it?
Thanks
Not sure - what did you change with the script I provided? That script came from a working purge task I have in an SSIS package. My guess is that it is failing because your SourcePath information is not valid.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 29, 2011 at 4:56 pm
Did you declare the property for "DirectoryInfo" somewhere in your package?
Thanks,
May 2, 2011 at 11:36 am
Hi,
When testing out this code I keep getting an error underneath all the "Dts." with the error saying "reference to a non-shared object requires an object reference", is there something that needs to be added to this code to remove this error? Thanks in advance.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.IO
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Tasks
Imports Microsoft.SqlServer.Dts.Runtime
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Class ScriptMain
Public Sub Main()
Dim SourcePath As String
Dim PurgeDays As Integer
PurgeDays = CInt(Dts.Variables("User::PurgeDays").Value)
SourcePath = CStr(Dts.Variables("User::SourcePath").Value)
For Each dir As DirectoryInfo In New DirectoryInfo(SourcePath).GetDirectories()
For Each file As FileInfo In dir.GetFiles()
If (Now - file.LastWriteTime).Days > PurgeDays Then file.Delete()
Next
Next
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
October 6, 2016 at 2:38 pm
Using strictly ssis tasks, for each file enumerator, script task to get file info,
conditionally delete file, done.
Works great if the directory is not empty, barfs a warning, then fails after 30 messages.
I'd like to not get the warnings, how to ?
October 6, 2016 at 8:33 pm
rsampson (10/6/2016)
Using strictly ssis tasks, for each file enumerator, script task to get file info,conditionally delete file, done.
Works great if the directory is not empty, barfs a warning, then fails after 30 messages.
I'd like to not get the warnings, how to ?
I realize this thread is old but the post above necro'd it. 🙂
If you're trying to delete all of a particular file type (or all files) prior to a certain date, why not avoid having to write loops, write your own conditional tests, and etc, etc? Call a command task that uses FORFILES. You can use a single line of code to delete all files with a certain extension in the "current" directory and all sub-directories.
Here's an example of listing the "Modified Date" and the "FULL PATH" of all files with a mask of "*.txt" the live in the "C:\Temp" directory and all of its sub-directories (/S is for recursion of all sub directories) that have a "Modified Date" date that occurred prior to 01/01/2016 (mm/dd/yyyy - no other format is possible).
FORFILES /P "C:\Temp" /M *.txt /S /D -01/01/2016 /C "CMD /C ECHO @FDate @Path"
Change everything to the right of "CMD /C" to the delete command that you want (probably DEL @Path) and you're done.
For more information, please see the following two links...
[font="Arial Black"]FORFILES command[/font]
[font="Arial Black"]DEL command[/font]
Personally, I avoid SSIS and PowerShell for things like this. I hate writing loops. Let the machine write its own pseudo-cursors for you, just like in T-SQL set based code.
As a bit of a sidebar, file deletions are mostly permanent and you can make some terrible mistakes. My recommendation would be to have one command to move the files to an "intended delete" area (RoboCopy works well as a separate command not using FORFILES) and only delete from that area after a given period of time. That will give people the opportunity to miss their files and bug you to put them back, which you'll easily be able to do because you will have them.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 1, 2016 at 10:42 pm
else,
i used this syntax
DECLARE @CMD sysname
--create .bat:
/*.bat:
@echo off
forfiles /p "d:\backup\bck_denyc\combine" /m *.bak /c "cmd /c del /Q @file" /d -30 --to delete 30 days back
@echo off
*/
--execute the .bat:
exec @cmd = xp_CMDShell 'd:\backup\bck_Denyc\"delete folder".bat'
If (@CMD = 1)
PRINT 'SUCCESS'
ELSE
PRINT 'FAILURE'
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply