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 differences between both destination components based on my personal experience, SSIS official documentation and some other experts experience in this domain.
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.
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 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:
- 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 case the destination table contains too many columns and you need to select only some specific one
Figure 1 – OLE DB Destination editor
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:
- Keep Identity: Similar to SET IDENTITY_INSERT ON in SQL
- 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
- Check constraints: Specify whether constraints are disabled/enabled while loading data. Similar to CHECK /NOCHECK CONSSTRAINT in SQL
- Rows per batch: Specify the rows count inserted in every batch. Similar to ROWS_PER_BATCH argument in BULK INSERT method in SQL
- 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
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.
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 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.
Figure 2 – Destination editor
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
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.
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.
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.
Figure 4 – Description from SSIS toolbox
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.
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.
SSIS Experts experience
In this section, I will mention some of the expert’s experience.
Donald Farmer
(Former Group Program Manager for 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.
Matt Mason
(Date Integration Specialist at 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.
Bill Fellows
(Microsoft Data Platform MVP and Top SSIS contributor at Stackoverflow.com)
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.
In addition, Bill mentioned many other reasons that are mentioned in the following posts at Stack Exchange websites:
- Stackoveflow.com question about both destination components
- Database Administrators question – should SSIS packages and SQL database be on same server?
My own Experience
After years of working with SSIS, I do not recommend at all using SQL Server Destination due to the following reasons:
- 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
- OLE DB Destination has better error handling since SQL Server Destination doesn’t support an error output
- SQL Server Destination requires that ETL server and a destination server are the same (not always preferred)
- OLE DB Destination allows performance optimization by calibrating batch and max commit size
- OLE DB Destination allows developers to choose from several data access modes (BULK INSERT is not always needed)
- 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)
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.
Helpful Links
- Is there any relevance for “ROW PER BATCH” AND “MAX INSERT COMMIT SIZE” IN SSIS PACKAGES?
- Using SQL command in OLE DB Destination
Table of contents
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023