Select a specific Excel file to import into SQL table in SSIS, HOW?!!!
Posted on 2011-10-21
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. To make things more complicated, we cannot used "Date modified" becasue the file is swept up by a an auto-sync program which makes all of the dates the same because it all gets downloaded and synced at the same time.
Also, the data has been placed on its own individual cell in the excel spreadsheet.
So the logic would be grabbing the file with the greatest number in the parenthesis and then pulling the data off the spreadsheet from specific cells.
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. I'm using SQL command and doing a select * from [SHEET1$A2:A2] as name, [SHEET1$D3:D3] as Address, [SHEET1$F5:F5] as Invoice
(Yes the data I need are all on separate lines).
Does anyone have any idea how to accomplish this? Thanks so much in advance!