在SSIS中使用动态Excel连接管理器时如何配置输入列 [英] How to configure input columns when using dynamic Excel connection managers in SSIS

问题描述

使用Visual Studio 2015 SSIS创建动态Excel连接管理器并遍历目录中的多个Excel文件后，我遇到了无法更改连接管理器中的列数的问题。 Excel文件没有相同数量的列（或标题名称/位置）。我将来自连接管理器的数据直接传递到脚本组件中，以处理此问题。

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.

我尝试创建一个包含更多列的Excel连接管理器在将其切换到程序包连接并将表达式 ExcelFilePath 设置为我的For / Each循环变量之前使用，但这似乎不起作用。此后，我收到了 VS_NEEDSNEWMETADATA 错误，并且在重建后收到了

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

当少于18列的Excel工作表通过时，在数据源中找不到列 F18

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

错误for /每个循环。

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.

推荐答案

如果Excel文件之间的列数不同，则不能使用相同的Excel源导入它们。您将始终得到您提到的× VS_NEEDSNEWMETADATA 异常。

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:

• 导入具有可变标题的excel文件

• Importing excel files having variable headers

如果您选择将Excel文件转换为平面文件，则有许多链接描述了如何导入具有不同结构的文件的完整过程。

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.

没有这个选择，您必须考虑自动创建更复杂的软件包（使用BIML或DTS包装器）

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

一些将Excel转换为csv的链接：

Some links about converting Excel to csv:

• 用于将Excel转换为CSV的脚本任务


• 在命令行上将XLS转换为CSV


• 自动将Xls转换为CSV


• 使用批处理将xlsx文件转换为csv


• 批量将xls-files转换为csv


• 将.xlsx文件另存为.csv文件的SSIS过程


• 使用脚本任务的SSIS-将XLS转换为CSV

• Script task for converting Excel to CSV
• Convert XLS to CSV on command line
• Auto convert Xls to CSV
• Convert xlsx file to csv using batch
• Batch convert xls-Files to csv
• SSIS process for saving .xlsx file as .csv file
• SSIS using Script task - Convert XLS to CSV

这篇关于在SSIS中使用动态Excel连接管理器时如何配置输入列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！