How to import multiple excel files with different sheet names into one sql table using script task in ssis

This is the visual basic Script to import multiple excel files with different sheet names into single SQL table. Here is the Steps to be followed:-

Step 1:- Create a multiple excel sheets with different name and different sheet name.
NOTE:- All Excel files and sheets have same columns.

Step 2:- Create a SQL table which has same attributes as Excel sheet.

Step 3:- Open SSIS project and then create or open package.Create 4 variables for example
@FolderPath- Set value according to the folder where you stored your Excel File for example C:/Excel/
@FileName- Set value according to file name, make sure that this string have complete path for example C:/Excel/ExcelFile1.xls.
@SheetName- This variable have only sheet name for example ABC$.
@FileExtension- Set the file extension for example *.xls

Step 4:-Create Connection Manager.
Excel Connection Manager- Set the expression property to Connection String and value as @FileName.
OLEDB Connection Manager- Set Connection with your SQl Table
Step4:-In Control Flow Task drag For Each loop. In For Each Loop Container drag Script Task and data flow task.connect script Task and data flow task.

Step 5:- Enumerate For Each Loop container with with folder as @Folderpath and files @FileExtension
In Variable mapping select @FileName as Variable and index 0

Step 6:- Edit script Task make sure that you are selecting Scripting Language as MIcrosoft Visual Basic
Set Read only Variable to @FolderPath which you have created.
Set Read and Write Variable to @FileName and @SheetName

Click on Edit Scrit and write the code given below(Make changes according to your requirement).

Public Sub Main()
        Dim excelFile As String
        Dim connectionString As String
        Dim excelConnection As OleDbConnection
        Dim tablesInFile As DataTable
        Dim tableCount As Integer = 0
        Dim tableInFile As DataRow
        Dim currentTable As String
        Dim tableIndex As Integer = 0


        Dim excelTables As String()
        Try
            excelFile = Dts.Variables("@FileName").Value.ToString
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + excelFile + ";Extended Properties=""EXCEL 12.0 XML;HDR=YES"";"
            excelConnection = New OleDbConnection(connectionString)
            excelConnection.Open()
            tablesInFile = excelConnection.GetSchema("Tables")
            tableCount = tablesInFile.Rows.Count

            ReDim excelTables(tableCount - 1)
            For Each tableInFile In tablesInFile.Rows
                currentTable = tableInFile.Item("TABLE_NAME").ToString
                currentTable = currentTable.Replace("'", "")

                If Right(currentTable, 1) = "$" Then
                    excelTables(tableIndex) = currentTable

                    tableIndex += 1
                End If
            Next

            Dts.Variables("@SheetName").Value = excelTables(0)
            excelConnection.Close()
        Catch Ex As Exception
            MessageBox.Show(Ex.Message)

        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub

Step 7:- Goto Data flow Task and Drag a excel Source and OLEDB Destination. Make Connections according to connection Manager.

Step 8:- Edit Excel Source and set properties
AccesMode- OpenRowSet From Variable
RowsetVariable- @SheetName

Step 9:- Save the package and execute.