SSIS Excel Import - Worksheet variable OR wildcard

Question

I have a SSIS data import package that uses a source Excel spreadsheet and then imports data into a SQL Server database table. I have been unsuccessful in automating this process because the Excel file's worksheet name is changed every day. So, I have had to manually change the worksheet name before running the import each day. As a caveat, there will never be any other worksheets.

Can I make a variable for the worksheet name? Can I use a wildcard character rather than the worksheet name? Would I be better off creating an Excel macro or similar to change the worksheet name before launching the import job?

Answer 1

If you are using SSIS to import the sheet you could use a script task to find the name of the sheet and then change the name or whatever else you needed to do in order to make it fit the rest of your import. Here is an example of finding the sheet I found here

Dim excel As New Microsoft.Office.Interop. Excel.ApplicationClass
Dim wBook As Microsoft.Office.Interop. Excel.Workbook
Dim wSheet As Microsoft.Office.Interop. Excel.Worksheet

wBook = excel.Workbooks.Open 
wSheet = wBook.ActiveSheet()

For Each wSheet In wBook.Sheets
MsgBox(wSheet.Name)  
Next

On the MsgBox line is where you could change the name or report it back for another process

Answer 2

I use the follow script task (C#):

System.Data.OleDb.OleDbConnection objConn;
DataTable dt;

string connStr = ""; //Use the same connection string that you have in your package
objConn = new System.Data.OleDb.OleDbConnection(ConnStr);
objConn.Open();

dt = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbShemaGuid.Tables,null);
objConn.Close();

foreach(DataRow r in dt.Rows)
{
   //for some reason there is always a duplicate sheet with underscore.
   string t = r["TABLE_NAME"].ToString(); 

   //Note if more than one sheet exist this will only capture the last one
   if(t.Substring(t.Length-1)!="_")
   {
       Dts.Variables["YourVariable"].Value = t;
   }
}

And then in SSIS, I add another variable to build my SQL.

new variable "Select * from [" + "Your Variable" + "]"

Finally set your datasource to that SQL variable in Excel Source.