2

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
DaveDD
  • 63
  • 6

1 Answers1

1

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Thanks. I take it there isn't a straightforward way to convert from excel to flat with SSIS? I don't have VB or VC# installed with SSIS, but managed to compile use vbc at a command prompt. Haven't yet been able to create a package with the DTS wrapper but I'm going to keep trying as that sounds like it'd be the best option for me if I can figure it out. – DaveDD Feb 09 '19 at 05:38
  • I didn't understood how vb is not installed, can you add a script task?? Also i will provide some links on converting excel to csv – Hadi Feb 09 '19 at 07:57
  • @DaveDD also if the answer solved the issue you have to accept it. Check the [Tour page](https://stackoverflow.com/tour) – Hadi Feb 09 '19 at 09:35
  • Hadi, I will update during the work week. Thank you. – DaveDD Feb 11 '19 at 07:02
  • 1
    The Visual Studio 2015 installation I am using is Shell (Integrated), so programming languages are not included. I'm not sure if I would be able to run VBC to compile an automated package generation but it is costing me too much time to try to do this. I tried using a .vbs script to convert Excel to CSV but there is an error when I try calling .SaveAs. I may try to use the XLS to CSV link method linked above. – DaveDD Feb 11 '19 at 21:25
  • @DaveDD thanks for accepting the answer. I will be waiting for the result and always ready to give support – Hadi Feb 11 '19 at 21:32