I am a novice and am still learning with this new tool:
I am Iterating through a DataSet that is stored in a variable as an object.
The Data Set contains approximately 10,000 lines of data ( here in lies the problem)
I am using a ForEachLoop container to iterate through this large set of data:
In the loop are several ExecuteSQLTasks, which may or may not return a results.
There are roughly 12 tasks here.
At the end of the 12 tasks I do an Update.
I find the processing to be slow. Even after:
- placing the iteration into a separate package and calling it out of process.
From what I've read on the forum, execution should be quite fast
Could someone enlighten me on how execution is called within the ForEachLoop, are there solutions I can pursue ? Or should I rethink the design?
your help is greatly appreciatedIf you could build your process using data flows instead of Exec SQL, I think your performance will go up. It sounds like you are inserting/updating each row individually, rather than treating it as a batch. SSIS performs much better when you make use of the data flow components, and handle your processing within the data flow.|||Thank You Very Much for you Prompt Reply John.
Yes I have been following your advice. Hence my Tardy Reply.
With some design changes, appropriate lookups and parallel processing
I've managed to successfully execute the package under 10 minutes.
Though I'm sure there is room for Improvement.
Once again thank you for your advice.|||
Glad to help. Do you mind marking my response as an answer? It helps others searching the forums to find useful information.
Be sure to post if you need anything else.
