SSIS Tuning – Interesting thought
Baseline source system extract speed.
Understand your source system and how fast you extract from it. After all, Integration Services cannot be tuned beyond the speed of your source – i.e., you cannot transform data faster than you can read it.
Measure the speed of the source system by creating a very simple package reading data from your source with the a destination of “Row Count”:
Execute the package from the command line (DTEXEC) and measure the time it took for it to complete its task. Use the Integration Services log output to get an accurate calculation of the time. You want to calculate rows per second:
Rows / sec = Row Count / TimeData Flow
Based on this value, you now know the maximum number of rows per second you can read from the source – this is also the roof on how fast you can transform your data.