SQL Server Management Studio：导入时悄悄忽略 99.9% 的数据
问题
我正在尝试使用 SQL Server Management Studio 的导入数据任务将数据导入到表中。它只引入了 26 行，而原始行数为 49,325 行。 （编辑：这就是 99.9% 的来源：(1-26/49325)*100 = 99.9%
在企业管理器中使用 DTS 可以正确获取所有 49,325 行。
为什么 SSMS未导入所有行，报告已成功传输 49,325 行，并且没有遇到任何错误？ 为什么企业管理器能够正确导入所有 49,325 行？
Microsoft SQL Server Management Studio 版本：10.0.1600.22（来自 SQL Server 2008，今天安装在全新的设备上） Windows 7 计算机，应用 SP1）
证明 - 使用 SSMS 导入
STRTransactions 表最初为空：
来源是 上的 ：ContosoFrobManager 数据库锂
目标是 lithium 上的 Grob 数据库；
我想从一个（或多个）表复制数据：
我想复制 STRTransactions 表： 
您可以追加到现有表格，这很好（它是空的）。我想启用身份插入。并且不要尝试导入时间戳（因为无论如何你都会抱怨）： 
立即运行，就可以了： 
是的，你要做的事情： 
我设法在它传输 49,325 行（大约 1k 标记）时捕获它： 
全部完成。全部 49,325 行复制成功： 
这是报告：
执行成功
初始化数据流任务（成功）
初始化连接（成功）
设置SQL命令（成功）
设置源连接（成功）
设置目标连接（成功）
验证（成功）消息警告 0x80049304：数据流任务 1： 警告：无法打开全局共享 内存与性能的沟通 动态链接库；数据流性能计数器 不可用。要解决此问题，请运行 以管理员身份使用该软件包，或者 在系统的控制台上。 （SQL 服务器 导入和导出向导）警告 0x80047076：数据流任务1： 输出列“时间戳”(158) 输出“OLE DB 源输出”(11) 和 组件“源 - STRTransactions” (1) 随后不再使用 数据流任务。删除这个未使用的 输出列可以增加数据流 任务表现。 （SQL Server 导入 和导出向导）
准备执行（成功）
预执行（成功）
执行（成功）
正在复制到 [dbo].[STRTransactions]（成功）已传输 49325 行
消息信息0x402090df：数据 流程任务 1：最终提交 数据插入“组件 “目的地 - STRTransactions”(163)” 已经开始了。 （SQL Server 导入和 导出向导）信息 0x402090e0：数据流任务1： 数据插入的最终提交 在“组件”目的地 - STRTransactions“(163)”已结束。 （SQL Server导入和导出向导）
- 执行后（成功）消息信息 0x4004300b：数据流任务 1：“组件”目的地- STRTransactions“（163）”写了 49325 行。 （SQL Server 导入和导出 向导）
非常好。全部完成。 “写入了 49325 行”。
只是它只写了 26 行：
显然我没有疯。我做的一切都是对的。即使我没有这样做，SSMS 也没有给出任何问题的迹象。我已经重复了这些相同的步骤 8 次：
- 我自己重复了 5 次，
- 两次向两个不同的同事演示
- 一次，截图这个 SO 问题的过程
每次都是 26 行，不多也不少。但到底谁被毁了，是师父，还是徒弟？
但为了证明我没有做错任何事情，我们将使用企业管理器再次尝试。十多年前编写的优秀工具：
Proof - Import using Enterprise Manager
我已从 STRTransactions 表中删除了 26 行。我可以提供一张屏幕截图，显示我从一张空桌子开始；或者你可以相信我。由于向导几乎相同，因此您将看到几乎相同的屏幕截图。对此感到抱歉；但如果没有证据，没有人会相信我。
我想从 lithium 上的 ContosoFrobManager 数据库导出：
我想导入到 Grob 数据库 <代码>锂：
我想要复制表格：
我想要复制 STR 交易表：
您可以继续将行追加到现有表中（无论如何它都是空的）。我希望你插入身份值。并且不要尝试插入 timestamp 值，无论如何您都会抛出错误：
立即运行：
是的，你要做的事情：
我设法在导入过程中捕获它，大约 12k 行：
< a href="https://i.sstatic.net/CtIgk.png" rel="nofollow noreferrer">
全部完成，已成功复制 49,325 行：
我们从表中选择以查看行：
为什么 SSMS 是一个具有以下功能的工具积极开发了6年了，现在还没进去吧？虽然企业管理器最初的开发团队几乎没有错误？这是 SSMS 中严重错误的另一个例子。我发现的最后一个严重错误是它没有为所有对象编写脚本。
我怀疑我看到的唯一答案是
- 使用不同的工具，
- 您是否已在 Microsoft Connect 上报告过该问题？
- 调用 PSS
当然我有一个解决方法：停止使用 SQL Server Management Studio。但我已经必须在 XP 模式 32 位兼容性中运行企业管理器（正如您可以通过我的 Aero 主题桌面上的 Luna 主题窗口看到的那样）
我： 我让它工作了 同事：怎么样？ 我：我使用了企业管理器 同事： 嘲笑 好吧。 同事：那么我可能必须安装它。
我在 Microsoft Connect 上创建了票证，但几周后就关闭了。
The Problem
i'm trying to import data into a table using SQL Server Management Studio's Import Data task. It only brings in 26 rows, out of the original 49,325. (Edit: That's where 99.9% comes from: (1-26/49325)*100 = 99.9%
Using DTS in Enterprise Manager correctly brings all 49,325 rows.
Why is SSMS not importing all rows, reporting that it transferred 49,325 successfully, and experienced no errors? Why is Enterprise Manager able to correctly import all 49,325 rows?
Microsoft SQL Server Management Studio version: 10.0.1600.22 (From SQL Server 2008, installed today on a fresh Windows 7 machine, SP1 applied)
Proof - Import using SSMS
The STRTransactions table is initially empty:
Source is the ContosoFrobManager database on lithium:
Destination is the Grob database on lithium;
i want to copy data from one (or more) tables:
i want to copy the STRTransactions table:
You can append to the existing table, that's fine (it's empty). i want to enable identity inserts. And don't try to import a timestamp (since you'll just complain anyway):
Run immediately, that's fine:
Yup, you're going to do stuff:
i managed to catch it while it was transferring the 49,325 rows, around the 1k mark:
All done. All 49,325 rows copied successfully:
And here's the report:
The execution was successful
Initializing Data Flow Task (Success)
Initializing Connections (Success)
Setting SQL Command (Success)
Setting Source Connection (Success)
Setting Destination Connection (Success)
Validating (Success) Messages Warning 0x80049304: Data Flow Task 1:
Warning: Could not open global shared
memory to communicate with performance
DLL; data flow performance counters
are not available. To resolve, run
this package as an administrator, or
on the system's console. (SQL Server
Import and Export Wizard) Warning
0x80047076: Data Flow Task 1: The
output column "timestamp" (158) on
output "OLE DB Source Output" (11) and
component "Source - STRTransactions"
(1) is not subsequently used in the
Data Flow task. Removing this unused
output column can increase Data Flow
task performance. (SQL Server Import
and Export Wizard)Prepare for Execute (Success)
Pre-execute (Success)
Executing (Success)
Copying to [dbo].[STRTransactions] (Success) 49325 rows transferred
Messages Information 0x402090df: Data
Flow Task 1: The final commit for the
data insertion in "component
"Destination - STRTransactions" (163)"
has started. (SQL Server Import and
Export Wizard) Information
0x402090e0: Data Flow Task 1: The
final commit for the data insertion
in "component "Destination -
STRTransactions" (163)" has ended.
(SQL Server Import and Export Wizard)
- Post-execute (Success) Messages Information 0x4004300b: Data Flow Task
1: "component "Destination -
STRTransactions" (163)" wrote 49325
rows. (SQL Server Import and Export
Wizard)
Excellent. All done. "wrote 49325 rows".
Except that it only wrote 26 rows:
Obviously i'm not crazy. i did everything right. And even if i didn't, SSMS gives no indication of any problems. i've repeated these same steps 8 times:
- 5 times for myself
- twice to demonstrate to two different colleagues
- once to screenshot the process for this SO question
Every time it's exactly 26 rows, no more, no less. But which was destroyed, the master, or the aprentice?
But just to prove that i'm not doing anything wrong, we'll try again with Enterprise Manager. An excellent tool written over ten years ago:
Proof - Import using Enterprise Manager
i've deleted the 26 rows from the STRTransactions table. i could provide a screenshot that i'm starting with an empty table; or you could just trust me on this. And since the wizards are nearly identical, you'll be seeing nearly identical screenshots. Sorry about that; but nobody will believe me without proof.
i want to export from the ContosoFrobManager database on lithium:
i want to import to the Grob database on lithium:
i want to copy tables:
i want to copy the STR Transactions table:
You can go ahead and append rows to the existing table (it's empty anyway). i want you to insert identity values. And don't try to insert timestamp values, you'll just throw an error anyway:
Run now, sure:
Yup, you're about to do stuff:
i managed to catch it in the middle of the import, around 12k rows:
All done, 49,325 rows successfully copied:
And we select from the table to see the rows:
Why is SSMS, a tool which has been actively developed for 6 years now still not gotten in right? While Enterprise Manager was nearly bug-free by the initial dev team? This is another example of the critical bugs in SSMS. The last critical bug I found was that it does not script all objects.
I suspect the only answers I'll see are
- use a different tool
- have you reported it on Microsoft Connect?
- call PSS
Of course I have a workaround: stop using SQL Server Management Studio. But I'm already having to run Enterprise Manager in an XP Mode 32-bit compatibility thing (as you can see by the Luna themed windows on my Aero themed desktop)
Me: I got it to workColleague: How?Me: I used Enterprise ManagerColleague: scoff Well.Colleague: I might have to install that, then.
I created a ticket on Microsoft Connect, but it was closed after several weeks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
答案是：
我同意最新的微软产品中确实存在一些垃圾。在 SSRS 中，当您单击现有文本中间的文本框并点击粘贴时，粘贴操作后光标位于所有文本的末尾，而不是粘贴文本的末尾（中间）。 SSRS 和 SSIS 充斥着诸如此类的废话。
The answer:
I agree there is some real junk in the latest Microsoft products. In SSRS when you click into a text box in the middle of existing text and hit paste, after the paste operation the cursor is at the end of all the text instead of at the end of the pasted text (in the middle). SSRS and SSIS are just rife with all sorts of nonsense like this.
我有几个这样的问题。那个巫师绝对不可靠。如果这是一项一次性任务，我将导出到 csv，然后从 csv 导入。如果您需要定期运行它，请编写自己的代码。我们最近进行了类似的讨论：程序员是否应该使用 SSIS，如果是，为什么？
I had several such issues. That wizard is absolutely not reliable. If this is a one time task, I would export into csv, and then import from csv. If you need to run it regularly, write your own code.We recently had a similar discussion: Should programmers use SSIS, and if so, why?
我只有两种方法可以让 SSIS 正确执行任何操作。
首先是将数据放入一个全新的表中。
第二种方法是先将其导入 MS Access，然后从那里导入 sql server。
坦白说，自从他们取消了 DTS 以来我一直很不高兴。至少 DTS 工作始终如一。
请参阅 我的 Microsoft Connect 错误。
https://connect.microsoft.com/SQLServer/feedback/details/386948/simple-ssis-import-from-excel-2007-consistly-fails-with-useless-error-messages
他们声称这些问题将在下一个 Sql Server 版本中得到解决。坦率地说，除非他们解雇了负责此功能的整个团队，否则我不相信他们。
There are ONLY two ways that I've been able to get SSIS to do anything correctly.
The first is to bring the data into a brand new table.
The second is to import it into MS Access first then import it from there into sql server.
Quite frankly I have been unhappy since they got rid of DTS. At least DTS worked consistently.
See my Microsoft Connect bug on this.
https://connect.microsoft.com/SQLServer/feedback/details/386948/simple-ssis-import-from-excel-2007-consistently-fails-with-useless-error-messages
They claim the issues will be resolved in the next Sql Server release. Quite frankly, unless they fired the entire team responsible for this feature, I don't believe them.
看来同样的问题刚刚发生在我身上。无法追踪真正的问题。
我不知道这是否对您有帮助，PK 和身份插入可能会出现问题。
引用自链接：
“在某些情况下也会忽略启用标识插入”
“导出向导“跳过”应导出的记录”
“启用“针对多个表进行优化”时会忽略启用标识插入。不幸的是，该选项确保导入操作遵守引用外键连接表之间的完整性”
http://connect.microsoft.com/SQLServer/feedback/details/135905/mappings-settings-not-working-in-export-data-wizard
非常奇怪和烦人。
这些边缘情况是吗？只是因为 SSIS 本身用于安静的大型项目/数据库，而我以前从未遇到过这种情况。
It seems the same problem just occured to me. Unable to track down the real issue.
I don't know if this helps you, it is possible to have problems with PKs and identity inserts.
Quoted from link:
""Enable identity insert" also ignored in certain circumstances"
"export wizard "skipping" records that should be exported"
"Enable identity insert is ignored when "optimize for multiple tables" is enabled. Unfortunately that option ensures that the import operation observes referential integrity between foreign key connected tables"
http://connect.microsoft.com/SQLServer/feedback/details/135905/mappings-settings-not-working-in-export-data-wizard
Very weird and annoying.
Are these edge cases - just because SSIS itself is used in quiet big projects/DBs, and I've never encountered this before.
我遇到了类似的问题，仅从 540K 行传输了 291 行，但导入向导显示成功（我从 SQL Server 2008 转到 2005）。
顺便说一句：
如果我正确理解您的屏幕截图，那么当 SSIS 导入向导失败时，您还在 SSIS 导入向导中使用了 SQL Server Native Client 10.0，但您却使用了 Microsoft OLE DB Provider for SQL Server > 当使用企业管理器时它起作用了。
我过去在使用 SQL Server Native Client 时没有遇到任何问题，因此我怀疑在我的情况下，由于将数据从 SQL Server 2008 复制到 2005，因此需要更改为 Microsoft OLE DB Provider for SQL Server
。屏幕截图和你的问题确实对我有帮助 - 所以谢谢:)
I had a similar issue, only 291 rows transferred from 540K rows, yet the import wizard was showing success (I was going from SQL Server 2008 to 2005).
As an aside:
If I understand your screen shots correctly, you were also using SQL Server Native Client 10.0 within SSIS import wizard when it failed, yet you were using to Microsoft OLE DB Provider for SQL Server when using Enterprise Manager when it worked.
I have not had any issues using SQL Server Native Client in the past, so I suspect that changing to Microsoft OLE DB Provider for SQL Server was required in my case due to copying data from SQL Server 2008 to 2005.
You did a great job on the screen shots and your question really helped me - so thanks :)