Understanding the “Full Result Set” of SSIS

Question

I have two questions about the result set generated by an execute SQL task -

1) What is the class of the object where the result set is stored ? Is it ADODB recordset ? 2) Whatever object it is, how does one iterate that object. I want to simply iterate it an print it in grid form, just like it would appear in SQL server management studio. I want to do this WITHOUT putting it into a DataTable object or something like that. That would be a waste of memory and time.

Can someone show me how to do that ?

Answer 1

An alternative would be to use an ADO.NET connection instead of OLEDB. If you use ADO.NET connection, you will get ADO.NET DataSet object. A DataSet is a collection of DataTable.

Some links for DataSet tutorials -

http://www.dotnetperls.com/dataset

Scott Mitchell's tut -

http://msdn.microsoft.com/en-us/library/aa581776.aspx

Answer 2

1) The result set is technically stored in an OLEDB Rowset object, which can be put into an ADORecordConstruction object and converted into an ADO RecordSet. SSIS does this implicitly whenever you actually access the data of the object.

Without scripting, the only way to ever see this data within an SSIS execution is to use a Foreach Loop container.

1. Set the Foreach Loop enumerator to Foreach ADO Enumerator.
2. Assign a variable to each column in your result set.
3. Use these variables in the loop to display them in your execution log using event handlers.

That being said, if you really want to view the object's data, I highly recommend creating a data flow, converting the object into a Script Source, and attaching a Data Viewer to it to see the data in grid form.

And finally I recommend if you're merely attempting to debug and review the data set returned by your SQL task, a script task can execute the query and push its results into (for example) a local file very quickly.