By Pass "Excel Named Range" in SSIS When Importing Data From Excel File

Sometimes when you want to import data from excel file using SSIS, and when you want to edit the Excel Source SSIS data item to choose the sheet that contains the data, you will be shocked with a big list of sheets names that they are not even in the excel file you want to import data from, something like:

DATA1, DATA2, DATA3 ... AND TEST1, TEST2, TEST3 ... and other sheet names

if the name of the sheet that contains the data is always the same, you can find the name of that sheet in the list and that's all, but if the name of the sheet that contains the data changes from time to time and from file to file , for example the name of the sheet is the date when the data was inserted in the sheet, you have to use a script task to get the sheet with data ( known as the TABLE_NAME) and by pass the Excel sheet names , the code is:

        Try            
            Dim strConnectionString As String
            Dim oledbExcelConnection As OleDbConnection
            Dim ExcelDataTable As DataTable
            Dim SheetCount As Integer = 0
            Dim ExcelSheet As DataRow
            Dim CurrentSheet As String
            Dim ListOfExcelSheets As String()
            Dim LoopForNumberOfRealTables As Integer = 0

            Dim flagSourceMultiSheet As Boolean
            flagSourceMultiSheet = Dts.Variables("uVar_SourceMultiSheet").Value

            If flagSourceMultiSheet = True Then
                strConnectionString = Dts.Variables("uVar_SourceConnectionString").Value.ToString
                oledbExcelConnection = New OleDbConnection(strConnectionString)
                oledbExcelConnection.Open()

                ExcelDataTable = oledbExcelConnection.GetSchema("Tables")
                SheetCount = ExcelDataTable.Rows.Count

                For Each ExcelSheet In ExcelDataTable.Rows
                    CurrentSheet = ExcelSheet.Item("TABLE_NAME").ToString
                    CurrentSheet = CurrentSheet.Replace("'", "")

                    If Right(CurrentSheet, 1) = "$" Then
                        LoopForNumberOfRealTables += 1
                        ReDim Preserve ListOfExcelSheets(LoopForNumberOfRealTables - 1)
                        ListOfExcelSheets(LoopForNumberOfRealTables - 1) = CurrentSheet
                    End If
                Next
                oledbExcelConnection.Close()
            Else
                ReDim Preserve ListOfExcelSheets(0)
                ListOfExcelSheets(0) = Dts.Variables("uVar_SourceActiveSheetName").Value.ToString
            End If

            Dts.Variables("uVar_ArrayOfExcelSheetNames").Value = ListOfExcelSheets
            Dts.TaskResult = ScriptResults.Success

        Catch ex As Exception
            Dim msgException As String
            msgException = ex.Message.ToString
        End Try

by the way, this list is called "Excel Named Ranged".
Also you can check the article titled Looping Through Excel Files And Sheets in SSIS, it contains a good detailed case and step by step example.