Problem with variable (Excel tab name) in a Foreach Loop

• LHumberto

  SSC Enthusiast

  Points: 132

  More actions

  October 4, 2013 at 1:01 pm

  #278701

  I have to import a number of excel spreadsheets. I'm using the classic Foreach Loop inside another Foreach loop approach. The outside loop (Foreach File Enumerator) cycles through the Excel files, while the inside loop (Foreach ADO.NET Schema Rowset Enumerator - ExcelSchema - Tables) to cycle through the individual Excel sheets in each file.

  Nothing special there; however, for some reason these excel files have some "phantom" tabs that should not be imported. I call them phanton because they show up as an importable tab in a SSSIS import wizard but actually are not listed in the excel file structure (no, they are not hidden tabs, I checked).

  My idea is to use a constraint to NOT import those phanton tabs. The name convention should allow me to do that because the normal tabs have the name 'AAAAAAyyyymmdd$' and the phantom tabs show up as 'AAAAAAyyyymmdd$'_xlnm#_FilterDatabase (the line below was captured from the Local Variable window and show one of the phantom tabs name).

  + User::WorksheetName {'AAAAAAyyyymmdd$'_xlnm#_FilterDatabase} String

  I tried using Len (@[User::WorksheetName]) == 17, which corresponds to the length of the normal tabs name ('AAAAAAyyyymmdd$'). However, it does not work. For some reason the portion of the phantom tab name after the ending single quote (_xlnm#_FilterDatabase) appears to be ignored.

  I tested with a number of different expressions, including reversing the variable, to no avail. It seems that internally just the standard name between quotes is what the constraint sees. Or the other possibility is that the value that is being used is not the current one. If that is the case how can i use the "true" current value in my expression?

  Ideas?

  LRH

  If you don't have a destination,
  No matter how fast you travel
  You never get there.