Exploring the System.Object Package Variable [SSIS]

A Record SetAs you may already know, it is possible to use the Execute SQL Task to populate a package variable with a result set.

In case you’re not that familiar with this technique yet, here are a quick two words on setting that up.  You just give it a query, set the ResultSet property to Full result set and configure a package variable in the Result Set property window.  The package variable’s type is System.Object.

But what exactly is this mysterious System.Object and how can we explore it?  Well, that depends.  More precisely, it depends on the Connection Type which you’ve chosen in the Execute SQL Task properties.

Let’s explore two possibilities: ADO.NET and OLE DB.  Our end goal is straightforward: retrieve the number of records in the result set.

The query which I’m using in the Execute SQL task is this one:

select ProductAlternateKey
from dbo.DimProduct
where Color = 'blue'

On my AdventureWorksDW2012 database it should return 28 records: 28

Exploring the ADO.NET result set

The first step is finding out what type exactly this result set object is.  Hook up a Script Task to your Execute SQL task and put a breakpoint on it.  Now run your package and examine the Locals window:

Debugging the Control Flow to find the object type

Well look at that, it’s a System.Data.DataSet!  Using this knowledge it’s fairly simple to produce code that fetches the record count:

DataSet ds = (DataSet)Dts.Variables["MyResultset"].Value;

Note: don’t forget to add the package variable to the ReadOnlyVariables before opening the code editor.

The System.Data namespace is included by default in the using statements, no worries there.  So we can just cast the variable into a Dataset.  The DataSet object contains a DataTableCollection called Tables.  As there’s only one result set this is located at index zero.  We travel down the object tree to finally find the Count property of the Rows DataRowCollection.

And here’s the result:

The message box shows 28 items

That’s all there’s to it, easy huh?  Let’s move on to our second option, OLE DB.

Exploring the OLE DB result set

Once again we start at the beginning: with the debugging of the Control Flow to find out what object type our mysterious System.Object is:

The OLE DB result set gives us a System.__ComObject, hmm...

Hmm, System.__ComObject, that’s … special.  Ow right, the OLE DB provider uses a COM wrapper.  How can we “unwrap” our object and introduce it to the .NET world?  Let’s see if we can find out what’s hidden behind that wrapper, by using the following code:


TypeName is a VB.NET function and retrieves the data type of the parameter passed into it.

To get this to run in a C# SSIS task you first need to add the Microsoft.VisualBasic reference:

Adding a reference to the VB.NET assembly

Executing the package results in this:

Result type: Recordset

So, our result is Recordset, hmm, well, I think we more or less knew this already.  What kind of Recordset?  Well, an ADO Recordset.  We know this because the following code works:

System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt, Dts.Variables["MyResultset"].Value);

Basically, we use the Fill method of the OleDbDataAdapter to fill a System.Data.DataTable with the data from the ADO Recordset.  The version of the method in our example (there are several overrides) accepts two parameters:

public int Fill(

DataTable dataTable,

Object ADODBRecordSet


With the DataTable filled we’ve got once again access to a Rows DataRowsCollection, exactly the same as in our ADO.NET example in fact.  Executing the package now results in exactly the same message box as shown earlier: 28 records!

Beware of pitfalls

If you mix the two methods up you’ll get funky errors such as:

System.InvalidCastException: Unable to cast COM object of type ‘System.__ComObject’ to class type ‘System.Data.DataSet’. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

and also

System.ArgumentException: Object is not an ADODB.RecordSet or an ADODB.Record.

So be careful, use the right object types for your particular System.Object.


In this article I’ve demonstrated a couple of methods which can be used to retrieve information from the mysterious System.Object result set in an SSIS package.

Have fun!


Additional References

Execute SQL Task

Result Sets in the Execute SQL Task

OleDbDataAdapter Class


Tags: , ,

  1. Koen Verbeeck’s avatar

    Good to know that the C# code becomes a lot easier if you use an ADO.NET connection manager.


  2. BoltBait’s avatar

    Thank you so much for this article. It was just what I needed to solve a problem today.


  3. Martin Aronsson’s avatar

    Really useful information, thanks!


  4. Cristian Riffo’s avatar

    Thank you, very useful and well explained.


  5. Nangamso’s avatar

    Thank You.. This was a quick solution to my problem :-)


  6. Shelley’s avatar

    thanks so much! Solved my problem today.


  7. hotel’s avatar

    Hurrah! Finally I got a web site from where I be capable of really get
    helpful information regarding my study and knowledge.


  8. online’s avatar

    My brother recommended I might like this blog. He was entirely right.
    This post actually made my day. You cann’t imagine
    just how much time I had spent for this info!


  9. barang unik’s avatar

    I am actually thankful to the owner of this web page who has shared this great paragraph
    at at this place.


  10. toko online’s avatar

    What i don’t understood is in reality how
    you are not really much more neatly-favored than you may be right now.

    You’re very intelligent. You know therefore considerably in the case of this
    subject, produced me in my view believe it from a lot of numerous angles.
    Its like men and women are not fascinated until it’s one thing to accomplish
    with Lady gaga! Your own stuffs outstanding. All the time deal with
    it up!


  11. Willodean Schacht’s avatar

    Hey there, You have done a great job. I’ll definitely digg it aand personally recommend to
    my friends. I am confident they will be benefited from this site.


  12. Thurman’s avatar

    It’s in point of fact a great and useful piece of information.
    I am glad that you just shaqred this useful info with us.
    Please keep us informed like this. Thank you
    for sharing.


  13. Get the facts’s avatar

    I have been exploring for a little bit for any high quality articles or weblog posts on this sort of space . Exploring in Yahoo I eventually stumbled upon this web site. Studying this information So i am glad to exhibit that I’ve a very just right uncanny feeling I found out exactly what I needed. I most for sure will make sure to don’t fail to remember this website and give it a look on a constant basis.


  14. realtek hd audio’s avatar

    While other countries like the US and Brazil have enabled their municipal governments to issue debt instruments or securities,


  15. Ian Delcamp’s avatar

    Everything is very open with a really clear explanation of the issues.
    It was reaally informative. Your website is useful.
    Thanks for sharing!


  16. www.hotradioserang.com’s avatar

    is a trusted QQ Texas hold’em Online and Bandar Ceme Online gambling site that gives online card
    video games such as Online Texas Hold’em, DominoQQ, Capsa
    Online, Ceme Online, Ceme99, Online Gambling Online Online Poker
    Sites. QQ Casino Poker Ceme, the very best and safest on the internet poker agent website with 24 hour
    IDN Online Online poker solution. For those of you Lovers of the video game
    Online Online as well as who wish to play gambling Online Casino poker, Online Ceme, Domino QQ, City Ceme,
    Online Gaming, Bandar Capsa Online in 1 ID


© 2008-2019 BI: Beer Intelligence? All Rights Reserved