Solved

Import multiple worksheets (tabs) from one excel OR multiple excel files using SSIS

Posted on 2014-10-15
4
143 Views
Last Modified: 2016-02-11
Dear Folks,

In SSIS, in case I've an excel/multiple excel files and I want to import all worksheets OR tabs from each excel file in SQL Server.
Do you have any idea best way to do that?

Best Regards,
Mohit Pandit
0
Comment
Question by:MohitPandit
  • 3
4 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40384461
<Wild guess based on a fuzzy memory of doing this in a past project>
Eyeball the Excel connection to see if you can name a named range, and if yes enter a tab name and run, and see if SSIS will process the data in that tab.

If successful, you should be able to copy-paste that connection to create more, then edit the named range to reflect that tab whose data you're trying to import.
0
 
LVL 5

Author Comment

by:MohitPandit
ID: 40408407
I've resolved it. I'll share the steps.
0
 
LVL 5

Accepted Solution

by:
MohitPandit earned 0 total points
ID: 40582285
Please find below steps through which I've resolved it.

1. First, For each Loop (Enumerator: Foreach File Enumerator)
      -- Folder path (in Collection)
      -- file extension .xlsx (in Collection)
      -- Store in variable in variable mapping
2. Second, Next another For each loop editor (with in First point)
      -- (Enumerator: Foreach ADO.NET Schema Rowset Enumerator)
      -- Store in variable (sheet name) in Variable mapping
3. Third, Data Flow Task (with in second point)
      -- Source as excel
            -- Select variable name (i.e. sheet name) in "OpenRowSetVariable" property

Best Regards,
Mohit Pandit
0
 
LVL 5

Author Closing Comment

by:MohitPandit
ID: 40593207
It is done myself.
Thanks
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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…
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
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

777 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