SSIS Retrieve Column Names from ADO.NET Recordset
Hello I am having an issue retrieving column names from an ADO.NET connection. I will walk through what I have done and then let me know if you guys have any suggestions or alternatives to solve this issue. SSIS Structure: 1. For each loop that retrieves SQL statements directly from a table2. Execute SQL task that stores the resultset as a variable (using an ADO.net connection to Teradata)3. Create a script task to retrieve the column names -- This needs to be fixed Now, I want to be able to extract the column names from this variable, a System.Object variable that stores the recordset. I have found script tasks that will work with an OLEDB connection. This works great for an OLEDB connection, but it errors when I use ADO.NET. I am using Execute SQL task instead of ADO.net source + Recordset destination since the input Sample Code: Dim oleDA As New Data.OleDb.OleDbDataAdapter Dim dt As New DataTable Dim col As DataColumn Dim num As Integer num = 1 oleDA.Fill(dt, Dts.Variables("objresults").Value) For Each col In dt.Columns Dts.Variables("Header" & num).Value = col.ColumnName num = num + 1 Next Dts.TaskResult = ScriptResults.Success The problem is that I need to use ADO.net to connect to Teradata. I am looking into getting an OLEDB driver for it, but would prefer an alternative solution. Here is the error I am getting: 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_b37c1fbeda164cad8acd827f1f1436ba.vbproj.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()
July 13th, 2012 12:20pm

Because you're using a managed connection manager, such as ADO.NEt connection Manager (Execute SQL Task), the returned object is a System.Data.Dataset. Result Sets in the Execute SQL Task This forum post includes a script example for accessing a resultset via an ADO.NET connection. See response from JayH. Carla Sabotta
Free Windows Admin Tool Kit Click here and download it now
July 16th, 2012 6:34pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics