SSIS Derived Column Transformations

In some ETL scenarios, it is necessary to transform certain data elements such as dates or numeric fields.  For instance, you may have a field that reads 090501; which literally needs to be translated into a date of 05/01/2009.  Other times, you may have a numeric field that does not allow nulls but in the data it is a blank field where ” ” would need to be represented as 0.

In SSIS you can accomplish transformations like these using a Derived Column Transform in your Data Flow task.

Date Transforms

YYYYMMDD Date:

(DT_DATE)(SUBSTRING([TEST_DATE],5,2) + “-” + SUBSTRING([TEST_DATE],7,2) + “-” + SUBSTRING([TEST_DATE],1,4))

MMDDYYYY Date:

(DT_DATE)(SUBSTRING([TEST_DATE],1,2) + “-” + SUBSTRING([TEST_DATE],3,2) + “-” + SUBSTRING([TEST_DATE],5,4))

Handle a string value where the source system used “00000000”, “00/00/00”, etc.  as a lack of date (date format YYYYMMDD):

[TEST_DATE] == “00000000” ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING([TEST_DATE],5,2) + “-” + SUBSTRING([TEST_DATE],7,2) + “-” + SUBSTRING([TEST_DATE],1,4))

Handle blank values where source system used ” ” as lack of date (date format YYYY/MM/DD):

[TEST_DATE] == “” ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING([TEST_DATE],6,2) + “-” + SUBSTRING([TEST_DATE],9,2) + “-” + SUBSTRING([TEST_DATE],1,4))

Handle out of range or erroneous dates.  This would NULL a date below the year of 1900 and above the year 2100.  This would also catch erroneous dates like 2031221.

SUBSTRING(TEST_DATE,1,4) < “1900” || SUBSTRING(TEST_DATE,1,4) > “2100” ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING(TEST_DATE,6,2) + “/” + SUBSTRING(TEST_DATE,9,2) + “/” + SUBSTRING(TEST_DATE,1,4))

Numeric Transforms

Handle a numeric with a blank value:

NUMERIC_FIELD == “” ? “0” : NUMERIC_FIELD

Add a decimal point with a precision of 15 and a scale of 2

(DT_NUMERIC, 15, 2) [NUMERIC_FIELD] /100

Handle potential trailing sign fields.  This checks if there is a negative sign at the end; if yes. Adds the negative sign to front of the field and trim spaces off of field otherwise trim all spaces for amount field.  (example 123.45- or 123.45+):  NOTE: Only works for fixed fields.

SUBSTRING([AMOUNT_FIELD],14,1) == “-” ? “-” + LTRIM(SUBSTRING([AMOUNT_FIELD],1,13)) : TRIM([AMOUNT_FIELD])

Handle potential trailing sign fields and is a “fix” for the transform above to handle variable length fields.  This checks if there is a negative sign at the end; if yes. Adds the negative sign to front of the field and trim spaces off of field otherwise trim all spaces for amount field.  (example 123.45- or 123.45+): 

SUBSTRING([Column 0],LEN([Column 0]),1) == “-” || SUBSTRING([Column 0],LEN([Column 0]),1) == “+” ? SUBSTRING([Column 0],LEN([Column 0]),1) + SUBSTRING([Column 0],1,(LEN([Column 0]) – 1)) : [Column 0]

Advertisements

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: