ssis ado.net/oledb performance against oracle makes no sense

Question

I am using vs 2017 enterprise for development and an internal Azure run time environment to run the ssis packages. I am reading a ~1.5 million row oracle table and writing into an MS Sql DAN database. Can anyone explain the performance characteristics I describe below? It appears that the OLEDB Oracle driver is very efficient but then it runs terribly with the OLEDB SSIS task. Here are the run time characteristics:

• ADO.net Task with Oracle Ado.net driver: ms sql table takes ~32 minutes to load
• ADO.net Task with an Oracle OLEDB driver: ms sql table takes ~5 minutes to load (the winner)
• OLEDB Task with an Oracle OLEDB driver: ms sql table take ~20 minutes to load

question from:https://stackoverflow.com/questions/65911287/ssis-ado-net-oledb-performance-against-oracle-makes-no-sense

Answer 1

Ok, I think I figured this out. I had been just taking the MS Sql write side for granted but, in my environment, there are two drivers you can specify in an OLEDB Destination Task for MS Sql, SQLOLEDB.1 and SQLNCLI11.1 . The job that writes in 5 minutes is using SQLOLEDB.1 to write to MS Sql. The 3rd job that takes ~20 minutes is using SQLNCLI11.1 . Ironically SQLOLEDB.1 is older and many organizations are deprecating it even though it is much faster. I will be able to prove my suspicions soon and will report the final result.