This is an old debate and yet not resolved. To use Script Component or Derived Column Transformation??

Yes Derived Column Transformation can NOT do all that can be done in Script Component.

Advantages of Script Component:

• Code is more readable
• Easy to maintain and debug.
• Can achieve a lot more than what can be done in Derived Column Task.

Advantages of Derived Column Transform:

• Easy to code for the first time.
• Very useful if you have good command over Expressions
• No need to get into the complexities of writing Script Component.

Drawbacks of Script Component:

• Not very user friendly Interface
• A Bit confusing at times
• One needs to learn C# or VB.NET coding (That’s a separate debate whether SSIS Developers should know .NET or that, will not get into that right now.)

Drawbacks of Derived Column transform:

• VERY DIFFICULT TO DEBUG complex expressions.
• Difficult to code if the level of nesting goes beyond 3 or may be 5(that’s what I feel not a rule as such).

So I conclude that Script Component should be used when the logic is complex and Derived component should be used when me need some minor modification like append or nesting of if then else loop up to 3(that’s what I feel not a rule as such) levels, or simple string manipulation .

As per the performance issue regarding the use of the above I will do some tests and keep this post updated.

Would like to hear your views as well…