使用 SSIS 导入/导出向导在数字列中保留 NULL？
我在使用 SSIS 数据导入向导将数据从制表符分隔的平面文件（TSV 文件）上传到 SQL Server 2005 时遇到问题。我在 SQL Server 2000 中使用等效过程时没有遇到此问题，并且我已经检查了我尝试导入的文件的内部结构自 SQL Server 升级发生之前就没有发生变化。
问题在于，具有数字数据类型（例如，smallint、float 等）的列中的所有空白值在导入时都将转换为 0，而不是 NULL。这意味着对这些数据进行平均会给出错误的输出。
TSV 文件不包含文本限定符，但是使用一些虚拟数据测试限定符的使用并没有解决此问题。
可以通过导入 VARCHAR 列来保留 NULL，但这远非理想。有没有办法指示 SSIS 导入/导出向导将平面文件中的空白值导入到数字数据类型为 NULL 而不是 0 的列中？
I am having a problem uploading data from tab-delimited flat files (TSV files) into SQL Server 2005 using the SSIS Data Import wizard. I did not experience this problem using the equivalent procedure in SQL Server 2000, and I have checked that the internal structure of the files I am trying to import is unchanged since well before the SQL Server upgrade took place.
The problem is that all blank values in columns with numeric data types (e.g. smallint, float etc) are being converted to 0s on import, instead of NULL. This means that AVGing across these data is giving erroneous output.
The TSV files do not include text qualifiers, however testing the use of qualifiers with some dummy data did not lead to a resolution of this problem.
It is possible to retain the NULLs by importing into VARCHAR columns, however this is far from ideal. Is there a way of instructing the SSIS Import/Export wizard to import blank values from flat files into columns with numeric data types as NULL rather than 0?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
@gbn：谢谢你的指点。我相信我现在已经找到了解决此问题的方法，并且能够成功地将数字列中包含 NULL 值的数据导入到我的 SQL Server 2005 数据库中。
如果其他人遇到同样的问题：
我使用 Business Intelligence Development Studio 中的数据流任务（而不是像以前那样使用dtswizard）通过构建导入数据从平面文件源到 OLE DB 目标的数据流任务。
在“平面文件源编辑器”框中，有一个“将源中的空值保留为数据流中的空值”复选框。勾选此项似乎可以解决此问题。
正如 @gbn 指出的，向导中缺少此框。
@gbn: Thanks for the pointer. I believe I have now found a way around this problem and have been able to successfully import into my SQL Server 2005 database data containing NULL values in numerical columns.
In case anyone else is having the same problem:
I imported the data using the Data Flow task in the Business Intelligence Development Studio (rather than using the dtswizard as previously) by building a Data Flow task from Flat File Source to OLE DB Destination.
In the Flat File Source Editor box there is a 'retain null values from the source as null values in the data flow' tick-box. Ticking this appears to resolve this problem.
As @gbn pointed out, this box is missing from the wizard.