Parallel execution of source SQL commands

Hot Topic

Multiple Triggers Error: Nesting Level Exceeded (Limit 32). SQL 2005
Problems attaching to database from VWD
SQL Server 2005 DB capacity
Error when i try to Deploy my reports to Reporting services - PLEASE HELP i'm also NEW at this
Select Data based on Certain Time period
Query running in SQL Server 2000
SQL 2005 Express
parameterized update sp_executesql
Report Manager error
Should Database Mail be used for production email applications?

SQL Server

Cannot Kill Process stays in KILLED/ROLLBACK status
Import Data to SQL server from Excel spreadsheet
Help about running SQL CE on Win CE 4.2
The password of the account must be changed
Tag name from an XML
Custom Data Flow Component Column Mapping Question
Restoring SQL Server 2005 backup to SQL server 2000
New to sql server
Changes related to PDF in SQL Server 2005 Reporting Services
what are the differences between float and decimal?

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,




Answer this question

Parallel execution of source SQL commands

  • zendic

    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



  • Valery Zharkov

    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!



  • script

    Excellent. i've voted and commented on it.

    -Jamie



  • Butterflyangel02

    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...



  • NotTaken

    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



  • Parallel execution of source SQL commands
Answer this question