SSIS: Process Files in File Date Order

It would be nice if the SSIS For Each Loop Enumerator could provide the developer the flexibility to process files in order of filename, file create date or perhaps file modified date. This article really helped me out recently…

http://microsoft-ssis.blogspot.com.au/2011/04/how-to-configure-foreach-loop-container.html.

Keep it simple – usually means keeping clear of toxic script tasks or script components, however on this occasion, the script task is a must.

Use the code in the article above, and follow the instructions.   Make sure you setup the variables in the package, dataset (object), filepath (string) and startlocation (string).   The file path is the folder path of the folder to traverse without the \ character at the end.

To use modified date change this line …

filelistTable.Rows.Add(fileInfo.FullName, fileInfo.Name, fileInfo.CreationTime)

to this …

filelistTable.Rows.Add(fileInfo.FullName, fileInfo.Name, fileInfo.LastWriteTime)

To adjust the filter to gather only certain filename patterns, edit this part, for example, look for files with the txt extension in ascending date order oldest to newest …

"FileName like '*.txt'","FileDate ASC"

This example looks for files starting with the letter A, with an extension that starts with the letter T, then builds a list of files with the files ordered newest to oldest …

"FileName like 'A*.T*'","FileDate DESC"


About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s