December 7, 2007 at 2:31 am
Hi, I have a folder which contains 100 three months old files. Question is: How can I in the "Foreach loop editor" in SSIS choose just files older then one week. I will then in the "Foreach loop editor" use "File System Task", "Delete file" operation to delete its files. I want to change "xp_delete_file" which I'm using now, script for this is:
DECLARE @OneWeekAgo VARCHAR(50)
SELECT @OneWeekAgo = CAST(DATEADD(d, -7, GETDATE()) AS VARCHAR)
EXECUTE master.dbo.xp_delete_file 1,?,N'txt',@OneWeekAgo
I can not find syntax explanation for xp_delete_file, don't know what first
parameter (1) mean. Do someone have xp_delete_file syntax?
Best Regards
/Semko
December 11, 2007 at 9:27 am
You could use a script transformation without a ForEach Loop container - i.e.
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports System.IO
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
Public Sub Main()
Dim strFile As String, strFilePath As String
strFilePath = Dts.Variables("varFileLocation").Value 'Change This
Dim strFileNames = Directory.GetFiles(strFilePath)
Try
For Each strFile In strFileNames
Dim FileInfo As New System.IO.FileInfo(CStr(strFile))
'For Debug
'System.Windows.Forms.MessageBox.Show(FileInfo.CreationTime)
'System.Windows.Forms.MessageBox.Show(FileInfo.CreationTime < DateTime.Now.AddDays(-3))
If (FileInfo.CreationTime <= DateTime.Now.AddDays(-7)) = True Then
File.Delete(strFile)
End If
Next
Catch ex As Exception
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Tommy
Follow @sqlscribeDecember 12, 2007 at 7:34 am
..thanks a lot..
/Semko
December 12, 2007 at 1:02 pm
check this post:
http://qa.sqlservercentral.com/Forums/Topic359308-338-1.aspx#bm361850
Maintenance plan has a cleanup task to do such things. There is a bug. It has a fix now. http://support.microsoft.com/kb/933508
March 5, 2008 at 11:05 am
Thank you Tommy Bollhofer
I needed something like this for a non-maintenance plan ETL package to clean up archived files.
Still wish we were able to use C# in SSIS.
Skål - jh
March 5, 2008 at 12:12 pm
April 2, 2008 at 6:01 am
Still wish we were able to use C# in SSIS.
I read that SSIS 2K8 will have C#.Net.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply