Foreach Loop in SSIS not processing files correctly

I have a Foreach Loop Container and I'm using a variable to pick-up and process all xlsx files in a directory. It only works if I specify the full path and exact file name in the value of the variable.

I am using an excel connection manager in the package.

Is there a trick to making it pick up any xlsx files? I have the correct directory and filespec in the Foreach Loop properties, but again, it will only run the file I specifically point to in the Value of the expression.

Any help you can offer would be greatly appreciated, thank you.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Show us a screen shot of both the Control Flow, and the properties of the ForEach File Loop.
earwig75Author Commented:
Unfortunately, I can't.
earwig75Author Commented:
I should mention, it will pick up other files in that directory with different names, but at least one has to have the name that is specified in the variable.
Alpesh PatelAssistant ConsultantCommented:
Make your excel connection string by expression.

1. You have folder path in variable so you are iterating that folder.
2. Get each file name and take in variable.
3. Merge both variable to get full file path.
4. Use that value to excel connectionstring expression.
this is because your connection manager for your input file is set to a single file.

1. you need to create a new variable, in foreachloop editor click on variable mapping
2. create a new user variable, set the value type to string. (Lets say the variable is named "excel_input_file")
now every time for each loop finds a new file it will put it in this variable at each run
3. now right click on your excel connection manager and select properties
4. in expression select the three doted button to browse
5. create a connection string property  and set the expression to: @[use::excel_input_file]

i am assuming you have set the collection parameters of your for each loop container already if not:

1. select collection from the left hand side menu
2. set the folder location where your excel files are
3. set the files to : *.xlsx
this is your excel file extensions older excel file extensions might be xls, if thats the case make sure you use the correct one here.
4.  select fully qualified

and done.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.