MSSQL - Native OLE DB in SSIS

Here is the difference between the 2 OLE DB for Oracle:

1.       Microsoft OLE DB Provider for Oracle : this one does a translation from UNICODE (NVARCHAR) to NON-UNICODE (VARCHAR) however is slower

2.       Oracle Provider for OLE DB : this one you have to do a manual conversion from NVARCHAR to VARCHAR but is twice as fast

Also, in general OLEDB is faster than ADO.Net.  Use OLEDB whenever it is possible.

Another note: when you need change the “OLE DB Connection Manger” type (eg from Microsoft OLE DB to Oracle OLE DB) you will have to delete the old one and build a new one because the internal table schema (data type) doesn’t get repopulated and will cause an conversion error.