August 1, 2011 at 8:57 am
Hi All,
I want to download hundreds of table using SSIS from an off-site source but want to make sure they run largest first on a limited number of threads, say 10 at a time.
I want to run largest first as it should hopefully minimize download time and keep all the threads busy.
I can limit the processing to 10 using EngineThreads but don't know how to get them to process in a particular order.
I am wondering if there is some kind of ID number against each data flow that I can choose as the parallel processing start order otherwise it'll just be a free for all and I could end up with lots of empty threads near the end of processing.
Currently I'm running them in batches of 10, each 10 in a separate Data Flow Task.
I'd prefer them to be all in one Data Flow Task and run in a predefined order.
Any ideas?
August 2, 2011 at 12:35 pm
It sounds like you want Control Flow functionality inside a Data Flow...I don't think that's how it works 🙂
Someone may drop in with some magic setting I am not aware of. Or you may be able to do something with Scripting, but I think you'd be re-inventing [portions of] the wheel that way.
I think you may be stuck with multiple Data Flow Tasks as you have it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 5, 2011 at 7:55 am
I have found the magic setting... "ID"
If you click the source box of any of your data flow tasks and look at its properties there is a common property called ID that is greyed out and not editable. It is an integer.
You can however edit it in the XML directly but need to be careful that it stays unique.
I just pulled the XML into SQL and reordered the ID's so that they went in decreasing table size order. They then process in parallel using "lowest ID" as the candidate for the next available thread.
Jobs a goodun...
August 5, 2011 at 8:04 am
Eureka! Thanks for posting back. That sounds a little like black magic 🙂 Have you been able to validate that the ID does not change through saving/importing/exporting the package? I would recommend retesting this one through patches and upgrades. Given that it's greyed out in the IDE it would not surprise me if there were an undocumented change in behavior in the future.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply