SSIS Process files that has been downloaded in the last hour

A recent question on SQL Server Central was asking about how to process the last hour downloaded files.

I think it's fairly simple using a script task to get the files creation date property, and then added a constant constraint to check the difference between now and the creation date, then pass through to the data flow task to process the file.

I've created 6 variables, I'm here getting more usable file properties, but you don't have to do that, I'm just here showing the concept more than doing the exact time difference.

Add a "foreachloop Container" and a "script task" inside.

edit the "Foreachloop Container" location to your file system location. (the one that has your files).

Map the 'FilePath' variable to the output of the "foreachloop"

Edit the "Script Task" ,I'm using VB.net language here.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO.File
Imports System.IO.FileInfo
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
 Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
 Enum ScriptResults
  Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
  Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        'opening the Variables for write and read for some of them
        Dim Vars As Variables = Nothing  'var dispenser
        Dts.VariableDispenser.LockForRead("User::FilePath")
        Dts.VariableDispenser.LockForWrite("User::FileSize")
        Dts.VariableDispenser.LockForWrite("User::FileCreationDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastAccessedDate")
        Dts.VariableDispenser.LockForWrite("User::FileLastModifiedDate")
        Dts.VariableDispenser.LockForWrite("User::FileAge")
        Dts.VariableDispenser.GetVariables(Vars)
        Dim f As IO.FileInfo = New System.IO.FileInfo(CStr(Vars("User::FilePath").Value.ToString))
        Dim fileSize As Double = f.Length
        Dim fileDate As Date = f.CreationTime
        Dim fileLastAccess As Date = f.LastAccessTime
        Dim fileLastMod As Date = f.LastWriteTime
        Vars("FileSize").Value = fileSize / 1024   'get file size
        Vars("FileCreationDate").Value = fileDate
        Vars("FileLastAccessedDate").Value = fileLastAccess
        Vars("FileLastModifiedDate").Value = fileLastMod
        Vars("FileAge").Value = CInt(DateDiff(DateInterval.Minute, fileDate, Now()))
        'only for testing to visualize the results, comment out
        MsgBox("File Name: " & f.FullName & vbCrLf & _
                "File Size: " & Vars("FileSize").Value.ToString & vbCrLf & _
                "File Date: " & Vars("FileCreationDate").Value.ToString & vbCrLf & _
                "File Age: " & Vars("FileAge").Value.ToString)

        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

That's it....

Test it and you'll get results like that..

Now use a  precedence constraint to check the file's age and process the less than 60 minutes.

The package could be downloaded from: http://www.box.net/shared/dgyb5z79bgdgdp9pkpi3

Hope that helps someone...