Hi Everyone! I've built an SSIS package which loads multiple Excel files into a specified table. The Excel files will always have Sheet1 but the name can differ. I have a ForEachLoop container configured to a specified folder path along with *.xls*. I also have a FileName variable configured and the Excel Connection Manager modified to use the variable for connection. This package works but I'm wondering how I can modify this package to load any Excel file from within a specified directory for data load. I have Test1.xlsx as a filename but the package loops through and pulls AnotherFile.xlsx and etc. But once the files are loaded with no errors if I move the loaded files to Archive folder and add another set of xls files not named like the ones mentioned the package fails because it is looking for Test1.xlsx (the starting configured file). I want the package to be looking for any Excel file. Does anyone have a link to help me out or can anyone explain to me how I can create the SSIS package to look for any Excel file in a specified folder not based on a name of the file? Greatly appreciated!
1. Simply configure the Foreach Loop container as Foreach File Enumerator. 2. In the Enumerator COnfiguration define the folder in which you want to lookup the xls files. (if the folder should be configured, use SSIS Configuration or in case you have the folder in variable, then specify an Expression for the Folder property to assign the variable value. 3. In the Files property of Enumerator configuration specify `*.xlsx`. This will ensure, that Foreach Loop container will iterate through all the excel files in that particular directory. 4. In the Retrive File Name specify **Fully qualified** 5. In the Variable mappings, map your variable which is then used by the flat file connection manager. That's all and the solution should work properly.