Recently this question came up on an internal SSIS mailing list:
We have to process hundreds of thousands of rows through a fairly complex script component. Would converting it to a custom SSIS component give us any performance gain?
I’ve seen this question come up a couple of times, and I thought it would make a good post.
The main reason to move from script -> custom component is make re-use and maintenance of the code easier. I wouldn't recommend doing it just for performance reasons.
People usually see a minor performance gain when making the switch, provided that the component is written properly (minimizing calls to the COM interfaces and caching all metadata in PreExecute() are key!). The nice thing about the Script Component is that it wraps all of the implementation details from you. Custom components don’t have these wrappers, which makes it easier to make coding mistakes that can drastically decrease your performance.
You would get a performance gain by writing the custom component using native code (C++), as it skips all of the managed layers and interop. I wouldn't recommend it for anything other than really low level operations, however, as the COM interfaces have minimal documentation and can be pretty hard to use.
You typically get the biggest gain by making sure the code is well written. The quickest way to identify performance bottlenecks it to http://msdn.microsoft.com/en-us/magazine/cc337887.aspx . Custom components have an http://blogs.msdn.com/mattm/archive/2008/11/12/performance-profiling-your-custom-extensions.aspx , as you can’t hook up a debugger to a script component, but you can achieve the same results by moving your script outside of the SSIS, and unit testing it directly.
