asked
Import multiple Excel Files in SSIS
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!
Have you set property DelayValidation = TRUE for the Data Flow task? Try that and let us know.
DOH!!! Yup that seems to have been the issue. UGH!! I thought I had set that value already. I'm thinking maybe when I built this package from scratch that maybe I had over looked that setting or didnt save it. *embarassed* Thank you all so much for your input! I'm in a team of 1 so I depend on forums to bounce ideas off of.
We are happy to help you anytime :) And consider this large wonderful community as your team. Cheers.
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.
@Pavel Pawlowski +1. This kind of same thing is happening in our environment but for the flat files and is working fine. I think same is the case with yours, as you mentioned "flat file connection manager" in the last point :) I guess that needs to be changed.
