SSIS: Loop through files that are within a date range (if date is part of the file name)

For those lucky ones that have to process file system files based on a date range, AND the date is part of the file name; here is one possible solution:

This example assumes that the date is in the last 8 characters of the file name in a format mmddyyyy.

Use a ForEach Loop container to iterate through all files in the folder hosting the source files. The file name would be stored in a SSIS variable (FileName). Then using an additional SSIS variable (FileDate) with an expression, parse the file name and get the date portion to be compared with the StartDate/EndDate range. Then based on that result, decide if the file has to be processed or ignoreded. For that comparison I used a precedence constraint with an expression.

Here are a few screen shots of the package I built:

Control Flow


















Variables:









ForEach Loop Conatiner:


The Precedence constraint:
























This is the expression in Filedate variable to get the date portion of the file name:

(DT_DATE) (Substring(Right(@[User::FileName], 8),1,2) +"/" + Substring(Right(@[User::FileName], 8),3,2) +"/" + Substring(Right(@[User::FileName], 8),5,4))


The Expression in the precedence constraint:

@[User::FileDate] >= @[User::StartDate] && @[User::FileDate] <= @[User::EndDate]