<div>
Hi, 
 
pls refer to 
 
<a href="http://www.mssqltips.com/sqlservertip/1674/retrieve-excel-schema-using-sql-integration-services-ssis/" rel="ugc">http://www.mssqltips.com/sqlservertip/1674/retrieve-excel-schema-using-sql-integration-services-ssis/</a> 
 
Regards
</div>

<div>
Assuming all sheets have the same structure (columns), loop through the sheets with VBA to combine them into a single &quot;master&quot; worksheet and use the OPENROWSET statement once. &nbsp;The code below creates a new worksheet in position 1 named &quot;Master&quot;. &nbsp;That name can be changed to whatever you want in the Initialize Variables section of the code. 

<pre><code class="code-100 nolinks" id="code-20-39757365-1">Sub Combine_Worksheets()
 Dim i As Integer, j As Integer
 Dim lstRow1 As Integer, lstRow2 As Integer, lstCol As Integer
 Dim ws1 As Worksheet, ws As Worksheet
 Dim wsMstr As String
 Dim LstCell As String
 Dim EMsg As String
 Dim DisplayMsg As String
 Dim ColCnt As Integer
 Dim CntFlg As Integer
 Dim Response As String
 Dim errorMsg As String

 On Error GoTo errHandler
 errFlag = False

 With Application
 .DisplayAlerts = False
 .ScreenUpdating = False
 .EnableEvents = False
 .StatusBar = False
 End With

 'Intitialize Variables
 wsMstr = &quot;Master&quot; 'Master Worksheet Name - rename as needed
 EMsg = &quot;&quot;
 CntFlg = 0
 Application.StatusBar = &quot;Creating/Updating the &quot; &amp; wsMstr &amp; &quot; Worksheet with Current Data&quot;

 Sheets(wsMstr).Activate
 With Range(&quot;A1&quot;, ActiveCell.SpecialCells(xlLastCell))
 .Clear
 .EntireRow.Delete
 End With

comeBack:
 Set ws1 = Sheets(wsMstr)
 lstRow2 = 1
 j = 1

 'Check the Number of Columns in each data worksheet
 'Set CntFlg to 1 if any sheet has a different number of columns
 ColCnt = Sheets(2).Cells(1, 16384).End(xlToLeft).Column
 For i = 2 To Sheets.Count
 If Right(Sheets(i).Name, 1) &lt;&gt; &quot;+&quot; Then
 Worksheets(i).Activate
 EMsg = EMsg &amp; ActiveSheet.Cells(1, 16384).End(xlToLeft).Column &amp; &quot; Columns - &quot; &amp; Sheets(i).Name &amp; Chr(10)
 If ColCnt &lt;&gt; ActiveSheet.Cells(1, 16384).End(xlToLeft).Column Then errFlag = True
 End If
 Next

 'IF CntFlag was set above, Display the Error Dialog and exit the routine
 If errFlag = True Then
 DisplayMsg = &quot;The Component Sheets Do Not Have the Same Number of Columns&quot; &amp; Chr(10) &amp; Chr(10)
 DisplayMsg = DisplayMsg &amp; EMsg &amp; Chr(10)
 DisplayMsg = DisplayMsg &amp; &quot;Correct the Sheets Before Continuing&quot; &amp; Chr(10) &amp; &quot;Routine is Exiting&quot;
 Response = MsgBox(DisplayMsg, vbCritical, &quot;wsMstr Column Count Error&quot;)
 GoTo finished
 End If

 'Combine the sheets
 For i = 2 To Sheets.Count
 If Right(Sheets(i).Name, 1) &lt;&gt; &quot;+&quot; Then
 Worksheets(i).Activate
 lstCol = ActiveSheet.Cells(1, 16384).End(xlToLeft).Column
 lstRow1 = ActiveSheet.Cells(65536, &quot;A&quot;).End(xlUp).Row
 Range(&quot;A&quot; &amp; j, Cells(lstRow1, lstCol)).Copy
 With ws1.Range(&quot;A&quot; &amp; lstRow2)
 .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
 .PasteSpecial Paste:=xlPasteFormats
 End With
 Application.CutCopyMode = False
 lstRow2 = ws1.Cells(65536, &quot;A&quot;).End(xlUp).Row + 1
 j = 2
 End If
 Next

 'Determine the Last Cell in the wsMstr worksheet
 LstCell = Worksheets(wsMstr).Cells.SpecialCells(xlCellTypeLastCell).Address

 With Sheets(wsMstr)
 .Activate
 .ListObjects.Add(xlSrcRange, Range(&quot;$A$1:&quot; &amp; LstCell), , xlYes).Name = &quot;Tbl_Mstr&quot;
 .Range(&quot;A1&quot;).Select
 End With

