Solved

SSIS To import all worksheets from sub directories

Posted on 2014-09-05
2
137 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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

15 Experts available now in Live!

Get 1:1 Help Now