Thanks for the notes, Gosta.
This isn't actually for any application. It was actually an example spreadsheet that I saw on one of the threads that I've modified a bit. If I were building a student scoring system, there'd be a bit more to it, as you say.
The reason for my questions about SSIS is because I know how to do all of this from SQL Server using T-SQL and the ACE drivers but I don't know much about SSIS except the current 60,000 foot view of what some of it's capabilities and limitations are. These types of spreadsheet-import problems have been presented more and more as questions on these and other forums and I wanted to make sure that it wasn't so easy to do in SSIS that one would be an idiot for not doing it in SSIS before I spent time writing both an article and a presentation on how to do all of this in T-SQL. Part of the goal is to demonstrate that you don't need to have a handle on any thing else other than T-SQL and a very simple call using the ACE drivers to do this.
As I suspected, it's not fall-off-the-slippery-log simple in SSIS. It's also not a beginner task in T-SQL but it's simple enough and does allow for self-healing/expanding imports to staging tables where you can do whatever else you want/need to do from there.
Basically, this all falls into one of those items that I have in my signature line below about "not" doing certain stuff in T-SQL. "Not" is in quotes for a reason. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.