finished:

 With Application
 .DisplayAlerts = False
 .ScreenUpdating = False
 .EnableEvents = False
 .StatusBar = False
 End With

 Exit Sub

errHandler:

 'If the wsMstr worksheet does not exist a Subscript out of Range error will
 'occur. Create the wsMstr worksheet and return / continue
 If Err.Number = 9 Then
 Sheets.Add Before:=Worksheets(1)
 ActiveSheet.Name = wsMstr
 Resume comeBack
 Else
 errorMsg = &quot;An error has occurred.&quot; &amp; Chr(10) &amp; Chr(10) &amp; Err.Number &amp; &quot; - -&quot; &amp; Err.Description
 MsgBox errorMsg, vbCritical, &quot;Error Message - &quot; &amp; ThisWorkbook.Name
 Resume finished
 End If

End Sub
</code></pre>
</div>

<div>
you should just use SSIS foreach loop 
 
see full &nbsp;ready to use project , just adjust for yourself: 
How to load data from multiple Excel sheets to any destination in SSIS 
<a href="http://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any" rel="ugc">http://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any</a>
</div>

<div>
just be careful with some of the suggested solutions because some of the other suggestions require all sheets in the Excel file must have the same structure.
</div>

<div>
Greate Feedback . I tested the two first proposals and worked fine . I do have the same structure for all sheets (represent sales from all substores of the same company. I retreived the table easily as proposed &nbsp;by Mark and after using a Foreachloop it worked perfectly. 
Just notice, that I tested the solution as proposed by Rgonzo i found out that &nbsp;it works &nbsp;in newer version than visual studio 2005 ('outputbuffer' if &nbsp;Iam correct its not supported in VS2005). &nbsp; 
Thanks all of you &nbsp;for your immediate responce
</div>

<div>
<div class="content wysiwyg-content">
I'am currently working on an SSIS project and I want to get &nbsp;the data from all &nbsp;sheets of 
an Excell file (.xlsx) &nbsp;that I need to import it into an SQL table periodically. I don't know each time the number of sheets its contains. 
I know how to deal with one sheet &nbsp;using for example 
Select * from Openrowset (MicrosoftFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;IMEX=1; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Database=E:\export\file.xlsx', 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'SELECT * FROM [sheet1$]' 
Is there a clever way to get all the sheets? I suspect that somehow i must get the excelschema via script &nbsp;to get the sheet name in an variable and then to use a For-Loop . If that so then could any one can help me with the script.. 
Thanks in Advance
</div>
</div>

I'am currently working on an SSIS project and I want to get  the data from all  sheets of
an Excell file (.xlsx)  that I need to import it into an SQL table periodically. I don't know each time the number of sheets its contains. 
I know how to deal with one sheet  using for example
Select * from Openrowset (MicrosoftFROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;IMEX=1;	Database=E:\export\file.xlsx', 
				'SELECT * FROM [sheet1$]'
Is there a clever way to get all the sheets? I suspect that somehow i must get the excelschema via script  to get the sheet name in an variable and then to use a For-Loop . If that so then could any one can help me with the script..
Thanks in Advance

Read data from all sheets of  an  excel file using OPENROWSET

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. SSIS replaced Data Transformation Services, which had been a feature of SQL Server since Version 7.0.

SSIS

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.