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