Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

In SSIS, how can I loop through all the files in a folder?

Posted on 2011-05-08
9
Medium Priority
?
305 Views
Last Modified: 2012-06-22
I have a folder with lots of Excel files.  I want to loop through all the files in that folder to extract data in those Excel files.  For each of those Excel files, I'm going to apply a SQL to get the dataset I want.  How should I do this?  Thanks.
0
Comment
Question by:thomaszhwang
9 Comments
 
LVL 9

Accepted Solution

by:
kaminda earned 400 total points
ID: 35717647
Hi,

This has a step by step explaination of how you can do this

http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 400 total points
ID: 35717682
Do as see
Untitled.png
0
 

Author Comment

by:thomaszhwang
ID: 35717855
Thanks guys.  Both posts help to solve the loop issue, but the thing is in the Foreach Loop Container, there is a Data Flow Task and in that Data Flow Task, there is supposed to be an Excel Source.  The problem is how can I set the property for that Excel Source?

I have already set up an Excel Connection Manager to use Expression to pick up the file path for each Excel file, but the Excel Source apparently cannot pick up the schema of any Excel file.  Therefore its property page cannot be setup as shown in the attached screen shot.

Please help.  Thanks. Failed to Setup the Property Page
0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 1200 total points
ID: 35717872
0
 

Author Comment

by:thomaszhwang
ID: 35717890
Thanks partima_mcs, but the post doesn't solve my issue with the setting of the Excel Source component.
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 1200 total points
ID: 35717930
0
 

Author Comment

by:thomaszhwang
ID: 35717949
Thanks pratima_mcs.  I have done what the post says, but it didn't elaborate on how to set the Excel Source.  I have successfully setup the Excel Connection Manager using the variable, but I have problem setting up the Excel Source component.  I have posted error screen shot in my previous post.  Please help, thanks!
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 1200 total points
ID: 35717979
0
 

Author Closing Comment

by:thomaszhwang
ID: 35722885
Thanks guys.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question