I have set up a foreach loop to go through a directory and import all Excel (*.xls) files into a SQL Server table. The data structure for each file should be the same. The first row contains column headers. However there is an additional column (with no header) at the end of some of these files. This column also needs to be imported. I can add the additional column in my data flow task, but the entire package fails when it comes across a file that does not have this column at all. How can I alter my Data Flow task to ignore this non-existent column?

Since you loop through the files I would approach this the following way: read the first raw of the file if it has N columns then the package takes one avenue, if the file has N+1 columns then another. Save the number to a package variable. Take a look at the Precedence Constraints: http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspx?wa=wsignin1.0 (perhaps there is a better article out there). PS: all you will need is the right expression.Arthur My Blog

you can select always N+1 columns from excel files, I didn't run a test for this actually , but I think if you select N+1 columns , the files which has N columns will import with an extra empty column and files with N+1 columns will import successfully too. Note that you can write sql command like : select f1,f2,... from [Sheet1$] It's worth to try a test with it.http://www.rad.pasfu.com

Good idea, but is that the only route? Make another data flow task? Not that it would take long but I was hoping SSIS provided for something to handle this within the task, rather than at the control flow level.