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.

Solution 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.

Solution 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.

Solution 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.

Solution 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"];

View more solutions

Comments

[Brian]

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

[Steam]

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

[Brian]

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 */);.

[Steam]

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.

[Steam]

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

[Brian]

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

[Steam]

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.

[Steam]

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.

[SyMo7amed]

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