为什么 SSIS 导入 8500 条记录所需的时间比通过 SSMS 手动导入要长得多？

Why does SSIS take Significantly Longer to Import 8500 Records than Manually Importing via SSMS?

提问人：Dizzy49 提问时间：11/3/2023 最后编辑：Dizzy49 更新时间：11/3/2023 访问量：65

问：

我有一个有 8 张纸的 Excel 文件。它们都不是特别大，整个文件小于2MB。我尝试导入的工作表有 8500 条记录，大约有 70 列。 如果我在 SSMS 中使用 Task->Import Data from the Database，则导入大约需要 1 分钟。 我创建了一个 SSIS 包，该包刷新了一堆表/文件，并重新创建了我尝试导入的工作表。然后，它创建表（无索引），然后我有一个导入数据的数据流任务。当我在 SSIS 中运行导入任务时，它花了 10 分钟才能完成。

有几件事需要注意。 我已经使用这个软件包大约一年了。我在连接管理器上遇到了各种问题，但我已经能够让它再次启动并运行，并且导入记录需要 10 秒（打开 Excel 文件可能需要 8 秒，导入需要 2 秒）。 在此之前，我的连接管理器再次出现问题。我收到错误，我删除了连接管理器并使用 Excel 2016（我使用 Office 365）重新创建，但它仍然不起作用。我再次尝试使用Excel 2007-2010，它可以工作，但它很慢。AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009

为了更好地衡量，我检查以确保设置为我的 CPU 从未达到两位数，我的内存是 20-26%。Run64bitRuntimeFALSE

我发现的其他问题已经很老了，大多数都在尝试远程运行。SQL Server 是 Azure 云，但其他一切都在我的本地 PC 上。

SQL Server 12.0.2 and Visual Studio 2019 (w/ SSIS and SSDT)

Destination Setup

SQL-Server Excel SSIS

评论

0赞 billinkc 11/3/2023
在数据流中，如何配置目标组件？在这种情况下，屏幕截图很有帮助
0赞 Dizzy49 11/3/2023
@billinkc我不知道为什么在读取 Excel 文件时会出现问题，但Microsoft谁知道呢。我添加了一个屏幕截图，这是您需要的吗？
0赞 Ozan Sen 11/3/2023
@Dizzy49 真的有必要把这70列都带进来吗？也许您应该取消选中某些列，以免将一些可疑列（大尺寸等）带入其中并再次测试包。
0赞 Panagiotis Kanavos 11/3/2023
@Dizzy49“导入数据”任务也使用 SSIS。您甚至可以保存生成的包并对其进行检查。检查生成的包与生成的包有什么不同
2赞 Panagiotis Kanavos 11/3/2023
正如答案所说，目的地很重要。这是配置影响导入操作的所有设置的位置。如果您阅读实际的错误消息，它会说 .该向导将检测源和目标之间的类型更改，并根据您的设置添加转换步骤。您自己的流没有转化"Invalid character value for cast specification".

答：

4赞 billinkc 11/3/2023 #1

加载速度的差异是由于选择了“数据访问模式”。通过 SSMS 生成的包使用“表或视图快速加载”，而你选择了“表或视图”

实现的区别在于 Table 或 View 基本上是单个插入语句，就像 Pez 分配器一样。如果您只想将一些数据添加到表中，表或视图适合您。

如果您有很多数据，那么“快速加载”选项可能是您想要的，因为您会更快地完成。快速加载批量发送数据，这是一个配置选项，并将该数据倒入目标表中。这种方法的缺点是，如果目标表非常忙于查询和更新许多其他进程，则快速加载可以阻止它们，直到它将其所有数据推送到目标。

当您使用 Excel 时，我怀疑即使您加载了一百万行，除非您的 SQL Server 资源严重不足，否则它锁定表的时间也将无关紧要。

正如 Panagiotis Kanavos 明智地指出的那样，性能变慢的另一个机会是数据类型转换失败。Excel 正在提取数据，很可能在每一列上都以 Unicode 格式。如果要将其放入定义良好的表中，则会进行从 DT_WSTR/Unicode 数据类型到非 That 的隐式转换。这不是 SSIS 包的本机功能。您需要使用从源数据类型到目标数据类型的显式转换。此处的选项是“派生列”（我的偏好）或“数据类型转换”组件。我更喜欢派生列，因为我发现我必须在本机转换工作之前对数据进行调整，因此，如果我必须对值进行模糊处理，我也可以处理转换。

评论

1赞 Panagiotis Kanavos 11/3/2023
该错误是类型转换错误。我怀疑延迟是由多个（如果不是全部）行上的转换错误引起的。
0赞 Dizzy49 11/4/2023
有趣。我手动导入了表格，几乎所有东西都是 varchar，有几个小数字段。我复制了表定义，并在运行 ssis 包时使用它重新创建表。可悲的是，我确实需要所有 70 列。我是这个过程的一部分，从 700 多个（是的，七百个）字段减少到我实际需要的 70 个。
0赞 Dizzy49 11/4/2023
我想当我试图让连接再次工作时，我更改了数据访问模式。我把它改回来了，看起来最大插入提交大小是“2147483647”。是不是太高了？如果 Excel 或 SSIS 能说出它对哪个字段/列有问题，那就太好了。可能是小数点，我发誓它们总是在Excel中被搞砸。当我组合所有内容时，我使用 Power Query 进行了相当多的转换。有些数字被存储为带有数字的字符串，所以 123.05 只是 12305，或者 1.2305 也存储为 12305，所以弄清楚所有这些很有趣。
0赞 billinkc 11/6/2023
Excel：一款出色的数据可视化工具。还有 Excel：最糟糕的数据传输格式。

上一个：使用 dtsx/SSIS 包导入后，SQL Server smalldatetime 列仍为 NULL

下一个：SSIS 基于其他列替换列值