Can anyone say the Difference between ADO NET source and OLE DB Source in SSIS 2008 ?are they both same in any context ?
First of all, it is good to know that ADO.NET used OLE DB providers to access data while OLE DB uses ODBC to access relational databases. After giving an overview of these three SSIS connection managers, I will try to illustrate some of the differences between them from an SSIS development perspective.
ADO.NET Connection Managers NET provider. A data reader source can correctly interpret an LDAP query, allowing you to access LDAP services in the data flow. 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.
The ADO NET source consumes data from a . NET provider and makes the data available to the data flow. You can use the ADO NET source to connect to Microsoft Azure SQL Database. Connecting to SQL Database by using OLE DB is not supported.
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)
With small datasets there is little difference between ADO NET source and OLE DB Source in SSIS 2008. The difference between them is how they communicate with their underlying data sources. OLEDB talks directly to OLEDB compliant sources, but ADO .NET source talks through a .NET provider. The only reason you should use the ADO.NET source is:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With