SQL Server Integration Services – loading Excel files

As BI consultants we regularly get the question to load one or more Excel files. Files tend to have different layouts, almost always consist of multiple worksheets and columns often contain different data types, making it more complex to load an Excel file than it would initially appear.