Parallel execution of source SQL commands

Hi,

we're accessing a SQL Server as a source for some SSIS packages using quite complex SQL commands. We have dataflows getting data from up to 10 queries. The problem is that SSIS starts all these queries in parallel using up all the memory of the server (the source SQL server, not the server SSIS is running on). So the queries are very slow. Is there any way to force SSIS to start the queries after each other?

I already browsed the web for some answers on that and I'm not very optimistic... Maybe the only solution is really to feed the result of the query in raw files and process them later...

Thanks,

Thomas,

I know you'll already have considered this but what is wrong executing them in sequence using precedence constraints?

Alternatively you could change the package's MaxConcurrentExectuables property which I'm guessing is currently set to -1.

-Jamie

|||

Jamie,

thanks for the fast reply...

As far as I understand (and that's what I see at least while debugging) precendence constraints and MaxConcurrentExecutables control only the workflow, not what's happening in the dataflow... The problem is that it's one dataflow with 10 queries feeding one destination. I guess both options don't help in this case...

|||

Thomas,

I'm sorry, I misunderstood. I thought you meant the queries were all in seperate data-flows. Now I understand your comment about raw files. Currently that is definately the only way to go.

Mind you, if they are all on the same server can you not just join all the tables together using a single SQL statement?

I have raised a similar request on Microsoft Connect about being able to define the order in which we insert to the destination adapters. You can see it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058

Why not click-through and add some detail about your source adapter problem as well? Or open it as a seperate issue. I would definately vote for it.

-Jamie

|||

Hi Jamie,

I hoped that there is a tweak for it... But that's what I expected...

I voted for your suggestion and opened up a new one since the problem and the solution is a little bit different... http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178069

THANKS!

|||

Excellent. i've voted and commented on it.

-Jamie