ADO.NET or OLEDB Recordset ?

[CodeDigger]

My package needs to be a High Performance (target: 150,000 rows in 30 minutes) ETL solution. We are using all MS technologies - SSIS, SQL 2005, BIDS, etc. I need to loop the recordset executed by a Stored Proc in a Execute SQL Task in a Source Script Component.


If I use an ADO.NET Connection Manager, here is the code in the Source Script Component Public Overrides Sub CreateNewOutputRows()


Code 1
--------------
Dim sqlAdapter As New SqlDataAdapter
Dim dataRow As Data.DataRow
Dim ds As DataSet = CType(Me.Variables.rsSomeResultset, DataSet)
sqlAdapter.Fill(ds)



I get: Error: System.InvalidCastException: Unable to cast object of type 'System.Object' to type 'System.Data.DataSet'.



Code 2
--------------
Dim oledbAdapter As New OleDb.OleDbDataAdapter
Dim dataTable As DataTable
oledbAdapter.Fill(dataTable, Me.Variables.rsSomeResultset)

Error: System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb


It works all right when I use an OLEDB Connection Manager with the second code sample.


Question:
In order to extract the maximum performance, wouldn't it be preferred to use ADO.NET with SqlClient Provider in an all SQL Server 2005 environment? Or will an OLEDB Connection provide comparable or equal performance?


If so, what code can I use? Since the recordset returned by the Stored Proc (in the Execute SQL Task) can only be captured in a System.Object variable and you can only use the overload of the Fill() method of the OleDbDataAdapter to accept an ADO Recordset or Record object.

 

[MDXer]

THis should help you figure out what is best for your enviroment. Look for the response from Deniz Erkam.

[URL unfurl="true"]http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1785799&SiteId=1[/url]

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck