The following expressions can be used in the Derived Column Transformation Editor expression cell.

A derived column is a new column which is build from other input column(s). The \ signifies the escape character within the double quotes.

-- SSIS expressions

-- SSIS derived column expression

-- SSIS replace

/* Replace "a" with "A" in ShipName column */

REPLACE(ShipName,"a","A")

REPLACE(ShipName,"\x0061","A")

REPLACE(ShipName,"\x0061","\x0041")

REPLACE(ShipName,"a","\x0041")

/* Replace CR with empty string - filter out CR */

REPLACE(ShipName,"\x000D","")

REPLACE(ShipName,"\r","")

/* Replace LF with empty string - filter out LF */

REPLACE(ShipName,"\x000A","")

REPLACE(ShipName,"\n","")

/* Replace tab with empty string - filter out tab */

REPLACE(ShipName,"\x0009","")

REPLACE(ShipName,"\t","")

/* IF...ELSE... equivalent */

SalesActual >= SalesPlan ? "Sales target met" : "Sales target missed"

-- If AccountNo column is less than 6 digits, business account, else consumer account

(DT_UI8)AccountNo < (DT_UI8)99999 ? "BUSINESS" : "CONSUMER"

/* Translate expression: either shipped or NULL */

(ShipmentCode == "C") ? "Shipped" : NULL(DT_WSTR, 8)

/* Elapsed days since CreateDate */

/* SSIS GETDATE() returns the current system date in DT_DBTIMESTAMP format */

/* SSIS DATEDIFF function is similar to T-SQL DATEDIFF */

DATEDIFF("dd", (DT_DATE) CreateDate,GETDATE())

------------

-- Strip time from datetime OrderDate column

-- SSIS substring

SUBSTRING((DT_WSTR,"25")(DT_DBDATE)OrderDate,1,10)

-- 2015-03-15

------------

/* Format current datetime to YYYY/MM/DD style */

(DT_WSTR,4)YEAR(GETDATE()) + "/" +

RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()), 2) + "/" +

RIGHT("0" + (DT_WSTR,2)DAY( GETDATE()), 2)

------------