March 6, 2012 at 4:25 am
Hi All,
Can anybody give me the differences of the connection managers in SSIS (ADO.NET and OLEDB)
Thanks in advance!
March 6, 2012 at 6:30 am
My Understanding is we will be using OLEDB to connect across Different Databases.
i.e ( Will be using to connect( Linked Server) From SQL Server database to another Oracle Database).
March 8, 2012 at 2:03 pm
They are just different providers. ADO.NET is the ActiveX Data Object for .NET, OLE DB is implemented using the COM model.
Important points are:
* OLE DB is most of the times a bit faster than ADO.NET (certainly if you use it in the OLE DB Destination with the Fast Load option)
* you can use ADO.NET in a For Each loop to loop over a recordset
* Lookup components only support OLE DB
* if I'm not mistaken you can use ODBC over an ADO.NET connection
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 14, 2015 at 9:50 pm
OLE DB Connection Managers
An OLE DB connection manager enables a package to connect to a data source by using an OLE DB provider.
Fails unless the variable is of type object.
The OLE DB Source cannot parse the query, so you have to use an Execute SQL and pass the value into the dataflow in a variable.
Faster in some scenarios
parameter mapping is not very clear and when large numbers of parameters need to be mapped it becomes difficult to track
ADO.NET Connection Managers
An ADO.NET connection manager enables a package to access data sources by using a .NET provider.
let’s you return varchar(MAX) columns into variables of type string from the Execute SQL task
A data reader source can correctly interpret an LDAP query, allowing you to access LDAP services in the data flow.
A little slow compared
Mapping Parameters in Execute SQL Task is very convenient and clear
January 28, 2015 at 1:59 am
Adodb works with MS-based databases such as Sql Server.
Oledb is a standard format supported by a large number of dbs, so you can connect to oracle, db2 etc. using Oledb.
You can also use oledb to connect to Sql Server but the performance is lower as compared to a adodb connection which is optimized to work with Sql Server and MS Access.
January 28, 2015 at 2:14 am
anjalipunjabb (1/28/2015)
You can also use oledb to connect to Sql Server but the performance is lower as compared to a adodb connection which is optimized to work with Sql Server and MS Access.
Any resources to back this claim?
I have used OLE DB and ADO.NET for years and I haven't noticed any performance difference.
In fact, OLE DB with fast load is usually the fastest.
ADO.NET can also work with other sources than SQL Server or MS Access. It can use ODBC to connect to any possible source for example.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply