SSIS - How to dynamically import data from multiple spreadsheets to multiple SQL Server tables

Yes. In my script task script (please note (1) how I have changed the query to Insert Into as the tables would always be existing and (2) all sheet names are different from the different files so I am taking this into consideration)::

Imports System
Imports System.IO
Imports System.Text
Imports System.Windows.Forms
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    ' The execution engine calls this method when the task executes.  
    ' To access the object model, use the Dts object. Connections, variables, events,  
    ' and logging features are available as static members of the Dts class.  
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.  
    '  
    ' To open Code and Text Editor Help, press F1.  
    ' To open Object Browser, press Ctrl+Alt+J.  
    Public Sub Main()
        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"  
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
        ' Create a new HTTP client connection  
        Dim connection As New HttpClientConnection(nativeObject)

        'exteract filename for urlstring
        Dim FullUrlString As String = Dts.Variables("User::UrlString").Value.ToString()
        Dim FindTemplate As String = "outputFileName="
        Dim startIndex As Integer = FullUrlString.IndexOf(FindTemplate)
        FullUrlString = FullUrlString.Substring(startIndex + FindTemplate.Length)
        Dim endIndex As Integer = FullUrlString.IndexOf(".xls")
        FullUrlString = FullUrlString.Remove(endIndex)

        Dts.Variables("User::DestTableName").Value = FullUrlString


        ' Download the file #1  
        ' Save the file from the connection manager to the local path specified  
        Dim filename As String = Dts.Variables("User::FilePath").Value.ToString() + System.IO.Path.DirectorySeparatorChar + FullUrlString + ".xls"

        'Dts.Connections("HTTP Connection Manager").ReleaseConnection(nativeObject)
        Dim SheetName As String

        Select Case FullUrlString
            Case "AccMaterialHierarchySource"
                SheetName = "MaterialGroup"
            Case "AccAllPartsSource"
                SheetName = "Parts"
            Case "AccPartsDocMappingSource"
                SheetName = "PartsDocMapping"
            Case "AccVendorMappingSource"
                SheetName = "VendorMapping"
            Case "AccBomSource"
                SheetName = "BOM"
            Case "AccWhereUsedSource"
                SheetName = "WhereUsed"

        End Select

        Dts.Variables("User::SqlString").Value = "INSERT INTO " + FullUrlString + " SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" + filename + "', 'SELECT * FROM [" + SheetName + "$]')"

        connection.DownloadFile(filename, True)


        Dts.TaskResult = Dts.Results.Success
    End Sub


End Class

good, you did it correctly.
Regards,