How to access data in resultset returned in variable by ExecuteSQLTask

Hot Topic

Which version is best for me ?
Reading and writing same variable to a script
Debugging Custom Report Data Processing extension
Matrix Help with Columns
Replication between SQL CE 2.0 and SQL Server 2005 not working
deleting sql server 2005 deployed packages?
Joining Table Operation
Left join does not work after SQL 2005 upgrade
How to call remote object using C# Stored procedure
concatenate a string within a loop from a temp table

SQL Server

Reporting Services Performance
Gettint error "product level is insufficient" - did full re-install of SQL 2005 Standard Edition
environment variables
DECODE Statement
No grouping?
Foreach loop for DBASE IV problem
SSIS will not start after SQL Server 2005 SP1 upgrade
Error in the PROCEDURE....
wizard generated insert proc adds two records
Urgent- sqlconnection string

How to access data in resultset returned in variable by ExecuteSQLTask

According to the BOL documenation of the ExecuteSQLTask you can populate a variable with a resultset. I specified a variable of type 'Object' and followed the instructions and everything is fine untill then.

However later I want to access the variable in a script task and it appears that the variable is populated with a System.__ComObject.
How do I access my rows from here

Your suggestions would be appreciated.

Regards,
Henk

Answer this question

How to access data in resultset returned in variable by ExecuteSQLTask

  • Ibrahim Y

    OK,
    Well it looks like you'ev got it working anyway.

    Is there a reason why you couldn't shred the object in a Foreach loop as Darren suggested Its much easier than writing code!

    Regards
    Jamie

  • Yicong Shen

    The clue is in the error. It is a COM object be because it an ADO object, not ADO.Net. I forget the exact type, probably a ADO RecordSet, although it may vary depending in the result set type you are using.

    Depeding what you want to do the ADO For Each may help. See the example used in here-

    Shredding a Recordset
    (http://www.sqlis.com/default.aspx 59)

  • Shaunkerr

    Because I want to use the table as an in memory lookup table and need to look up the content from a script.

  • Ayan Debnath

    Sorry I wasn't clear, have you got it working now, I haven't checked the code If not let me know and I'll dig out some code.

  • Neil Kiser

    Jamie and Darren, Thanks for the input.

    Jamie, I already studied your blog article, but it was not clear to me how from that how to get hold to the recordset object and iterate it. The moving of the adodb.dll still is needed, but changed slightly: copy it from C:\Program files\Microsoft.Net\Primary Interop Assemblies to C:\Windows\Microsoft.NET\Framework\v2.0.50215 (or what ever the VSA add reference wizard looks in for dlls)

    The code to grap the recordset and iterate the records I ended up with:

    Public Sub Main()
            Dim rs As ADODB.Recordset
            Dim myVariableValue As Object = Dts.Variables("myVariable").Value

            rs = CType(myVariableValue, ADODB.Recordset)
            Dim count As Int32 = rs.RecordCount()

            While (Not rs.EOF)
                Dim fields As Fields = rs.Fields
                'Do something with the record
                'Dim field As Field = fields(0)
                'MsgBox(field.Name() + " - " + field.Value().ToString())
                rs.MoveNext()
            End While

            Dts.TaskResult = Dts.Results.Success
    End Sub

  • ChampAmp

    This may also help: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx

    Note that this was written quite a while ago so its possible the stuff about moving DLLs may not be necassary.

    -Jamie


  • How to access data in resultset returned in variable by ExecuteSQLTask
Answer this question