What is SSIS Derived Column Transform

SSIS Derived column transform is used to change existing columns coming from Source or add New Columns in the Pipeline Buffer.Note: For simplicity we have used Free DummyData Generator Source in this post rather than real Source.. Please download it from this link

Changing / Adding column using SSIS Derived Column Transform

Many time you need to change upstream column value inside pipeline somewhere in between source and destination. In that case you will most likely use either SSIS Derived Column Transform or SSIS Script Transform. For simple expressions use SSIS Derived Column Transform because its very fast compared to Script Transform.

Remember few things while using SSIS Derived Column Transform

Column Names are Case sensitive

You can only have single line Expressions

Expressions are hard to debug so avoid very complex expressions (use script transform in that case)

Here is sample how to TRIM and do UPPER case of existing Source column

SSIS Derived Column Transform Expression

Caution about using SSIS variable in Derived Column Transform

Some time you may have to use SSIS Variables in Derived Column Transform. Values of SSIS Variables are cached in PreExecute phase of Transform so its read only once when transform receive first row and then cached.

Troubleshoot SSIS Derived Column Transform errors

The most annoying part in SSIS Derived Column Transform is troubleshooting. Here are some tips so you don’t pull your hair.

If error is at design time then most likely you will get validation error. You can read it by doing mouse over expressions flagged as red color.

If error is at runtime then you have to find out which raw causing issue.

Drag error output (red arrow coming from transform) of SSIS derived Column Transform and attach to some Flat File destination to know which rows are failing