finding latest excel file from folder using ssis

Question

I have group of excel files in a folder. excel file name will be like

ABC 2014-09-13.xlsx
ABC 2014-09-14.xlsx
ABC 2014-09-15.xlsx

I need to get the data from latest excel file and load it into the table using ssis package.

Answer 1

This may not be the shortest answer, but will help you.

Steps:

1. Create a For-each loop, to fetch all the excel sheets. Insert all the excel sheet names to a table.
2. Create a variable. Assign its value as the MAX() among Excel dates.
3. Add a 2nd Fore-each loop. Just like the 1st loop, pick all the excel sheets 1 by 1, compare each file name with Variable value. Load the excel which matches it.

Answer 2

As this is duplicate question, I will put answer anyway with some changes or additional info.

1. You should have created table for excel to import and added Connection Manager into package.
2. Create 2 variables MainDir, where excel files exists, and ExcelFile to hold last file full name.
3. Add Script Task to package. Open it and in the Script tab add ReadOnlyVariables = User::MainDir and ReadWriteVariables = User::ExcelFile
4. Press Edit Script... button and in the new window paste this code:

into Main

 string fileMask = "*.xlsx";
 string mostRecentFile = string.Empty;
 string rootFolder = string.Empty;

 rootFolder = Dts.Variables["User::MainDir"].Value.ToString();     
 System.IO.DirectoryInfo directoryInfo = new System.IO.DirectoryInfo(rootFolder);     
 System.IO.FileInfo mostRecent = null;

 System.IO.FileInfo[] legacyArray = directoryInfo.GetFiles(fileMask, System.IO.SearchOption.TopDirectoryOnly);    
 Array.Sort(legacyArray, (f2, f1) => f2.Name.CompareTo(f1.Name));

 mostRecent = legacyArray[legacyArray.Length - 1];

 if (mostRecent != null)
 {
      mostRecentFile = mostRecent.FullName;
 }

 Dts.Variables["User::ExcelFile"].Value = mostRecentFile;
 Dts.TaskResult = (int)ScriptResults.Success;`

5. Create Excel Connection Manager and in the Edit mode select Excel file path to some excel, Excel version and if needed keep First row has column names checked.
6. In the properties of Excel Connection Manager find Expressions and add Property ExcelFilePath with value @[User::ExcelFile]
7. Put Data Flow Task, connect with Script task.
8. Add Excel Source into Data Flow Task. Open editor. Select Excel Connection Manager you created before, Data access mode change to SQL command and add this line (make sure, that excel file sheet name is Sheet1): SELECT * FROM [Sheet1$]. Also check if all necessary columns selected in Columns tab.
9. The last component is OLE DB Destination, which you must connect with Excel Source component. Add connection manager, select table and mappings to table you want to insert.

That's all you need to do to insert excel...