using ssis to generate load

hi.
i'm trying to use ssis for something it wasn't meant for (i think)... which is load generation.

I have a table which has exec sql statements. I want to load this table, multicast the statements into x recordsets, then get x sql connections and execute these statements.

so far i can load the table, and multicast it into x recordsets. But i can't figure out how to execute these statements by getting them out of the dataflow and into control flow 'execute sql' task....

i'm i on the wrong path

thanks!

Answer this question

using ssis to generate load

shane_w

PugV wrote:
yes, the number of statements is known..

No problem then. Have as many Execute SQL Tasks as you have statements, let them use the same connection manager - and that should be it.

PugV wrote:
if there's an executesql task for each statement, does that mean a connection per statement

No.
A connection manager maintains a differnt conenction for each task that uses it (hence its called a connection manager and not a connection).

If so desired you can change this behaviour by setting RetainSameConnection=TRUE. The default is FALSE.

-Jamie
bmoyno
Not quite sure what you're trying to do here but I'll take a guess. You have a list of SQL statements sitting in a table. And you want to execute each one of those statements using an Execute SQL Task. Is that correct

First of all, I don't understand what your data-flow is doing here.

What I would do is use a Foreach loop to enumerate your list of SQL statements and pass each one into a variable. Your Execute SQL Task (which sits inside the Foreach loop) can then take its SQL statement to be executed from that variable.

-Jamie
Madhavan281981
yes, the number of statements is known..

if there's an executesql task for each statement, does that mean a connection per statement

April Reagan

PugV wrote:
I'm trying to stress test the database by running these sql statements from many connections simultaneously..

so to achieve that I can just have a bunch of foreach loops running 'ExecuteSQLTask'

thanks!

I see. No then, you can't use Foreach loop because it won't execute them all at the same time (FYI: a parallel Foreach loop has been requested for the next version).

In order to run them simultaneously you would need an Execute SQL Task for each statement. Is the number of statements known at design time

-Jamie

satya chappidi
I'm trying to stress test the database by running these sql statements from many connections simultaneously..

so to achieve that I can just have a bunch of foreach loops running 'ExecuteSQLTask'

thanks!
using ssis to generate load

Answer this question