Monday, July 25, 2011

SSIS: Performance in loading many Excel Files to SSIS

On my last project, I tested different methods in SSIS 2008 package to load 9 small Excel files to the same target table (about 10 rows in each file).
Each file is loaded in a  simple dataflow. My objective was to find the which method will run with best performance.
I tested:
·         Parallel running of all 9 dataflows (full parallel).
·         Serial connection between all dataflows (no parallel).
·         Make each dataflow as separate package, master package runs all in parallel.
·         Organize dataflows as 2 or 3 series (half parallel).
Well, here are the results:
Runtimes of ALL of above mentioned method were the same!! All run times were in the same small range, between 2:07-2:13 min.
Every dataflow separately took about 15s, in 2 series it took 25-40s for everyone, etc.. In full parallel design, first dataflow finished after 1:30 min only..
The conclusion is clear: Excel Provider works as one-instance process, that doesn’t use more resources to run faster.
Nice to know..

No comments:

Post a Comment