How to use SSIS Derived Column Transform

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

Use case of SSIS Derived Column Transform

1. Changing data to Upper case or Trimming white spaces from values coming from Flat File
2. Combining First name and Last name to create New Display name column
3. Detecting NULL and Replace with BLANK
4. Format Date Time

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

1. Column Names are Case sensitive
2. You can only have single line Expressions
3. 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

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.

1. 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.
2. If error is at runtime then you have to find out which raw causing issue.
   1. 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
   2. Use data viewer
3. Reduce rows coming from source and narrow down troubleshooting

Example Expressions for Derived Column Transform

ssis derived column transformation if then

[CustomerFlag]==1 ? "ACTIVE" : "IN ACTIVE"

ssis derived column transformation switch

[State]=="GA" ? "GEORGIA" : " [State]=="NY" ? "NEWYORK" : [State]=="AL" ? "ALABAMA" : "Other"

ssis derived column transformation concatenate string (see how to type cast int to string)

[Col1] + [Col2] + "some static value"  + (DT_WSTR)55