Solved

SSIS To import all worksheets from sub directories

Posted on 2014-09-05
2
145 Views
Last Modified: 2016-02-11
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
0
Comment
Question by:SQLSearcher
2 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 500 total points
ID: 40307102
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
 

Author Comment

by:SQLSearcher
ID: 40319288
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now