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
Solved

SSIS To import all worksheets from sub directories

Posted on 2014-09-05
2
158 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

791 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