Nick Maroudas (2/4/2008)
Hi Jamie, thanks for your interest on this topic.To answer the second part of your query first, the decision to implement as a dataflow rather than use the T-SQL in a stored procedure or in a SQL task within SSIS is mainly legacy based. Although the ability to change the way the package works is far simpler in SSIS than having to trudge through the SQL. This will also make it easier for future developers to follow on and improve on existing flows within the package.
Very interesting justifications. I wrote an article once comparing these 2 options which you can see here: http://blogs.conchango.com/jamiethomson/archive/2006/03/14/SSIS_3A00_-Data-flows-or-T_2D00_SQL.aspx
I'd be really grateful if you could add your comments into the comments section there.
On improvement/degradation,
In my experience and that is not the same as everyone else, the improvement on the SQL was markedly greater, although when developing our initial solution it was done mainly on a workstation. I will look into getting the server stats for you with regard to the differences.
Nick.
I would expect T-SQL to be significantly faster. You're using a row-by-row lookup which will be very slow in comparison.
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson