multiple sheets in excel

I know how to use ssis to import data from the sheet in an excel file.
How do you use ssis to get the data out from different sheets into the database?
Is there such functionality please?
Thanks

arkiboys wrote:

I know how to use ssis to import data from the sheet in an excel file.
How do you use ssis to get the data out from different sheets into the database?
Is there such functionality please?
Thanks

Hi,

I haven't tried this yet, but, there can be at least 2 ways of doing this:

1) Create separate Data Flow Tasks for each sheet.

2) Use the Foreach Loop Container to loop through all the available sheets in the Excel, provided, the structure remains same.

Regards,

B@.ns

|||

I know how to loop through files but not sheets in one file

Do you know how this is done please?

|||I used Sript and following code:

Code Snippet

Private Sub ShowExcelWorksheets(ByVal File As String)
Dim excelTables As System.Data.DataTable
Dim excelTable As System.Data.DataRow
'Dim currentTable As String
Dim excelConnection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ScriptMain.CreateExcelConnectionString(File))
excelConnection.Open()
excelTables = excelConnection.GetSchema("Tables")
For Each excelTable In excelTables.Rows
'Me.ComboBoxWorkSheet.Items.Add(excelTable.Item("TABLE_NAME").ToString)
Next
excelConnection.Close()

End Sub

|||

There is a good how-to article on using the Foreach loop with ADO.NET Schema Rowset enumerator to do this.

http://technet.microsoft.com/en-us/library/ms345182.aspx