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.

Answer 1

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.