hello, I have a SAP system that exports data to excel file, then we import this data to SQL server. i wanted to automate this process by developing a SSIS package, but when i double click the Excel Source SSIS data flow item in the SSIS package i'm developing, and wanted to choose the "Name of the Excel sheet" (after i chose the "excel connection manager" that connects to the wanted excel file), i got the following: i got those sheet names, but really they are not there in the excel sheet, there is just only one sheet and named "Sheet1" !!! if i copy the data to another new excel file (and of course modify the connection) it works fine and it shows just Sheet1. FYI, the excel file extension is .xlsx does anybody know why this happens and how to fix it ?!

it is called "Excel named Range", open the file in excel and on the left top right corner open the drop down list and you will see the same list as you have shown in your screen shot , to by pass this in SSIS , 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 End Sub check LinkSincerely SH -- MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).