• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

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

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
MohitPandit
Asked:
MohitPandit
  • 3
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<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
 
MohitPanditAuthor Commented:
I've resolved it. I'll share the steps.
0
 
MohitPanditAuthor Commented:
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
 
MohitPanditAuthor Commented:
It is done myself.
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now