SQL Server 2005删除/插入截断错误
因此，我尝试使用 SSIS 进行批量插入并不断获取：
“Microsoft SQL Native Client”Hresult：0x80004005 描述：“字符串或二进制数据将被截断。”
即使我已经将每一列的数据转换为与插入行的表完全相同的类型。 我使用了一个视图，数据看起来就像在数据库插入步骤之前应该的那样。 仍然收到错误。
接下来，我进入 SQL Server Management Studio 并在该该死的表中设置插入查询，但仍然遇到相同的截断错误。 然后我设置了 ANSI_WARNINGS OFF，插入工作数据在表中看起来不错。 现在，当我尝试删除这一行时，出现截断错误。
除了对这种情况的任何基本输入之外，我的问题是如何关闭 SSIS 中的 ANSI_WARNINGS 以便批量加载可以通过？
So I am trying to do a bulk insert with SSIS and continually get:
"Microsoft SQL Native Client" Hresult: 0x80004005 Description: "String or binary data would be truncated."
Even though I already have a data conversion for every column into the exact same type as the table that the rows are getting inserted into. I used a view and the data looks like it supposed to just before the DB insert step. Still get the error.
Next I went into sql server management studio and setup an insert query into that damned table and still get the same truncation error. I then did a set ANSI_WARNINGS OFF and the insert works data looks good in the table. Now when I try to delete this row I get the Truncation error.
My question besides any basic input to the situation is how can I turn off the ANSI_WARNINGS within SSIS so that the bulk load can go though?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
听起来您的列太窄，无法接受您提交的数据。
您能验证一下情况是否如此吗？
当我们与第三方确定架构时，我经常遇到一个非常类似的问题。
您可以选择视图中所有列的 LEN 吗？ 这可以帮助找到问题。
除此之外，我发现的唯一方法是打印源数据列实际长度的报告。
It sounds like you have a column that is too narrow to accept the data you are submitting.
Can you verify if this is or isn't the case?
I had a very similar issue arise frequently while we were nailing down a schema with a third party.
Can you select a LEN of all of the columns in the view? That could help find the issue.
Other than that, the only way I have found is to print out a report of the actual lengths of the source data columns.
听起来您有一行（可能更多，但只需要一行！），其中数据值超过了表列的长度。 将数据转换为较短类型会将错误转移到从目标进行转换的任何转换。 我建议创建一个平面文件目标，并将转换的错误输出绑定到它。 将错误结果更改为“重定向行”。 这将允许所有有效行通过，并为您提供要被截断的行的副本，以便您手动处理。
Sounds like you've got one row (possibly more, but it only takes one!) where your data value exceeds the length of the table columns. Doing a data conversion to the shorter type will MOVE the error to whatever transform does the conversion from the Destination. What I'd recommend is creating a Flat File Destination, and tying the error output of your transforms to it. Change the error result to 'Redirect Row'. This will allow all the valid rows to go through, and provide you with a copy of the row(s) that are getting truncated for you to manually handle.
您要插入的表上是否有触发器？ 那么错误可能来自触发器所采取的操作。
Are there triggers on the table you're inserting into? Then the error may come from an action that the trigger takes.
事实证明，在 SSIS 中，您可以使用“数据访问模式 > 表或视图：快速模式”设置 OLE DB 目标。 当我选择此设置时，批量插入不会出现任何警告或错误，并且数据在数据库中看起来很完美。 不确定这一更改到底做了什么，但它确实有效，在一个 SSIS 插入上 16 小时后，我对结果感到满意。
感谢您的建议。
Turns out that in SSIS you can setup the OLE DB Destination with "Data Access Mode > Table or view: Fast Mode". When I chose this setting the bulk insert went through without any warnings or errors and the data looks perfect in the database. Not sure what this change did exactly but it worked and after 16hours on one SSIS insert I'm happy with results.
Thanks for the suggestions.