?
Solved

SSIS To import all worksheets from sub directories

Posted on 2014-09-05
2
Medium Priority
?
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
DcpKing earned 2000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 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