Expression does not use variable full value

[4N6Mstr]

Hello, folks.

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 phanton 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.

Ideas?



Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[TheBugSlayer]

How many valid tabs do your spreadsheets have? If more than one, are you importing data from all or at least one more table? Of course, if that's not the case then all you need to do is import the first tab.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[4N6Mstr]

There will always be at least one valid, but I will not know beforehand how many valid (or invalid) ones in total in each Excel book.

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[fredericofonseca]

try doing the process with some c# code (using the microsoft ACE driver - free and available in 64 bit)instead - I´ve always found that excel would give way too much hassle for different aspects if using the normal SSIS objects

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[4N6Mstr]

at this point, i'm inclined to move to Talend or ETL Tools. Testing both, but there is a learning curve there, as I've been using SSIS for some time.

I just can't understand why SQL (SSIS) / Office guys at Microsoft can't get together and address problems that have been around for so many years.

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there