Random access in a result set

I use an SQL task to produce a result set which I store in a package variable.
It works fine to access the result set in a script task by first casting it to a ADODB.Recordset, and then enumerating it and accessing the rows in the given order.

But is there a simple and clean way to randomly access the result set In other words to repeatedly select a random row from it

One thought I had was to convert the result set to some sort of array which can be randomly indexed. But I didn't find out how... I'm not interested in creating an array by looping through the whole result set and inserting the rows one by one into an array. So is there some other better way

Answer this question

Random access in a result set

Bede
Oh sorry... guess I wasn't clear enough.
What I want to do is to generate arbitrary many rows where each row is randomly selected (and therefore most probably unique). So preferably not a bunch of identical rows.

Hope I managed to explain it a bit more! ! =)
norman_timo

Terence,

Will the row sampler component not work for you

-Jamie
protools
Thanks Jamie!
Sean B.
Thanks Jamie... But this "read the recordset into an internal storage mechanism"-thing was actually what I wanted to avoid.

I am looking for
either (1) a clean way to do the conversion from a record set into some kind of array without having to loop through the complete record set.
or (2) a straightforward way to make random selections from a record set.

Maybe there is no better way than the "looping through"-solution

Caleb T

Samuel L wrote:
Oh sorry... guess I wasn't clear enough.
What I want to do is to generate arbitrary many rows where each row is randomly selected (and therefore most probably unique). So preferably not a bunch of identical rows.

Hope I managed to explain it a bit more! ! =)

Yeah, that makes alot more sense (I thought it was a strange requirement).

The same still stands though. Within the script component read the recordset into an internal storage mechanism and pick as many rows as you want at random.

-Jamie

mdoiron

That's right Duane.

Terence, there is a data-flow transformation component in SSIS that randomly samples rows for you.

-Jamie
Kim9999
In the same way that you process the result set in a script task you could do it in a source script component (in a data-flow) and then use the row sampler component to get a random row.

Just an idea...not a recommendation!

-Jamie
Robert Blake
i'm assuming that you mean the "row sampling transformation". correct
Tim Rachel
Hi Duane and James,

May i know more about this transformation component in SSIS
what is that actually

How it work and can i have some sample code btw may i know is the randomly samples rows record will not duplicate

regards
terence chua
MichelleMcKelvey
Ah. I missed the word "repeated" in your original post.

Again, you could probably do this in a script component. Pick a record at random (I dare say .net provides a way of picking a number at random) and output that record as many times as you want. Is that what you want to do

This would have to be an asynchronous component. Your script source is an asynchronous component so perhaps you could combine the two into one!

-Jamie
Bhavish_K

Hi Jamie,

may i know what is row sampler actually i'm new in .NET normally i only code in vb6. and i not advance user. cos the program is all just in house using only so many component also dun know how to use. did you have any sample code

terence
AltheaGayle
Thanks Jamie... That's a good idea to use the source script component, because that's actually what I want to do... Generate arbitrary many random rows. But then there is a limitation with the row sampler: it can only produce at most as many rows as it gets as input. If it only gets 100 rows it can at most produce 100 sample rows... =(

Hrm... Is there some other way to go
ssandu

Samuel L wrote:

Maybe there is no better way than the "looping through"-solution

I don't know of one (which doesn't mean there isn't one!!!)

-Jamie
ddrakonn

Hi Samuel,

did you find any solution on your problem already

actually i also face similiar requirement. i need to create a customer contact list basic on yesterday transaction record. to just say thank to them. but my management dun want 100% of customer be selected, them just want around 80% and not 80% from begining or end of transaction. them want it randomly.

then write all the record into a .txt or .dat file. to pass to the other module to send out sms.

i able to use rnd() function to get the sequence randomly, and i also try to avoid using loop to get all record.

regards

terence
Random access in a result set

Answer this question