sql-server - SQL Server 目标与 OLE DB 目标

Question

我使用 OLE Db 目标批量导入多个平面文件。经过一些调整后，我最终将 SQL Server Destination 的速度提高了 25 - 50%。

虽然我对这个目的地感到困惑，因为网上有相互矛盾的信息，有些人反对，有些人建议使用它。我想知道，在将其部署到生产环境之前是否存在任何严重的陷阱？谢谢

Answer 1

在这个回答中，我将尝试提供来自 SSIS 官方文档的信息，并提及我在 SQL Server 目标方面的个人经验。

1。 SQL Server 目标

根据official SQL Server Destination documentation :

The SQL Server destination connects to a local SQL Server database and bulk loads data into SQL Server tables and views. You cannot use the SQL Server destination in packages that access a SQL Server database on a remote server. Instead, the packages should use the OLE DB destination.

The SQL Server destination offers the same high-speed insertion of data into SQL Server that the Bulk Insert task provides; however, by using the SQL Server destination, a package can apply transformations to column data before the data is loaded into SQL Server.

For loading data into SQL Server, you should consider using the SQL Server destination instead of the OLE DB destination

2。 OLEDB 目标

根据official OLEDB Destination documentation :

OLEDB Destination - fast load option: Load data into a table or view in the OLE DB destination and use the fast load option, which are optimized for bulk inserts

3。 OLEDB 目标与 SQL Server 目标

根据 SQL Server Destination Vs OLE DB Destination - MSDN topic :

前集成服务组项目经理 Donald Farmer 表示，使用 SQL Server Destination 可以将性能提高 5% 到 10%。

另外，引用以下Matt Masson的帖子Microsoft 的数据集成专家，他回答了以下问题:

Should I use the SQL Server Destination?

答案是

No

...

My recommendation is that if you need every bit of performance (a 10% perf increase on a 10 hour load can be significant), try out the SQL Server Destination to see how it works for you. However – keep in mind the following limitations of the SQL Server Destination:

• You must have SSIS running on the same machine as the destination database
• You must run the package as an administrator
• It is very difficult to debug when things go wrong

Given these limitations, I recommend using the OLE DB Destination even if you are seeing a performance increase with the SQL Server Destination.

3.1。数据加载性能指南

(更新@2019-03-25)

在搜索 SSIS 最佳实践时，我发现了一篇非常有用的 Microsoft 文章，可以用作引用:

• The Data Loading Performance Guide

在这篇文章中，他们对包括 SQL Server 目标和 OLEDB 目标在内的所有数据加载方法进行了比较，他们提到:

SQL Server Destination The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH.

Be aware that this destination requires shared memory connections to SQL Server. This means that it can only be used when Integration Services is running on the same physical computer as SQL Server.

OLE DB Destination: The OLE DB destination supports all of the bulk load options for SQL Server. However, to support ordered bulk load, some additional configuration is required. For more information, see “Sorted Input Data”. To use the bulk API, you have to configure this destination for “fast load”.

The OLE DB destination can use both TCP/IP and named pipes connections to SQL Server. This means that the OLE DB destination, unlike the SQL Server destination, can be run on a computer other than the bulk load target. Because Integration Services packages that use the OLE DB destination do not need to run on the SQL Server computer itself, you can scale out the ETL flow with workhorse servers.

3.2。个人经历

(更新@2019-03-25)

由于这个问题被很多人用作引用，并且在这个领域有了更多的经验之后，我添加了这个部分来提及我使用 SQL Server 目标的个人经验。

虽然官方文档提到 SQL Server 目标将提高性能，但我完全不建议使用此组件，原因有很多:

1. 它要求目标服务器和 ETL 服务器相同(仅适用于本地 SQL 服务器)
2. 总是抛出没有任何意义的异常
3. 在对大量数据进行测试后，与 OLEDB 目标的性能差异可以忽略不计(测试了大约 500 GB 数据 block 加载，时间差异小于一分钟)

您还可以引用以下帖子(来自@billinkc)以获取有关此主题的更多信息:

• Should SSIS packages and SQL database be on same server?

---

4。结语

根据 Microsoft 文章，您可以说 SQL Server Destination 提高了插入数据的性能(它使用 BULK 插入)，但它是为特定情况设计的这是本地 SQL 服务器。 OLEDB Destination 更通用，建议在其他情况下使用 Fast Load 数据访问模式 (也使用 BULK 插入) OLE DB destination 它将提高数据加载的性能。

另一方面，根据我的经验和 SSIS 专家撰写的许多文章，根本不推荐使用 SQL Server Destination，因为它不稳定并且经常抛出异常和性能可以忽略不计。

---

附加信息

最近，我发表了一篇关于这个主题的详细文章。您可以在以下位置查看:

• SSIS OLE DB Destination Vs SQL Server Destination