NB: In SQL 2005 the ADO .NET Source is called the DataReader Source, and can only be edited through the Advanced Editor.
In terms of configuration options there is little difference between the ADO .NET source and the OLE DB source, with the exception that you cannot populate the ADO .NET source table/view name or SQL command from a variable. If you are looking for a guide to basic use of the component, please read this post on the OLE DB Source as the same rules apply.
The OLE DB source and ADO .NET source are so similar in fact, it is forgivable to ask why they are even separate components. The simple(ish) reason for this is that the two components talk to their underlying data sources in very different ways – the OLE DB source will talk directly to relational OLE DB compliant sources, but the ADO .NET source goes through a layer of abstraction so it can talk to the source through a .NET provider. If all that sounds like gibberish, don’t worry. You only need to consider using the ADO .NET source in two cases. Firstly if no OLE DB provider is available – e.g. if you need to talk to a SAP BI instance. Secondly if you need to access the source through a Script Task.
Accessing a Connection Manager through the Script Task
To access a relational source in a script task, you need to use an ADO .NET Connection Manager. Trying to use an OLE DB Connection will return an error, so if you need to read data from a SQL Server source into a script, you need to use the slower ADO .NET component.
The code itself is not overly complex, but if you are a non-coder like me, it’s pretty impenetrable. I’ve used Visual Basic, which I am assured by programmers is a clear sign I don’t know what I’m doing – so one day I will endeavour to pick up C#. Note that this is to access in a Script Task in the data flow – the code required for a Script Component is slightly different.
The code sample is below, slightly edited for clarity. It is lifted from the sample package for this post and simply counts the rows in a table and presents the results in a message box.
Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.Data.SqlClient Dim sqlConn As SqlClient.SqlConnection ' Object to hold connection Dim sqlCmd As SqlCommand ' SQL Command object Dim rdrReader As SqlDataReader ' Data reader to hold output of command Dim sqlCmdText As String ' SQL Command text Public Sub Main() ' Opening the connection manager for use - for more detail see MSDN at http://msdn.microsoft.com/en-us/library/ms136018.aspx sqlConn = DirectCast(Dts.Connections("ADO .NET AdventureWorks2008").AcquireConnection(Dts.Transaction), SqlClient.SqlConnection) ' Set the command text sqlCmdText = "SELECT COUNT(*) FROM [Person].[Address]" ' Execute the command and put the results into the data reader sqlCmd = New SqlCommand(sqlCmdText, sqlConn) rdrReader = sqlCmd.ExecuteReader() ' Take the data item in the reader and show it in a message box rdrReader.Read() MsgBox(rdrReader.Item(0), , "The [Person].[Address] table contains this many rows:") rdrReader.Close() Dts.TaskResult = ScriptResults.Success
Obviously you can do much cleverer things with the data once you have it on board, but this helps you overcome the initial hurdle of getting access to it.
ADO .NET vs OLE DB Connection Performance
To demonstrate the effect of pushing the data through the extra processing that .NET providers, I’ve also included in the sample package two data Flows that simply shift the contents of a large table to a Raw File using an OLE DB Source and a ADO .NET source. On my PC the OLE DB task took 2.3 seconds, but the ADO .NET version took 4.2. So the lesson here is clearly that if you need to access OLE DB sources and don’t need the added functionality provided by the ADO .NET provider, don’t use it.
The ADO .NET Source is very similar to the OLE DB source, but adds overhead when extracting data from OLE DB compliant sources so should only be used to access those sources when specifically required, e.g. when they need to be access in code. For non OLE DB compliant sources, such as ODBC, it adds a wide range of connection capabilities and extends the number of sources SSIS can work against.