How to configure input columns when using dynamic Excel connection managers in SSIS

2
votes

After creating a dynamic Excel connection manager with Visual Studio 2015 SSIS and iterating through multiple Excel files in a directory I have run into the problem of not being able to change the number of columns in the connection manager. The Excel files do not have the same number of columns (or heading names/locations). I'm passing the data from the connection manager straight into a script component in order to handle this.

I tried creating an Excel connection manager with more columns they I will ever use before switching it to a Package Connection and setting the Expressions ExcelFilePath to my For/Each loop variable but this doesn't seem to work. I've received the VS_NEEDSNEWMETADATA error after this and, after rebuilding, received a

"Column "F18" cannot be found at the datasource"

error when an Excel sheet with fewer than 18 columns was passed through the for/each loop.

Any suggestions or assistance would be appreciated. Thank you.

1
sql-serverexcelssisetl

1 Answers

1
votes

If the columns count are different between Excel files, you cannot use the same Excel source to import them. You will always get the×VS_NEEDSNEWMETADATA exception that you mentioned.

If you are handling Excel files with same structure but with different columns order you can refer to my detailed answer on the link below:

If you have the choice to convert Excel files to Flat files, there are many links that describe the full process on how to import files with different structure.

If you don't have this choice, you must think on automating packages creation which is more complex (using BIML or DTS wrappers)

Update 1

Some links about converting Excel to csv: