I received a question this morning about whether there are any ways to estimate the memory requirements of executing SSIS packages? In the case of the question, the emailer wanted to know how to do things like find out the data size at the source and at the destination of a data transformation, or how to reckon the memory consumption of a SSIS package of variable size.
It turns out there are several good sources of information for this question and for others like it. First of all, I always recommend that you investigate http://www.sqlis.com, a site dedicated to Integration Services and supported by my MVP friends in the UK, Allan Mitchell and Darren Green.
Next, I googled for help on this specific question and came up with these resources:
1. http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx#EK
2. http://www.scalabilityexperts.com/default.asp?action=article&ID=167
3. And finally, http://msdn2.microsoft.com/en-us/library/ms137622(SQL.90).aspx, showing how to monitor the performance of the data flow engine in SSIS.
I hope this helps,
-Kevin
P.S. Thanks to Wee Hyong for also tackling this question!
