INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Expression does not use variable full value

Expression does not use variable full value

(OP)
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

RE: Expression does not use variable full value

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)

RE: Expression does not use variable full value

(OP)
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

RE: Expression does not use variable full value

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Expression does not use variable full value

(OP)
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. ponder

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close