SSIS Excel 导入强制不正确的列类型
我正在尝试使用 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?
我发现了这个:
- PRB:DTS 向导可能无法检测 SQL Server 中混合数据的 Excel 列类型
我对我的数据进行了排序，以便混合数据类型位于顶部，然后猜猜是什么:问题逆转.它没有导入字符数据，而是停止导入纯数字数据.显然有人不认为 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/存档/2005/01/11/477.aspx
相关文章