Where I am working I get Excel files in Bulk from various sources. We have specified file structure i.e. Column Name, Data Type etc. However this file created by different user manually. I have setup SSIS ETL process to upload data in Staging table. The issue I am having is This file created manually so sometime users make mistake, sometime they change column order or column name, and make data error. i.e. Date so my package get fail. I do have to process 100s of files together so can't check each file manually. Can anyone tell me the best way to resolve this issue.
Is it a good idea if I upload all data in Text Format from Excel and then I can easily check data or type using stored procedure?