Get name of the first Excel sheet dynamically and affect it to a variable

Question

I use SSIS to load xlsx files wich having different sheets names but the same structure. I need to load only the first sheet of each file, but the name can't be the same each time, so I need to always point on the first sheet regardless it's name. I think that in data access mode in Excel source task is not possible to use the index(1st) that's why I'm trying to use Task script to get the name of the first sheet of each file and put it in a variable Sheet_name and use it in the data access mode for each file.

I have an exception with the code above and I don't know how to solve it.

I've tried to look for a solution without using script but I didn't find it, that's why I'm trying to get my code work.

 public void Main()
 {
        String FolderPath = 
  Dts.Variables["User::Folder_To_Be_Processed_Path"].Value.ToString();
  String File_Name = Dts.Variables["User::File_Name"].Value.ToString();
  string fileFullPath = "";
  fileFullPath = FolderPath + "\" + File_Name;
  string connString = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source=" + 
 fileFullPath + ";Extended Properties="Excel 12.0;HDR=YES";";  

 using (OleDbConnection conn = new OleDbConnection(connString))
{
conn.Open();
MessageBox.Show(connString);
dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { 
null, null, null, "TABLE" });
Dts.Variables["User::Sheet_Name"].Value= dtSchema.Rows[0].Field 
("TABLE_NAME");
}
    Dts.TaskResult = (int)ScriptResults.Success;
}

user_7031230 · Answer

There are many Similar Posts on StackOverflow and Other Communities, The main idea is that OLEDB does not retrieve the Sheet names in the same order found in the Excel Workbook or you need to order the result table.

Getting sheet names Using OLEDB

You should sort the DataTable dtSchema by the ORDINAL_POSITION column since the OLEDB Provider does not return them in that order.

Getting the sheet names using Interop library

From this MSDN - post, the moderator mentioned that:

I am afraid that OLEDB does not preserve the sheet order as they were in Excel. Do you have to use OLEDB ? Another way to get the sheet names is using the office interop classes.

And provided the following code to get sheets names:

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook excelBook = xlApp.Workbooks.Open("D:\Book1.xlsx"); 

String[] excelSheets = new String[excelBook.Worksheets.Count];
int i = 0;
foreach(Microsoft.Office.Interop.Excel.Worksheet wSheet in excelBook.Worksheets)    
{
  excelSheets[i] = wSheet.Name;
  i++;
}

Get name of the first Excel sheet dynamically and affect it to a variable

2 Answers

Getting sheet names Using OLEDB

Getting the sheet names using Interop library

Other helpful Links