为何SSIS读取Excel时文件开闭状态下数据格式有差异？如何统一读取？

我正在使用SSIS读取Excel文件。在连接管理器的连接字符串中，已设置IMEX=1以支持读取混合数据类型，并且取消勾选“第一行是标题”，确保第一行被识别为文本，以此保证能读取混合数据类型。

查询语句如下：

SELECT * FROM [SHEET1$]

读取到的数据会被写入SQL Server中定义为nvarchar数据类型的表列。

观察到：当Excel文件处于打开状态和关闭状态时运行包，Excel数据源任务的预览结果不同，写入数据库表的数据也存在差异。

以数字列（示例：2,000）和百分比列（示例：90%）为例：

• 若保持Excel文件打开并运行包，预览显示2000和0.9，写入数据库的数据也是如此；
• 若关闭文件后运行包，预览显示2,000和90%，写入数据库的数据同样如此。

请问为何运行包时文件的开闭状态会导致读取的数据值不同？针对存在混合值的Excel文件，如何实现一致且正确的读取？

这是因为Excel文件打开和关闭时，SSIS依赖的OLEDB驱动读取逻辑存在差异：

• 文件关闭时：驱动读取的是Excel单元格的格式化显示文本，比如带千分符的数字、带%符号的百分比，此时IMEX=1会按文本类型读取内容，保留单元格的显示格式。
• 文件打开时：Excel处于活跃编辑状态，驱动直接读取单元格的底层原始值，比如数字的实际数值、百分比对应的小数，而非格式化后的显示文本，因此得到的是不带格式的纯数值。

要实现一致且正确的读取，可按以下方式处理：

1. 优化连接字符串参数：在现有IMEX=1基础上，添加TypeGuessRows=0和ImportMixedTypes=Text，强制驱动将所有混合类型列按文本读取，避免类型推断偏差。完整连接字符串示例：

   Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourExcelFile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text";
   

   • TypeGuessRows=0：禁止驱动通过前N行推断数据类型，改为扫描所有行确定类型
   • ImportMixedTypes=Text：将混合数据类型的列统一以文本格式读取
2. 固定文件状态：根据需求统一文件状态：
   • 若需要保留Excel的格式化显示文本，运行包前务必关闭目标Excel文件；
   • 若需要读取单元格的原始数值，则保持文件打开，确保每次运行状态一致。
3. 添加数据转换逻辑：在SSIS数据流中加入「数据转换」组件，将读取到的文本统一转换为目标格式，比如把带千分符的文本转成数值、把百分比文本转成小数，确保写入数据库的格式完全一致。

内容的提问来源于stack exchange，提问作者variable