In this article, I will give an overview of SSIS OLE DB Destination and SQL Server Destination and I will try to illustrate some of the difference between both destination components based on my personal experience, SSIS official documentation and some other experts experience in this domain.
在本文中，我将概述SSIS OLE DB目标和SQL Server目标，并根据我的个人经验，SSIS官方文档和该领域的其他一些专家的经验，尝试说明这两个目标组件之间的某些区别。
This article is the third article in the SSIS feature face to face series which aim to remove any confusion and to illustrate some of the differences between similar features provided by SQL Server Integration Services.
本文是SSIS面对面功能系列中的第三篇，旨在消除任何混淆并说明SQL Server Integration Services提供的相似功能之间的一些区别。
OLE DB目标 (OLE DB Destination)
The OLE DB Destination is used in order to load data into a destination supported by an OLE DB provider such as SQL Server, SQLite, Microsoft Access database and others. The destination connection configuration must be done within an OLE DB connection manager and it can be located on a local or remote server.
OLE DB目标用于将数据加载到OLE DB提供程序支持的目标中，例如SQL Server，SQLite，Microsoft Access数据库和其他。 目标连接配置必须在OLE DB连接管理器中完成，并且可以位于本地或远程服务器上。
OLE DB Destination provides many data access modes to load data into the destination, each one of these data access modes has its own configuration and available options:
OLE DB目标提供了许多数据访问模式以将数据加载到目标中，这些数据访问模式中的每一种都有其自己的配置和可用选项：
- Table or View: select an existing table or view / create a table 表格或视图：选择现有表格或查看/创建表格
- Table or View – fast load: select an existing table or view / create a table – using fast load options 表或视图–快速加载：使用快速加载选项选择现有表或视图/创建表
- Table name or View name variable: select a variable that contains a table or view name
- 表名或视图名变量：选择包含表名或视图名的变量
- Table name or View name variable – fast load: select a variable that contains a table or view name – using fast load options 表名称或视图名称变量–快速加载：使用快速加载选项选择包含表或视图名称的变量
- SQL Command: use the result of a SQL Statement to specify the destination metadata. This option can be used in two cases: SQL命令：使用SQL语句的结果指定目标元数据。 此选项可以在两种情况下使用：
- If the destination table contains too many columns and you need to select only some specific one 如果目标表包含太多列，而您只需要选择一些特定的列
- The destination is composed of many tables/views 目标由许多表/视图组成
Figure 1 – OLE DB Destination editor
图1 – OLE DB目标编辑器
快速加载选项 (Fast load options)
When selecting a data access mode with fast load, data are loaded into the destination using a BULK INSERT operation. In addition, there are many options that appear in the OLE DB destination editor:
当选择快速加载的数据访问模式时，数据将通过批量插入操作被加载到目标中。 此外，OLE DB目标编辑器中还显示许多选项：
- Keep Identity: 保持身份： Similar to SET IDENTITY_INSERT ON in SQL 类似于SQL中的SET IDENTITY_INSERT ON
- Table Lock: Lock the table while data is loaded (This will increase the performance) 表格锁定：在加载数据时锁定表格（这将提高性能）
- Keep nulls: Specify whether NULL values are copied while data is loaded 保留空值：指定在加载数据时是否复制NULL值
- Check constraints: Specify whether constraints are disabled/enabled while loading data. 检查约束：指定在加载数据时是否禁用/启用约束。 Similar to CHECK /NOCHECK CONSSTRAINT in SQL 类似于SQL中的CHECK / NOCHECK CONSSTRAINT
- Rows per batch: Specify the rows count inserted in every batch. Similar to ROWS_PER_BATCH argument in BULK INSERT method in SQL
- 每批次的行数：指定每个批次中插入的行数。 类似于SQL中BULK INSERT方法中的ROWS_PER_BATCH参数
- Maximum insert commit size: Specify the maximum number of rows allowed for each insert transaction. Similar to the BATCHSIZE argument in BULK INSERT method in SQL. The more this value is bigger values are inserted in one transaction, the if an error occurs all rows are rolled back. Else, if the commit size is smaller than the number of rows in the data flow then if an error occurs, all committed data will persist in the database and are not rolled back 最大插入提交大小：指定每个插入事务允许的最大行数。 与SQL中BULK INSERT方法中的BATCHSIZE参数相似。 此值越大，在一个事务中插入的值越大，如果发生错误，则所有行都将回滚。 否则，如果提交大小小于数据流中的行数，那么如果发生错误，则所有提交的数据将保留在数据库中，并且不会回滚
In general, it is not recommended to adjust Rows per batch and Maximum commit size values. But if you don’t have much resources for the data load operation, then you should adjust these values to obtain better performance.
通常，不建议调整每批行数和最大提交大小值。 但是，如果没有足够的资源用于数据加载操作，则应调整这些值以获得更好的性能。
When using fast load, rows are inserted in batches while in normal Table or View / SQL command options data are inserted Row-by-Row. Which means when using fast load option, if an error occurs when trying to insert data into the destination (error thrown by the database engine, not SSIS OLE DB Destination) then you will not be able to catch or redirect the specific row that caused the issue since the entire batch is redirected. You can refer to the following link for additional information: Fast load error output doesn’t redirect entire batch.
使用快速加载时，将批量插入行，而在普通Table或View / SQL命令选项数据中则逐行插入数据。 这意味着使用快速加载选项时，如果尝试将数据插入目标位置时发生错误（数据库引擎而非SSIS OLE DB目标位置抛出错误），则您将无法捕获或重定向导致问题，因为整个批次都被重定向了。 您可以参考以下链接以获取更多信息： 快速加载错误输出不会重定向整个批次 。
From a performance perspective, the fast load is highly recommended, but in case you need to perform a row-by-row insertion or you need a higher level of error handling you don’t have to use this option.
从性能的角度来看，强烈建议快速加载，但是如果您需要执行逐行插入或需要更高级别的错误处理，则不必使用此选项。
SQL Server目标 (SQL Server Destination)
SQL Server destination is used to load data into a local SQL Server database. It bulk loads the data into tables or views. This component cannot be used for SQL Server located on the remote server. Also, it reads the connection configuration from an OLE DB connection manager.
SQL Server目标用于将数据加载到本地SQL Server数据库中。 它将数据批量加载到表或视图中。 该组件不能用于远程服务器上SQL Server。 此外，它从OLE DB连接管理器读取连接配置。
Figure 2 – Destination editor
图2 –目标编辑器
There are many options that can be configured in the destination editor such as:
在目标编辑器中可以配置许多选项，例如：
- Keep identity 保持身份
- Keep Nulls 保持零
- Check Constraints 检查约束
- Table Lock 餐桌锁
- Fire triggers: Specify whether to execute the insert triggers defined on the destination table during the bulk load operation 触发触发器：指定在批量加载操作期间是否执行在目标表上定义的插入触发器
- First Row: Specify the number of the first row in the input to load during the bulk insert operation 第一行：指定在批量插入操作期间要加载的输入中第一行的编号
- Last Row: Specify the number of the last row in the input to load during the bulk insert operation
- 最后一行：指定在批量插入操作期间要加载的输入中最后一行的编号
- Maximum number of errors: Each row that cannot be imported is counted as one error 最大错误数：无法导入的每一行都计为一个错误
- Timeout: Bulk insert query timeout 超时：批量插入查询超时
- Order Columns: specify columns that contain sorted data 订单列：指定包含排序数据的列
Figure 3 – Destination Bulk insert options
图3 –目标大容量插入选项
OLE DB目标与SQL Server目标 (OLE DB Destination Vs SQL Server Destination)
In this section, first I will try to illustrate the difference based on the official documentation (in my opinion this is the theoretical point of view). Then I will try to mention some of the SSIS experts’ opinion. Finally, I will mention my own experience with both components.
在本节中，首先，我将尝试根据官方文档说明差异（我认为这是理论观点）。 然后，我将尝试提及SSIS专家的一些观点。 最后，我将提到我自己对这两个组件的经验。
官方文件 (Official documentation)
In the official documentation, they mentioned that for loading data into SQL Server, we should consider using the SQL Server destination instead of the OLE DB destination.
他们在官方文档中提到，要将数据加载到SQL Server中，我们应该考虑使用SQL Server目标而不是OLE DB目标。
On the other hand, when you click on the SQL Server Destination in the SSIS toolbox, in the component description they mentioned that: “To optimize performance, we recommend using OLE DB Destination instead”. This is because OLE DB Destination enables modifying Rows per batch and Maximum insert commit size properties.
另一方面，当您单击SSIS工具箱中SQL Server目标时，在组件描述中他们提到： “为了优化性能，我们建议改用OLE DB目标” 。 这是因为OLE DB目标允许修改每批行数和最大插入提交大小属性。
Figure 4 – Description from SSIS toolbox
图4 – SSIS工具箱中的描述
Based on the Data Loading performance guide, SQL Server destination guarantees a greater performance than OLE DB Destination. The main difference is that the first component used Shared memory protocol while the second uses TCP/IP and named pipes, which is the main reason that SQL Server Destination requires that SSIS is running on the destination server.
根据数据加载性能指南 ，SQL Server目标保证了比OLE DB目标更高的性能。 主要区别在于第一个组件使用共享内存协议，而第二个组件使用TCP / IP和命名管道，这是SQL Server Destination要求SSIS在目标服务器上运行的主要原因。
Also, SQL Server Destination requires that Users who execute packages must have “Create global objects” permission from the Local security policy which is not required in OLE DB Destination.
此外，SQL Server目标要求执行程序包的用户必须具有本地安全策略中的“创建全局对象”权限，而OLE DB目标中则不需要。
SSIS专家经验 (SSIS Experts experience)
In this section, I will mention some of the expert’s experience.
在本节中，我将提到一些专家的经验。
唐纳德·法默 (Donald Farmer )
(Former Group Program Manager for Integration Services)
（Integration Services的前组程序经理）
Donald mentioned that you can get a 5 to 10% increase in performance using SQL Server Destination. Unfortunately, the blog post isn’t available anymore, but I found it mentioned in the following MSDN topic.
Donald提到使用SQL Server Destination可以使性能提高5％到10％。 不幸的是，该博客文章不再可用，但我发现它在以下MSDN主题中提到。
马特·梅森 (Matt Mason)
(Date Integration Specialist at Microsoft)
（Microsoft的日期集成专家）
From the following post, Matt recommended using OLE DB Destination even if it is 10% slower on a 10 hours data load since it has many limitations such as: Hard and complicated debug process, Additional permission required and the package must be executed one the destination server.
从以下文章中 ，Matt建议使用OLE DB Destination，即使它在10个小时的数据加载时速度要慢10％，因为它有许多限制，例如：困难而复杂的调试过程，需要附加权限，并且必须在目标位置执行一个程序包服务器。
比尔研究员 (Bill Fellows)
(Microsoft Data Platform MVP and Top SSIS contributor at Stackoverflow.com)
（ 位于Stackoverflow.com的 Microsoft数据平台MVP和 SSIS杰出贡献者 ）
Bill doesn’t recommend using SQL Server Destination since the performance benefit does not outweigh the restriction that the package must be executed on the same machine as the destination database also because it keeps throwing meaningless errors that may disappear after restarting Visual Studio.
Bill不建议使用SQL Server Destination，因为性能优势并未超出必须与目标数据库在同一台计算机上执行程序包的限制，因为它不断抛出无意义的错误，这些错误可能会在重新启动Visual Studio之后消失。
In addition, Bill mentioned many other reasons that are mentioned in the following posts at Stack Exchange websites:
此外，Bill提到了Stack Exchange网站上以下帖子中提到的许多其他原因：
- Stackoveflow.com question about both destination components 关于两个目标组件的Stackoveflow.com问题
- Database Administrators question – should SSIS packages and SQL database be on same server? 数据库管理员的问题– SSIS软件包和SQL数据库是否应该在同一服务器上？
我自己的经验 (My own Experience)
After years of working with SSIS, I do not recommend at all using SQL Server Destination due to the following reasons:
经过多年的SSIS工作，由于以下原因，我不建议使用SQL Server Destination：
- It keeps throwing meaningless errors that that can waste your time 它不断抛出无意义的错误，这可能会浪费您的时间
- After contributing to the SSIS community for 3 years, I can say that OLE DB Destination is widely used, and there are many developers in the community that can help you more 在为SSIS社区贡献3年之后，我可以说OLE DB Destination被广泛使用，并且社区中有很多开发人员可以为您提供更多帮助
- OLE DB Destination has better error handling since SQL Server Destination doesn’t support an error output OLE DB目标具有更好的错误处理，因为SQL Server目标不支持错误输出
- SQL Server Destination requires that ETL server and a destination server are the same (not always preferred) SQL Server目标要求ETL服务器和目标服务器相同（并非总是首选）
- OLE DB Destination allows performance optimization by calibrating batch and max commit size OLE DB目标允许通过校准批处理和最大提交大小来优化性能
- OLE DB Destination allows developers to choose from several data access modes (BULK INSERT is not always needed) OLE DB Destination允许开发人员从几种数据访问模式中进行选择（并非总是需要BULK INSERT）
- OLE DB Destination fast load option performance is very close to SQL Server Destination performance especially when handling small and medium data size (tested it on 200 GB and difference is less than 5 seconds) OLE DB目标快速加载选项的性能非常接近SQL Server目标的性能，尤其是在处理中小型数据大小时（在200 GB上进行了测试，差异小于5秒）
结论 (Conclusion)
To sum up, all that we mentioned above, SQL Server Destination may improve the data load performance over large data size but it has many restrictions. OLE DB Destination is more generic and widely used and by using fast load data access mode, it increases the data loads performance and it is almost the same than SQL Server Destination on small and medium data size. In addition, many SSIS experts recommend using OLE DB Destination based on their experience.
综上所述，我们上面提到的所有内容，SQL Server Destination都可以在较大的数据大小上提高数据加载性能，但是它有很多限制。 OLE DB Destination更为通用且被广泛使用，并且通过使用快速加载数据访问模式，它提高了数据加载性能，并且在中小型数据大小上与SQL Server Destination几乎相同。 此外，许多SSIS专家建议根据他们的经验使用OLE DB Destination。
有用的网址 (Helpful Links)
- Is there any relevance for “ROW PER BATCH” AND “MAX INSERT COMMIT SIZE” IN SSIS PACKAGES? SSIS软件包中的“ ROW PER BATCH”和“ MAX INSERT COMMIT SIZE”是否相关？
- Using SQL command in OLE DB Destination 在OLE DB目标中使用SQL命令
目录 (Table of contents)
翻译自: https://www.sqlshack.com/ssis-ole-db-destination-vs-sql-server-destination/
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
