I think this is a question you could find on every .NET related forum. But I'm going to throw it: Which connection type should I use? ADO .NET or OLE DB I know in the past ADO was just a layer on top of OLE DB so it was better to use OLE DB directly. Nowadays, with ADO .NET, that isn't the case anymore or am I totally wrong? The reason why I'm asking this is because I'm considering to switch from OLE DB connection to ADO .NET because I have nothing but troubles with parameter mappings and wrong values inserted in the database because of data type conversions. When I use ADO.NET, everything works fine. What about performance? A fable or the truth? In my testing environment I work with small datasets but in production this won't be the case. Is there a good article somewhere out there on this subject? Thanks in advance,Tom

Tom, I'm currently in a training session for SSIS, and this very question has be asked by one of our Senior Database developers. I is inclined to use ADO.Net because it is explicit with the named variables. Our instructor then pointed out that if the variable name changes in the stored procedure then you break your package. He also suggested that if you abstract your variables to a table, then you could avoid that issue. My question is the performance between the two. Is ADO.Net just a wrapper for OLE DB? Or is it an independent environment? Brandon Forest Database Administrator Data & Web Services Sutter Connect IT foresb@sutterhealth.org

Hi Brandon, I've read a lot about this and nobody seems to know the answer. Performance isn't the issue apparently. Allthough Microsoft pushes developers towards ADO.NET (code examples on MSDN usually use ADO.NET),I for myself am tended to use OLE DB, this because of the simple factthey seem to have forgotten about ADO.NET in SSIS designer (OLE DB Source component - but nothing like ADO.NET Source Component) Greets, Tom

[Microsoft follow-up]

There isn't a single answer that's correct in all cases. As others have pointed out, there are differences between ADO.Net and OLE DB that lead to slightly different strengths and weaknesses, but performanceis muchmore stronglydetermined by the provider than the interface. I.e., two different OLE DB or two different ADO.Net providers for the same data source can demonstrate significantly different performance in only slightly different scenarios. So, if you really want a meaningful answer, you have to ask, "Should I be using OLE DB or ADO.Net in this particular scenario?" The best way to answer that question is with some prototyping.

ADO.NET vs OLEDB: I'll try to answer this question by slicing it into4 main areas: 1. support across SSIS components: a. SSIS is not welcoming ADO.NET and OLE DB connectors equally across the board. Although this is more so in SQL Server 2005, we are making steps to close the gap in Katmai in between the two. For instance, there is no ADO.NET destination component in Data Flow, and the Lookup component only supports OLE DB. OLE DB is by far the most supported connectivity option across the board. For a full list of supported connectivity options and SSIS components, see my article on the subject: http://ssis.wik.is/Connectivity_Libraries 2. performance a. Performance is very much dependent on the computing environment and the data and network load levels in your scenario. We highly recommend slicing your end-end scenario into isolated sub scenarios to see where the performance bottleneck is. RowCount component comes particularly handy where you can replace source and destination components with. By replacing the connectors with the RowCount component, which does not incur any I/O and frees memory as an inline component, you are indeed collecting benchmark performance numbers with and without the connector. This will give you the time the connector spends reading/writing the data. b. That being said, OLE DB, for most cases will have better performance than ADO.NET. This is due to ADO.NET being a managed faade, and providing more abstraction with a little more performance overhead. 3. 64bit considerations a. Since ADO.NET is managed, the deployment of packages and moving them around 32bit and 64bit boxes will be easier compared to the experience with OLE DB providers. SSIS design time is a 32bit application because of the dependency on Visual Studio, and has to work with 32bit connectors. However, during runtime, you have the option to choose a native 64bit or a 32bit connector. Please read my article on 64bit and why things are the way they are here: http://ssis.wik.is/64-bit_Story b. Some OLE DB providers are 32bit only (i.e. Office connectivity, JET and ACE providers), whereas others support both architectures (i.e. Oracles own OLE DB provider for Oracle). To work in a seamless 64bit environment, make sure your provider has both binaries. c. For an OLE DB provider to be available only in 32bit (i.e. JET), does not necessarily mean that you cannot run it on 64bit machines. You can, but it will be in WOW64 emulation mode. The only downside to that is if you need the 64bit address space of huge memory (i.e. greater than 4GB). Otherwise, its not a problem. 4. target data source & data type supportability a. Connectors, whether they are ADO.NET or OLE DB providers, dont support a specific target database/application system equally. For instance, the OLE DB providers for DB2 are all different in the way they support different flavors of DB2. Some support DB2 on Linux, others support DB2 on AS/400, etcFor a good list of connectors and what actually they support, please see the table we are compiling in our connectivity wiki : http://ssis.wik.is/Data_Sources b. Another key support question is whether these connectors support a specific version of the target database and how good they are in dealing with a recently introduced data type. For instance, Microsofts Oracle OLE DB provider was not recently updated to fully support new data types in Oracle 10g +However, Microsofts ADO.NET provider for Oracle does a better job with Oracle data types. Again, our connectivity wiki is a good place to start and contribute to this very organic structure of the connectivity realm. We also have a white paper on connectivity which covers other aspects of this comparison, its a good read: http://ssis.wik.is/Connectivity_White_Paper Hope this helps, Deniz

Thanks Deniz.Phil

Many thanks to all of you -Tom

Hi Deniz, Your wiki site is decommissioned. Have you migrated it to another site? Thanks Jason