maximum Data flow tasks execution

Hi guys,

i got a foreach loop that has about 20 data flow tasks(same database connections but different extractions) but i notice that when i execute the project it only runs 4 data flow tasks at a time.

i know that there is an option for each data flow to set the "Engine Threads", but is there a way to set the thereads in a foreach loop or for the whole project so it will execute all data flow tasks in one go for each loop.

please help?

Here is a interesting document that talks about: Integration Services: Performance Tuning Techniques that may be helpful to understand how to get better performance.

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

BTW what kind of hardaware do you have? wanting to run 20 data flow tasks in parallel sounds somehow unrealistic to me.

Rafael Salas

|||

Thanks for the post Rafael,

hmm, it wasn't exactly what i was looking for but it certainly gave me a good knowledge SSIS performance tuning.

the examplease that they had they were all in side data flows tasks, my problem sort of lies outside, inside a foreach loop. why it's only running 4 tasks at a time, there must be a counter somewhere.

but your link sort of gave me an idea of doing the extraction in another way. but it's going to be a lot of work, since i've already made my project.

you see what i'm doing is, i'm doing an overnight extract from the main datasbase to my data warehouse, has 20 scripts (all different sources and different destination). the reason it's inside a forloop is i've got many database sources that uses the same scripts to extract data.

the destination sql server is pretty good (SSIS runs on). it's a XEON 2 CPUs with 4gb RAM. resources is not an issue, well at least i think it's not. even if it's not enough i can get more CPUs and RAM to it, to get the job done faster.

cheers

AJ