SSIS To import all worksheets from sub directories

Hello Experts Exchange
I have a folder with several sub folders that have Excel spreadsheet, most of the spreadsheets have a work sheet called Import.

I would like SSIS to check each sub folder for a Excel spreadsheet, then check for a worksheet called Import, and if it exists import the data to a table.

Is this possible?

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?
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.

DcpKingCommented:
1. Create a DataFlow task in the ControlFlow page to hold the import of the data and check out that the import works.
2. Create a variable (type String) at the outermost level - package level
3. Create a ForEach block in the ControlFlow page.
4. On the Collection page set the Enumerator to a ForEach File Enumerator, the Folder to whatever your base folder is, and the "Files:" setting to "Import.xlsx". That will restrict the code to just that file in the folder.
5. Then check the "Traverse Subfolders" box to do the base folder and all below it.
6. Check also the button marked "Fully Qualified" for the filename, 'cos you'll want to distinguish just which file you're getting data from.
7. Go to the Variable Mappings page and choose the variable you made in #2. Set the mapping for this to zero.
8. That will apply your dataflow task to every file as you asked, but it won't know it yet!
9. In the dataflow task go to the excel connection that you've made and look for the Expressions property. In there find the expression that defines the Excel path and set that not to the path but to the name of the variable from #2.
10. For that same connector look for the Delay Validation property and set it true.

Now every time the dataflow runs it'll run connected to the excel file found by the foreach loop.

hth

Mike
0

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
SQLSearcherAuthor Commented:
Hello Mike
Would it be possible to make me a example SSIS project file that I can view, I'm new to SSIS and don't understand some of the steps you have given me.

Regards

SQLSearcher
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.