November 12, 2013 at 11:02 am
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 ?
November 12, 2013 at 11:47 am
Don't know much of this myself, but this may help:
http://technet.microsoft.com/en-us/library/cc280492.aspx
The table in that article tells you the type to expect depending on the type of connection and type of result set you choose.
November 12, 2013 at 1:33 pm
1. It depends on the connection manager used. This blog post explains it quite nicely:
Exploring the System.Object Package Variable[/url]
2. You can either iterate it using a for each loop (storing the current row in different variables) or use a .NET script task or script component. How were you planning to "print it"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 15, 2013 at 12:10 am
Koen Verbeeck (11/12/2013)
1. It depends on the connection manager used. This blog post explains it quite nicely:Exploring the System.Object Package Variable[/url]
2. You can either iterate it using a for each loop (storing the current row in different variables) or use a .NET script task or script component. How were you planning to "print it"?
1 - Koen thanks for that link. I can't tell you how badly I needed that. I wish SSIS books talked about these things too.
2 - This is what I had been doing until now -
Take a recordset and then fill it into a datatable. Then, iterate over the data table to do whatever you want - print, process, load to target etc. But this approach became slow and useless in case of big recordsets. So, I thought why not read from the sys.obj directly instead of dumping it into a data table and then reading it ?
This is the inefficient approach I had been using - http://beyondrelational.com/modules/2/blogs/106/posts/11133/ssis-reading-object-variable-in-script-task.aspx
So, I thought that I should know more about what I am doing instead of just blindly following run of the mill tutorials and books.
I wanted to know things like - What are the types of objects into which we can directly store result sets (eg. ADODB recordset, DataTable etc) ? What are the disadvantages of one type over the other ? How to iterate over such objects (with examples) ? Whether you can re-iterate over these objects ? etc...
I wonder which book can answer such real world questions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply