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

• mhongsyok

  SSC Veteran

  Points: 297

  More actions

  April 26, 2011 at 3:45 pm

  #239212

  I have been getting the error below:

  "Error: The script threw an exception: Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb

  at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults)

  at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet)

  at ScriptTask_164e0d9976794196bb0ab2d7dbea1ca6.ScriptMain.Main()

  "

  My source database is Oracle, and my connection type is "OLE DB". I have Execute SQL Task-->Script Task. Execute SQL Task runs successfully and parses value to the variable named "UnrecognizedOrg" with "Object" data type. I set Result Name = 0 (zero) for this variable.

  It blows up in Script Task which takes the result set from "UnrecognizedOrg" variable above. This variable is set as "ReadWriteVariables".

  Here is my troublesome code snippet. Could anyone please shed me some light on this? I have been unsuccessfully searching and searching for the solution for more than a week...

  Thank you in advance for your help!

  Imports System

  Imports System.Data

  Imports System.Math

  Imports Microsoft.SqlServer.Dts.Runtime

  Imports System.Xml

  Imports System.Data.OleDb

  Public Class ScriptMain

  Public Sub Main()

  Dim oleDA As New OleDb.OleDbDataAdapter

  Dim dt As New DataTable

  Dim col As DataColumn

  Dim row As DataRow

  Dim sMsg As String

  oleDA.Fill(dt, Dts.Variables("UnrecognizedOrg").Value) 'This is where it blows up..

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 26, 2011 at 6:01 pm

  #1316330

  you didn't mention it, maybe it's there but not in the snippet you posted, but where is the connection for the dataadapter?

  I know the ADODBRecordSet As object is the 4th of 5 overloaded .Fill() methods, and i'm sure that Dts.Variables("UnrecognizedOrg").Value is not a recordset but a string, right?

  Dim sConn As String = String.Empty

  sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\BlockGroups_2010\" & ThisFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

  Dim sSQL As String = "select * from SomeTable"

  Dim myConnection As New OleDbConnection(sConn)

  myConnection.Open()

  Dim myCommandObject As New OleDbCommand(sSQL, myConnection)

  Dim myDataAdapter As New OleDbDataAdapter(myCommandObject)

  myDataAdapter.Fill(myDataSource, "myDataTable")

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mhongsyok

  SSC Veteran

  Points: 297

  More actions

  April 26, 2011 at 7:53 pm

  #1316345

  Hi Lowell,

  Thank you for looking into this.

  I am indeed trying to mimic the functionality described in the posting/link below.

  http://sqlblog.com/blogs/andy_leonard/archive/2007/10/14/ssis-design-pattern-read-a-dataset-from-variable-in-a-script-task.aspx

  I defined my database connection through Connection Manager prior to and outside of the VB script. On the posting/link above, one guy named Slava and couple of other guys were having the same issue as me "Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb". But, no one really able to figure out how to fix it. I make sure that I use “Object” data type on my variable as suggested, and it still does not solve the problem.

  Dts.Variables("UnrecognizedOrg").Value is meant to be a recordset from the query “select o.org_name, o.org_id from organization o minus select r.org_name, r.org_id from vendor_organization_ref r” defined in Execute SQL Task with OLE DB connection type.

• mhongsyok

  SSC Veteran

  Points: 297

  More actions

  April 27, 2011 at 8:47 am

  #1316653

  Hi Lowell,

  I finally found out what I did wrong. It is a "duh" enlightment for being such a newbie to me.. I ran each of those tasks separately. I should have run them within the same sequence container so that Script Task can communicate with Execute SQL Task. Thank you for your time looking into this though. Have a great day!