SSIS - SQL Sever Destination vs OLE DB Destination

-

Question

I was using OLE Db destination for Bulk import of multiple Flat Files. After some tuning I ended up with SQL Server Destination to be 25 - 50 % faster.
Though I am confused about this destination as there are contradictory information on the web, some are against it, some are suggesting using it. I would like to know, are there any serious pitfalls before I deploy it to production? Thanks

Answer

In this answer, I will try to provide information from official documentation of SSIS and I will mention my personal experience with SQL Server destination.

1. SQL Server Destination

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 Destination

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 Destination vs SQL Server Destination

Donald Farmer, the former Group Program Manager for Integration Services said that you can get a 5 to 10% increase in performance using the SQL Server Destination.
In addition, refering to the following post of Matt Masson a data integration specialist at Microsoft where he answered the following question:
Should I use the SQL Server Destination?
The Answer was
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. The Data Loading Performance Guide

(Update @ 2019-03-25)
While searching on SSIS best practices i found a very helpful Microsoft artcile that can be used as a reference:
In this article they made a comparison between all data loads methods including SQL Server destination and OLEDB destination, they mentioned that:
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. Personal experience

(Update @ 2019-03-25)
Since this question is used as a reference by many, and after being more experienced in this domain, i added this section to mention my personal experience using SQL Server destination.
While official documentation mentioned that SQL Server destination will increase performance, i don't recommend at all using this components due to many reasons:
  1. It requires that destination server and the ETL server are the same (works only with Local SQL server)
  2. It always throw exception that don't have any meaning
  3. After testing on huge volume of data the performance difference with OLEDB destination is negligible (tested on about 500 GB data loaded in chunks and the time difference is less than one minute)
You can also refer to the following post (from @billinkc) to get more information about this topic:

4. Conclusion

Based on Microsoft articles, you can say that SQL Server Destination increase the performance of inserting data (it uses BULK insert), but it is designed for a specific case which is the Local SQL server. OLEDB Destination is more general and recommended in the other cases and by using the Fast Load data access mode (which uses also BULK insert) on the OLE DB destination it will increase the performance of data load.
On the other hand, based on my experience and from many articles written by SSIS experts, it is not recommended at all to use SQL Server Destination since it is not stable and it often throws exception and the performance can be considered as negligible.

Popular posts from this blog

SSIS - Script Task error: Exception has been thrown by the target of an invocation.

-
Question: When i execute the SSIS package the Script Task throws the following exception: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript() Which is meaningless. How can i read the real error message? Answer: Exception has been thrown by the target of an invocation. Is an general exception that is thrown by Script T
Read more

Don’t install Hadoop on Windows!

-
Image
A few years ago, I was hearing from my colleagues “don’t ever think about installing Hadoop on Windows operating system!” . I was not convinced of this saying because I am a big fan of Microsoft products, especially Windows. In the past three years, I worked on three projects where I was asked to build a Hadoop cluster on Ubuntu. The first time, it was a single-node Hadoop installation with a single Apache Spark worker. The other projects were about building a Big Data ecosystem for radiation data engineering , where the multi-node Hadoop cluster is deployed. Besides Hadoop, we installed and configured Apache Kafka, Spark, Hive, Pig, and Flume installations (I have published some installation guides previously, you can check the links at the end of this article). It was hard to become familiar with those technologies for the first time since they don’t have much documentation online. Each time I had to install Hadoop, I was thinking that why always Hadoop is installed on Linu
Read more

SSIS - script component DateTime Formats Implicit Conversion

-
General Info These are the default formats of datetimes datatypes (when converting from string) DT_DBDATE yyyy-mm-dd DT_FILETIME yyyy-mm-dd hh:mm:ss:fff DT_DBTIME hh:mm:ss DT_DBTIME2 hh:mm:ss[.fffffff] DT_DBTIMESTAMP yyyy-mm-dd hh:mm:ss[.fff] DT_DBTIMESTAMP2 yyyy-mm-dd hh:mm:ss[.fffffff] DT_DBTIMESTAMPOFFSET yyyy-mm-dd hh:mm:ss[.fffffff] [{+|-} hh:mm] Note: DT_DATE and DT_DBTIMESTAMP has the same SET method And i think that converting string to date is depending also on your current culture info More detailed information are found here https://msdn.microsoft.com/en-us/library/ms141036.aspx http://www.bidn.com/blogs/DevinKnight/ssis/1387/ssis-to-sql-server-data-type-translations Experiments : I didn't find any related article on this issue so i made the following experiments: SSIS Implicit datetime Conversion i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destin
Read more