Execute SQL Task (catching results)

Hi,

I'm trying to use an execute SQL task with a simple select to get results and scan them.

I have to create a variable for each column to get results? or may i create something like a resultset variable?

Does this task return only one row and i have to loop manually? (maybe on al script task..) or can i get all returned data on a result set to be the input for next step?

Thanks!

Hi ya,

It depends on the result set property of the Execute sql task. The property can be set to xml, single row, full result set or none.
So whatever variable you're creating would be entirely dependent on the result set and how you want to achieve it.

Cheers

Rizwan

|||

I have set the property to "full result set", and create a variable which its type is "Object", but how can i get and scan the results in the next step?|||

You would need to point the resultset to a variable. Take a look at this article on Sql IS.

http://www.sqlis.com/58.aspx

This has how return a full row set in a variable.

Hope that helps

Cheers

RIzwan

|||

Thanks RizwanKhan,

I have read the article and its very interesting, but there is'nt nothing regarding how to manage the output (rowset).

Do you have any article about it? Can i get this rowset to be the input of another component or script?

Thanks!!

|||

You can use a For Each Loop to iterate the resultset in the control flow, or you can use a script component in the data source to "shred" the recordset.

A couple of examples (there are many, many more, if you search for them).

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/20/using-for-each-to-iterate-a-resultset.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/writing-a-resultset-to-a-flat-file.aspx