I use the Derived Column transform in the Data Flow a lot. In fact, I can’t remember the last time I wrote a production package that didn’t include at least one Derived Column transform in it. Over time, I’ve built up a lot of common expressions that I keep using, as well as some that I stored "just in case". I thought it would be good to post a few of them here, primarily because it makes it easier for me to find them, but hopefully others will find these useful as well.
References to columns ([StringColumn], for example) need to be replaced with the appropriate column name in the data flow. Values in italics need to be updated with the appropriate value (literal, column, or variable). Also, some of the date expressions may not work if your regional settings are not US date formats.
| Description | Expression |
| Count the occurrences of a specific character in a string | LEN([StringColumn]) – LEN(REPLACE([StringColumn],"Character to count","")) |
| Cast a date stored in a string column as YYMMDD to DT_DBTIMESTAMP | (DT_DBTIMESTAMP)(SUBSTRING([StringColumn],3,2) + "-" + SUBSTRING([StringColumn],5,2) + "-" + SUBSTRING([StringColumn],1,2)) |
| Cast a date stored in a string column as YYYYMMDD to DT_DBTIMESTAMP | (DT_DBTIMESTAMP)(SUBSTRING([StringColumn],1,4) + "-" + SUBSTRING([StringColumn],5,2) + "-" + SUBSTRING([StringColumn],7,2)) |
| Cast a date stored in a string column as DDMMMYYYY to DT_DBTIMESTAMP | (DT_DBTIMESTAMP)(SUBSTRING([StringColumn],3,3) + "-" + SUBSTRING([StringColumn],1,2) + "-" + SUBSTRING([StringColumn],6,4)) |
| Pad a string to the specified length | [StringColumn] + REPLICATE(" ", 10 – LEN([StringColumn])) |
| Check a date to see if it in a valid range and replace with NULL if it is not | [DateColumn] < (DT_DBTIMESTAMP)"1753/01/01" ? NULL(DT_DBTIMESTAMP) : [DateColumn > (DT_DBTIMESTAMP)"9999/12/31" ? NULL(DT_DBTIMESTAMP) : [DateColumn] |
| Convert seconds to minutes and seconds | (DT_WSTR,20)([NumberOfSeconds] / 60) + ":" + (DT_WSTR,20)([NumberOfSeconds] % 60) |
| Combine date and time strings to DT_DBTIMESTAMP | (DT_DBTIMESTAMP)("2008-03-25" + " " + "10:20:00") |
| Return True if Date is a Weekend | DATEPART("Weekday",[DateColumn]) == 7 ? (DT_BOOL)"True" : DATEPART("Weekday",[DateColumn]) == 1 ? (DT_BOOL)"True" : (DT_BOOL)"False" |
| Cast a string to a numeric with a range check and return 0 if out of range | [StringColumn] > "9" ? 0 : [StringColumn] < "0" ? 0 : ((DT_NUMERIC,1,0)[StringColumn]) |