SSIS - Import data from excel using the most recent file in the folder
Posted on 2011-10-20
What I'm trying to do is insert data into a SQL table from an excel spreadsheet. The challenge is being able to grab the most recent excel spreadsheet as the data source.
The file has a naming convention similar to:
File(1).xls, File(2).xls, File(3), File(4) etc.,
Can someone tell me how I can grab the latest file? The issue is the person who is placing the file in the directory only increments the file each day, so if today was Monday, the file will be named File(1).xls, and Tuesday would be File(2).xls.
So the logic would be grabbing the file with the greatest number in the parenthesis.
Right now, the data is being inserted correctly if I preselect an excel spreadsheet, however, not pulling the most recent file in the folder.
The data is being inserted using Excel Source --> OLE DB Destination.
Does anyone have any idea how to accomplish this?