SSIS Excel导入强制不正确的列类型 [英] SSIS Excel Import Forcing Incorrect Column Type
问题描述
我正在尝试使用SSIS将电子表格导入到我们的数据库。由于某种原因，SSIS希望相信其中两列的类型为Double，当它们包含字符数据时。我已经尝试重新映射列为nvarchar（255），但它仍然不想选择它认为是双倍的数据，因为它有字符。如果我尝试编辑SSIS包并更改Excel源中的列类型，则不会让我更改错误输出中的列类型，如果常规输出和错误输出列不匹配。
I'm trying to import a spreadsheet to our database using SSIS. For some reason SSIS wants to believe two of the columns are of type Double, when they contain character data. I've tried remapping the columns to be nvarchar(255) but it still doesn't want to select the data it thinks is double, because there are characters in it. If I try to edit the SSIS package and change the column types in the Excel Source, it won't let me change the type of the columns in the Error Output and gives me an error if the regular output and error output columns don't match.
为什么SSIS坚持这些列是Double？如何强制它实现这些是字符串？为什么微软的一切都不能正常工作？
Why is SSIS insisting that these columns are Double? How can I force it to realize these are strings? Why does everything from microsoft have to not quite work correctly?
编辑：我发现这个： http://support.microsoft.com/kb/236605
我排序了我的数据，以便混合的数据类型将在顶部，并猜测什么：问题反转。它不是不导入字符数据，而是停止导入纯数字数据。显然有人认为12345可以表示为一个字符串...
I sorted my data so that mixed data types would be at the top, and guess what: The problem reversed. Instead of not importing character data, it stopped importing purely numeric data. Apparently someone doesn't think 12345 can be represented as a string...
推荐答案
我以前看过这个问题， Excel是不是SSIS的问题。 Excel会对前几行进行采样，然后推断数据类型即使您明确将其设置为文本。您需要做的是将其放入SSIS包中的Excel文件连接字符串中。该指令告诉Excel，这些列包含混合数据类型，并提示它们在确定该列是数字类型之前进行额外的检查。实际上它不是。
I've seen this issue before, it's Excel that is the issue not SSIS. Excel samples the 1st few rows and then infers the data type even if you explicitly set it to text. What you need to do is put this into the Excel file connection string in the SSIS package. This instruction tells Excel that the columns contain mixed data types and hints it to do extra checking before deciding that the column is a numeric type when in fact it's not.
;Extended Properties="IMEX=1"
它应该适用于（在多数情况下）。更安全的做法是将Excel数据导出到制表符分隔文本，并使用SSIS导入。
It should work with this (in most cases). The safer thing to do is export the Excel data to tab delimited text and use SSIS to import that.
- http://blogs.acceleration.net/ryan/archive/2005/01/11/477.aspx
这篇关于SSIS Excel导入强制不正确的列类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
