System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record

Question

I used the code below to fill a data table -

OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataTable dt = new DataTable();
oleDA.Fill(dt, Dts.Variables["My_Result_Set"].Value); 

I get the error -

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentException: 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 ST_34944nkdfnfkdffk333333.csproj.ScriptMain.Main()
   --- End of inner exception stack trace ---
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Why do I get this error ? I have used the exact same code before and it never gave me any problems.

Runstadler

Where is Dts defined? – Ilke

Okay, Dts is defined inside SSIS (An ETL tool). Its actually an Object variable which can be accessed via the syntax shown in my code. – Runstadler

Is it on that line that the exception is being thrown or before it? – Ilke

@Ilke - On that line. I found out like this - comment all code after that line - Error ! Then, comment that line also - No error ! Code before line is okay. No problems. – Runstadler

Answer 1

This error happens when you have not connected two components in SSIS. See image - The execute SQL task produces an ADO record set which will be consumed by the C# script. That ADO is stored in an SSIS object variable (not shown in image) and is picked up by the C# script which I mentioned in my question.

Runstadler

This irritating error happened again today. I had to copy paste some code from an old package into another. I copied it properly, created all the necessary variables and assigments. Then, i got this error. I simply deleted the copied stuff and re-copied it again. Problem solved. Strange as hell. – Runstadler

Answer 2

In a script task, I created a datatable called DtUsers, created columns in the datatable, added rows and assigned values to the row cells. I then stored this datatable in an object variable called activeDirectoryUsers:

Dts.Variables["User::activeDirectoryUsers"].Value = DtUsers;

I then created a Data Flow and added a Source Script Component. I tried to use Fill() and received the same error as the OP.

OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt, Variables.activeDirectoryUsers);  //error here

Object is not an ADODB.RecordSet or an ADODB.Record

The solution for me was twofold:

1. Add the DtUsers datatable to a DataSet and then store the DataSet in the object variable.

   DataSet ds = new DataSet();
   ds.Tables.Add(DtUsers);
   Dts.Variables["User::activeDirectoryUsers"].Value = ds;
   

2. In the Source Script Component, create a new DataSet and cast the object variable to type DataSet, and assign it to the DataSet:

   DataSet ds = (DataSet)Variables.activeDirectoryUsers;
   if (ds == null || ds.Tables.Count == 0) return;
   DataTable dt = ds.Tables[0];
   //do stuff with dt...
   

This article led me to this solution.

Ayo

this was the solution for my case, filling a datatable in Script task – Exsect

Answer 3

I think you need to append the .ToString() method when you perform the .Fill().

Something like this:

OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataTable dt = new DataTable();
oleDA.Fill(dt, Dts.Variables["My_Result_Set"].Value.ToString()); 

From the documentation here:

Fill(DataTable, Object) Adds or refreshes rows in a DataTable to match those in an ADO Recordset or Record object using the specified DataTable and ADO objects.

It looks like in your implementation, you are trying to pass an unexpected type to the .Fill() method.

Ilke

I just noticed that you are not passing your command object into your OleDbDataAdapter. Have you tried that? – Ilke

Brian - I am not sure what you mean. Please explain. – Runstadler

I am wondering what your command object is? And my previous comment was in regards to why you aren't passing the command to your DataAdapter, like this, OleDbDataAdapter oleDA = new OleDbDataAdapter(/* Your command goes here */);. – Ilke

I used the c# code given here - beyondrelational.com/modules/2/blogs/106/posts/11133/… . I am doing the same thing as that guy and it worked before too. Now, I don't know why I am getting this error. – Runstadler

I just discovered the reason. You have to connect the components involved graphically. Let me illustrate what happened. – Runstadler

That sounds great; and congrats on answering your question on your own :) – Ilke

Brian - I think the tool graphically does what you suggested in terms of code. Could that actually be the reason ? This is a graphical coding tool. For routine tasks, we have standard components and for custom, we have the script task which can be coded in C# or VB. – Runstadler

Answer 4

In my case the problem was resolved changing the code and using DataSet for Fill instead DataTable

OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataSet ds = new DataSet();
oleDA.Fill(ds, "MyTableName"); 
DataTable dt = ds.Tables["MyTableName"];

Valaree

Answer 5

Below worked for me without Fill.

OleDbDataAdapter oleDA = new OleDbDataAdapter();
DataSet ds = new DataSet();
ds = (DataSet)Dts.Variables["User::myresultSet"].Value;

Abettor

Answer 6

I had the same or very similar error and the problem was in the Execute SQL Task that loaded the Object variable that I was passing in, I had the ResultSet set to Single row and it needed to be Full result set.

Universalist

Answer 7

In my case, a silly mistake with Transaction happened. Just check in SQL server if you had not forgotten to Commit/Rollback any transaction on DB table. That will also gives you the same error message in SSIS while filling the DataTable using data apdapter.

Aggi