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

Question

I use SSIS to load xlsx files which have 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<string> 
("TABLE_NAME");
}
    Dts.TaskResult = (int)ScriptResults.Success;
}

score 2 · Accepted Answer · answered Jan 13 '19 at 20:37

2

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++;
}

Other helpful Links

answered Jan 13 '19 at 20:37

Hadi

36,233
13
65
124

1

Thank you for your answer @Hadi, your script look working besause i added a messageBox and i got it as much as sheets I have in my file. But I'm still having an exception when a fecting the sheet name to my external variable : Dts.Variables["@User::Sheet_Name"].Value = excelSheets[0]; I need only the first sheet name. Help please – MedEc Jan 13 '19 at 21:17
@M.ECH also notice that the variable name is case sensitive – Hadi Jan 13 '19 at 21:22
@M.ECH make sure that the variable is selected in the script ReadWrite variables – Hadi Jan 13 '19 at 21:34
the variable has exactelly the same name as declared and it is selected in the script task in ReadWriteVariables. I've added a MessageBox.Show(excelSheets[0]); and it shows me the sheet name which means that the script works until : Dts.Variables["@User::Sheet_Name"].Value = excelSheets[0].ToString(); but it still not working. Thank your for your help. – MedEc Jan 13 '19 at 22:07
remove `@` character use `Dts.Variables["User::Sheet_Name"].Value` or `Dts.Variables["Sheet_Name"].Value` – Hadi Jan 13 '19 at 22:08
1

It works, thank you so much for your valuable answers and your precious help. Thank you all for your help. How can I close this question with your answer or it's your choice ? – MedEc Jan 14 '19 at 09:44
@M.ECH you have to mark this answer as accepted. Click on the mark below the voting arrows. You can check the [Tour Page](https://www.stackoverfliw.com/tour) for more info – Hadi Jan 14 '19 at 10:54
1

It's done, can you check please and keep pe informed if there are more necessary actions from my side. Thanks. – MedEc Jan 14 '19 at 11:12

score 0 · Answer 2 · answered Jan 13 '19 at 19:48

0

To retrive the excel file sheet name dynamically

 using (OleDbConnection conn = new OleDbConnection(connString))
{
    conn.Open();
    dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    Sheet1= dtSchema.Rows[0].Field<string>("TABLE_NAME");
}

answered Jan 13 '19 at 19:48

Mohammad Ghanem

688
6
20

Thank you for your answer @Mohammed Alghanem, but I'm still having the exception for call on conn.Open();. Thank you for your help sir. – MedEc Jan 13 '19 at 20:04
I've edited my first code after adapting it to yours. Thank you for your help. – MedEc Jan 13 '19 at 20:21
@M.ECH please let me know what is the exception details ? – Mohammad Ghanem Jan 13 '19 at 20:24
Aghanem, DTS Script Task has encountered an exception in user code : an exception was thrown by the target of a call System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) – MedEc Jan 13 '19 at 20:37

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

2 Answers2

Getting sheet names Using OLEDB

Getting the sheet names using Interop library

Other helpful Links