SSIS: Some Comments about Parallelism
Today I came across some issues with “Parallel Execution” within a dataflow. Did you ever use one source system for more than one data source in a dataflow? If the queries you send of are not trivial it might affect your source system that you run these queries in parallel, which is the default for Integration Services. In the last days we struggled with a dataflow sending off 10 quite complex queries to one server using up all the memory at the source. So I was looking for a solution for this problem.
However there seams to be no solution. The only way you can handle this is by splitting up the dataflow into many and save the result i.e. in RAW files which you later use instead of querying the source.
What I would appreciate is a way to control how many queries can be send off to a connection manager at one time. I opened a suggestion for that on Microsoft Connect and everybody is invited to vote for it... http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178069
I also would like to point your attention to a suggestion Jamie posted. It’s the old issue that you can’t control how data is send to multiple destinations in a dataflow. So if you have two destinations (i.e. two tables connected by a foreign key constraint) you can’t enforce your data integrity by controlling that i.e. an order is saved before the details are added. Here’s the link to Jamie’s post and I assume that votes are appreciated as well... http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=178058
Thomas