What is the best approach to managing daily excel flat file imports into SQL Server with varying column counts per file?
You're dealing with unstructured data here I'm afraid and honestly, I feel your pain. I don't think you can really do what you want in SSIS - partially because it will expect your Excel format to be fixed. Even if you get it fixed, with SSIS and Excel - you'll have problems with how it guesses the metadata types for columns based on a sample of the data in the sheet - with the Excel provider, you only have 2 choices for text data for example - NVARCHAR(255) and NVARCHAR(MAX). It can get ugly pretty fast if you try to enforce data-typing at a data level even with fixed columns but varying lengths.

As such, I think you'll need to embrace the unstructured nature of the data and store it as such - storing it as an XML/JSON data types and converting the data from the spreadsheet and letting the MVC application parse the XML. The only other way I can suggest is using something like a key-value store. There's huge drawbacks to this from a relational perspective though. This is commonly known as EAV (Entity-Attribute-Value)

You might also want to consider non-SQL Server alternatives that can take unstructured data and search on it as well.