I'm working on a DTS to import data from excel files in a SQL database. The thing is, the excel files never have the same structure, meaning, never the same number of columns,sheets as we have to define a destination table and a transformation, do you know a way to do that?
I think you'll need to get the provider of the Excel file to at least include the field even if it doesn't have any data. Then, if in your destination table you can specify that columns allow NULL values, when you receive a file that doesn't have any data for a particular column in your destination table, it won't matter.
You could also set a default value on those columns. Are you working with a native Excel file or a CSV file?