Using SSIS Expression Builder to Retrieve File with the oldest datetime?

Question

I have a Holding directory with files in it that are downloaded from FTP throughout the day.

At any time I need to be able to sort the collection and retrieve the file whose CreationTime attribute is the oldest. This file will then be moved from "Holding" to "Pending".

Since I am using an SSIS package to move the file using a System File Task, I need to manipulate an expression based on the CreationDate variable. Trouble is, I am really unfamiliar with this "expression" language you are faced with using in the Expression Builder.

So, how would I retrieve a file whose creation date is the oldest using an expression script?

Or is there an easier way to do what I want to do with a Script Task?

Dupe/check out my answer on http://stackoverflow.com/questions/8831060/import-most-recent-csv-file-to-sql-server-in-ssis — billinkc, Feb 29 '12 at 21:07

score 1 · Accepted Answer · answered Feb 29 '12 at 21:31

1

you will have to loop trough the files on a script task and inside this script check the last created, load its name to a global variable and then use it on your System File Task

this may help you: http://www.simple-talk.com/sql/ssis/adding-the-script-task-to-your-ssis-packages/

answered Feb 29 '12 at 21:31

Diego

34,802
21
91
134

Would it be at all possible to load a dbml into the script task solution and use linq to sort the collection? – Isaiah Nelson Feb 29 '12 at 21:38
apparently you can (had to google it) but I never did it. If you can accomplish it, it would remove the necessity for the loop. great Idea. Let me know if you were able to – Diego Feb 29 '12 at 21:46
Good Deal. I appreciate the help, Diego. – Isaiah Nelson Feb 29 '12 at 21:49

Using SSIS Expression Builder to Retrieve File with the oldest datetime?

1 Answers1