How to delete SQL rows based on source file last modified date, using SSIS package?

I have created a functioning SSIS package which pulls rows from a flat file into a SQL table. I just need to be able to delete old rows in the table, once they are older than 10 days.

The only thing is, there is no date column and I'm wondering if there is a way to do this, using the DateLastModified property from the source file? I'm not sure if this can be done via a script task or something else?

Your advice would be appreciated. :-)

So I've tried to include the date of the source file by creating a FileDate variable, along with FilePath and SourceFolder variables. I've utilized the FileDate variable by adding a derived column, Date_Imported w/the expression, @[User::FileDate]. The FilePath variable is assigned the location, "d:\inputfiles*.txt", as indicated in the below code. The SourceFolder has been given the value, "D:\InputFiles\". However, I'm receiving an "Exception has been thrown by the target of an invocation.

System.MissingMemberException: Public member 'GetFiles' on type 'FileSystemObject' not found."

The following is the content of my script task to delete records older than 10 days; please disregard any commented out lines, as I've been trying different things...I appreciate any guidance you can give:

Public Sub Main()
    ' Add your code here

    Dim FilePath As String

    'Dim SourceFolder As String
    Dim iMaxAge = 10
    Dim oFSO = CreateObject("Scripting.FileSystemObject")

    Dim myConnection As SqlConnection
    Dim myCommand As SqlCommand

    myConnection = New SqlConnection("server = localhost; uid=sa; pwd=; database=StampsProj")

    FilePath = "d:\inputfiles\*.txt"
    'SourceFolder = "d:\inputfiles"
    'SourceFolder.ReadOnly = True

    'To delete records, older than 10 days from AddUpIn table
    'For Each oFile In oFSO.GetFolder(SourceFolder).Files
    For Each oFile In oFSO.GetFiles(Dts.Variables("User::SourceFolder"))
        Dim FileDate As Date = oFile.DateLastModified
        If DateDiff("d", oFile.DateLastModified, Now) > iMaxAge 开发者_如何学PythonThen
            'If DateDiff("d", oFile.FileDate, Now) > iMaxAge Then
            myCommand = New SqlCommand("Delete from AddUpIn", myConnection)
        End If
    Next

End Sub

Sounds like you need to add either a datetime column onto your import table and set its value to the date you run the import. Or create a seperate FileImport table which logs the filename and an identifier, then add the identifier to the import table so you can identify the rows to delete.